sqlite数据库转换到mysql操作流程
需求：需要将sqlite数据库导入mysql运行环境：Centos 7 x64Python2.7
解决步骤：
1.先将sqlite数据库导出为mysql
sqlite3 xmspace.net.db .dump > xmspace.net.sql
2.用python进行转换
#-- coding:UTF-8 --
import re
import io
def convert_sqlite_to_mysql(sql):
# 移除 PRAGMA 语句
sql = re.sub(r'PRAGMA .*?;', '', sql)
# 修改 BEGIN TRANSACTION 为 START TRANSACTION
sql = sql.replace('BEGIN TRANSACTION;', 'START TRANSACTION;')
# 替换 COMMIT TRANSACTION 为 COMMIT
sql = sql.replace('COMMIT;', 'COMMIT;')
# 替换 ROLLBACK TRANSACTION 为 ROLLBACK
sql = sql.replace('ROLLBACK;', 'ROLLBACK;')
# 修改 AUTOINCREMENT 为 AUTO_INCREMENT
sql = sql.replace('AUTOINCREMENT', 'AUTO_INCREMENT')
# 替换数据类型
sql = sql.replace('INTEGER', 'INT')
sql = sql.replace('TEXT', 'VARCHAR(255)')
# 替换双引号为反引号
sql = sql.replace('"', '`')
# 添加 CHARACTER SET utf8mb4 到表定义中
sql = re.sub(r'CREATE TABLE `(.*?)` \(', r'CREATE TABLE IF NOT EXISTS `\1` (', sql)
# sql = re.sub(r'\);', r') ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;', sql)
# 处理 INSERT 语句，确保数据字符集正确
sql = re.sub(r'INSERT INTO `(.*?)` \((.*?)\) VALUES', r'INSERT INTO `\1` (\2) VALUES', sql)
sql = re.sub(r'INSERT INTO `sqlite_sequence`.*?;', '', sql)
return sql
# 读取原始的 SQLite dump 文件
with io.open('xmspace.net.sql', 'r', encoding='utf-8') as file:
sql_dump = file.read()
# 转换为 MySQL 兼容的 SQL
mysql_compatible_sql = convert_sqlite_to_mysql(sql_dump)
# 将转换后的 SQL 写入新的文件
with io.open('xmspace.net_mysql.sql', 'w', encoding='utf-8') as file:
file.write(mysql_compatible_sql)
print("SQL conversion complete. Please check 'dump_mysql.sql' for MySQL import.")
页:
[1]