python sqlite与mysql数据转换

最新推荐文章于 2025-07-01 15:24:41 发布

原创最新推荐文章于 2025-07-01 15:24:41 发布 · 2.6k 阅读

6 ·

CC 4.0 BY-SA版权

Python-Coding 专栏收录该内容

11 篇文章

订阅专栏

本文介绍如何使用Python将SQLite数据库的数据转换并迁移到MySQL。通过示例代码详细展示了数据读取、连接MySQL以及数据导入的过程。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

代码：

#! /usr/bin/python
# encoding:utf-8

import MySQLdb
import sqlite3

def convertDB(fromDict, toDict):
    print fromDict['connect_parameters']
    if fromDict['type']=='sqlite':
        conn_from = sqlite3.connect(fromDict['connect_parameters'])
        cur_from = conn_from.execute("select id,name,age,address,salary from company")
        list = cur_from.fetchall()
    elif fromDict['type']=='mysql':
        conn_from = MySQLdb.connect(host=fromDict['connect_parameters']['host'],
                                  user=fromDict['connect_parameters']['username'],
                                  passwd=fromDict['connect_parameters']['password'],
                                  db=fromDict['connect_parameters']['dname'],
                                  port=fromDict['connect_parameters']['port'])
        cur_from=conn_from.cursor()
        cur_from.execute("select id,name,age,address,salary from COMPANY")
        list=cur_from.fetchall()
    for each in list:
        print each

    if toDict['type']=='sqlite':
        conn_to = sqlite3.connect(toDict['connect_parameters'])
    elif toDict['type']=='mysql':
        conn_to = MySQLdb.connect(host=toDict['connect_parameters']['host'],
                                  user=toDict['connect_parameters']['username'],
                                  passwd=toDict['connect_parameters']['password'],
                                  db=toDict['connect_parameters']['dname'],
                                  port=toDict['connect_parameters']['port'])


    cur_to=conn_to.cursor()
    cur_to.execute('''CREATE TABLE COMPANY
    (ID INT PRIMARY KEY  NOT NULL,
    NAME TEXT NOT NULL,
    AGE INT NOT NULL,
    ADDRESS CHAR(50),
    SALARY FLOAT);''')
    if toDict['type']=='sqlite':
        cur_to.executemany("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
            VALUES (?,?,?,?,?)",
                           list);
    elif toDict['type']=='mysql':
        cur_to.executemany("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
            VALUES (%s,%s,%s,%s,%s)",
                           list);
    conn_to.commit()

# 将sqlite数据库test转换到mysql
fromDict = {'type':'sqlite', 'connect_parameters' : 'D:/test.db'}
toDict = {'type' : 'mysql', 'connect_parameters' : {'host':'192.168.48.10', 'username':'test', 'password':'mysql',
                                                    'port':3306, 'dname':'test'}}
convertDB(fromDict,toDict)
# 将mysql数据库转换到sqlite数据库test1
fromDict = {'type' : 'mysql', 'connect_parameters' : {'host':'192.168.48.10', 'username':'test', 'password':'mysql',
                                                    'port':3306, 'dname':'test'}}
toDict = {'type':'sqlite', 'connect_parameters' : 'D:/test1.db'}
convertDB(fromDict,toDict)