数据库迁移特惠

import sqlite3
import mysql.connector

# 连接到SQLite数据库
sqlite_conn = sqlite3.connect('source.db')
sqlite_cursor = sqlite_conn.cursor()

# 连接到MySQL数据库
mysql_conn = mysql.connector.connect(
    host="localhost",
    user="user",
    password="password",
    database="target_db"
)
mysql_cursor = mysql_conn.cursor()

# 查询SQLite数据库中的所有表
sqlite_cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
tables = sqlite_cursor.fetchall()

for table in tables:
    table_name = table[0]
    
    # 导出表结构
    sqlite_cursor.execute(f"SELECT sql FROM sqlite_master WHERE type='table' AND name='{table_name}';")
    create_table_sql = sqlite_cursor.fetchone()[0]
    
    # 修改表结构以适应MySQL
    create_table_sql = create_table_sql.replace("AUTOINCREMENT", "AUTO_INCREMENT").replace("INTEGER PRIMARY KEY", "INT AUTO_INCREMENT PRIMARY KEY")
    
    # 创建表
    mysql_cursor.execute(create_table_sql)
    
    # 导出数据
    sqlite_cursor.execute(f"SELECT * FROM {table_name};")
    data = sqlite_cursor.fetchall()
    
    # 插入数据
    mysql_cursor.executemany(f"INSERT INTO {table_name} VALUES ({','.join(['%s']*len(data[0]))})", data)

# 提交事务并关闭连接
mysql_conn.commit()
sqlite_conn.close()
mysql_conn.close()