SQLite库导入MySQL库的Python方法

最新推荐文章于 2024-06-19 23:06:22 发布

洗洗睡去

最新推荐文章于 2024-06-19 23:06:22 发布

阅读量622

点赞数

分类专栏：杂项 sqlite 文章标签： mysql sqlite python

本文链接：https://blog.csdn.net/sunnyloves/article/details/106498506

版权

杂项同时被 2 个专栏收录

30 篇文章 4 订阅

订阅专栏

sqlite

2 篇文章 0 订阅

订阅专栏

需求

以前一直用SQLite库存储少量的数据，现在因为项目需要，要导入到MySQL库里去，用了SQLiteStudio的导出到sql、xml、json等方法，但MySQL不能导入，总是出错，于是干脆用Python从SQLite读，再写到MySQL里去的方法。

工具

SQLiteStudio
PHPStudy_Pro里的MySQL,Apache,MySLQAdmin
Python 3.5，环境由miniConda配置
IDE是 VSCode

步骤

miniConda里安装库sqlite3和pymysql

conda install sqlite3
conda install pymysql

VSC里打开Python工作目录，确保可以正常工作
新建文件，输入下面代码
运行完成

代码

import sqlite3
import pymysql
import time


start = time.process_time()
# 链接sqlite数据库
sqliteConnect = sqlite3.connect(D:/MyDocuments/test.db)       
sqliteCursor = sqliteConnect.cursor()
# 链接mysqli数据库
mySQLConnect = pymysql.connect(
    host='localhost',
    port=3306,
    user='root',
    password='root',
    db='test',
    charset='utf8'
)
mySQLCursor = mySQLConnect.cursor()
sqliteCursor.execute('select name from sqlite_master where type=\'table\' order by name')
sqliteTables = []
# 获得表名
for row in sqliteCursor:
    sqliteTables.append(row[0])

for item in sqliteTables:
    print('**********************')
    print('开始进行库{}复制'.format(item))

    sqliteCMD = 'SELECT sql FROM sqlite_master WHERE name=\'' + item + '\''  
    # 查询SQLite表创建DDL命令
    sqliteCursor.execute(sqliteCMD)
    for row in sqliteCursor:
        mySQLCursor.execute(row[0])   # 用这个命令创建MySQL新表
    sqliteCMD = 'SELECT * FROM ' + item  # 读取数据
    # 列名
    sqliteCursor.execute(sqliteCMD)
    columnNames = [tuple[0] for tuple in sqliteCursor.description]  

    # 装配语句
    temp = 'INSERT INTO '+item+' ('
    for column in columnNames:
        temp += column
        temp += ','
    mySQLCMD = temp.rstrip(',') + ') VALUES ('
    # print(mySQLCMD)
    idx = 0
    for row in sqliteCursor:
        mySQLCMD = temp.rstrip(',') + ') VALUES ('
        idx += 1
        if(idx % 50000 == 0):
            print('已复制{}行'.format(idx))

        temp2 = ''
        for i in range(len(row)):
            if(row[i] == None):
                temp2 += 'null'
            elif type(row[i]) is float:
                temp2 += str(row[i])
            elif type(row[i]) is str:
                if(row[i]==''):
                    temp2 += '0.0'
                else:
                    temp2 += '\'' + row[i] + '\''
            elif type(row[i]) is int:
                temp2 += str(row[i])
            else:
                print('还有'+type(row[i]))
                temp2 += row[i]
            temp2 += ','
        mySQLCMD += temp2.rstrip(',')+')'
        # print(mySQLCMD)
        mySQLCursor.execute(mySQLCMD)
    print('库{}复制完成，共{}行'.format(item, idx)) 

sqliteCursor.close()
sqliteConnect.close()

mySQLCursor.close()
mySQLConnect.close()

end = time.process_time() - start
print('**********************')
print("全部完成，共用时{}秒".format(end))