sqlite3 迁移 到mysql8 亲测有效

sqlite3 迁移 到mysql8 亲测有效，有问题评论区交流

import sqlite3
import mysql.connector
from mysql.connector import Error
import sys
import logging
from typing import List, Tuple, Optional


class DatabaseMigration:
    """
    SQLite3 到 MySQL8 数据库迁移工具
    """

    def __init__(self, sqlite_path: str, mysql_config: dict):
        self.sqlite_path = sqlite_path
        self.mysql_config = mysql_config
        self.sqlite_conn = None
        self.mysql_conn = None

        # 设置日志
        logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s')
        self.logger = logging.getLogger(__name__)

    def get_mysql_column_type(self, sqlite_type: str) -> str:
        """
        根据SQLite类型映射到MySQL类型
        """
        sqlite_type = sqlite_type.upper().strip()

        if 'INTEGER' in sqlite_type:
            return 'INT'
        elif 'TEXT' in sqlite_type or 'CHAR' in sqlite_type or 'CLOB' in sqlite_type:
            return 'LONGTEXT'
        elif 'BLOB' in sqlite_type:
            return 'LONGBLOB'
        elif 'REAL' in sqlite_type or 'DOUBLE' in sqlite_type or 'FLOAT' in sqlite_type:
            return 'DOUBLE'
        elif 'BOOLEAN' in sqlite_type or 'BOOL' in sqlite_type:
            return 'TINYINT(1)'
        elif 'DATE' in sqlite_type:
            return 'DATE'
        elif 'DATETIME' in sqlite_type or 'TIMESTAMP' in sqlite_type:
            return 'DATETIME'
        elif 'TIME' in sqlite_type:
            return 'TIME'
        elif 'NUMERIC' in sqlite_type or 'DECIMAL' in sqlite_type:
            # 尝试提取精度信息
            import re
            match = re.search(r'NUMERIC\s*((\d+),\s*(\d+))', sqlite_type, re.IGNORECASE)
            if match:
                precision = match.group(1)
                scale = match.group(2)
                return f'DECIMAL({precision}, {scale})'
            else:
                return 'DECIMAL(10, 2)'
        else:
            # 检查是否有长度定义
            import re
            match = re.search(r'(\w+)\s*((\d+))', sqlite_type)
            if match:
                base_type = match.group(1).upper()
                length = match.group(2)
                if 'VARCHAR' in base_type or 'CHAR' in base_type:
                    return f'VARCHAR({length})'

            return 'VARCHAR(255)'  # 默认类型

    def format_default_value(self, default_value, sqlite_type: str) -> str:
        """
        格式化默认值，根据数据类型决定是否加引号
        """
        if default_value is None:
            return ''

        sqlite_type_upper = sqlite_type.upper()

        # 检查是否为TEXT或BLOB类型，这些类型在MySQL中不能有默认值
        if any(t in sqlite_type_upper for t in ['TEXT', 'CLOB', 'BLOB']):
            self.logger.warning(f"TEXT/BLOB类型列不能在MySQL中设置默认值，将忽略默认值 '{default_value}'")
            return ""

        # 对于数值类型不需要引号
        if any(t in sqlite_type_upper for t in ['INTEGER', 'REAL', 'DOUBLE', 'FLOAT', 'NUMERIC', 'DECIMAL']):
            try:
                # 尝试转换为数值
                str_val = str(default_value)
                if '.' in str_val:
                    float(str_val)
                else:
                    int(str_val)
                return f"DEFAULT {str_val}"
            except ValueError:
                # 如果不是有效数字，则作为字符串处理
                pass

        # 对于布尔类型特殊处理
        if 'BOOLEAN' in sqlite_type_upper or 'BOOL' in sqlite_type_upper:
            if str(default_value).lower() in ('true', '1', 'yes', 'on'):
                return "DEFAULT 1"
            elif str(default_value).lower() in ('false', '0', 'no', 'off'):
                return "DEFAULT 0"
            else:
                # 如果不是标准布尔值，尝试作为字符串处理
                # 但首先检查是否为有效的布尔值字符串
                try:
                    # 尝试转换为布尔值
                    bool_val = bool(default_value)
                    return "DEFAULT 1" if bool_val else "DEFAULT 0"
                except:
                    # 如果不能转换为布尔值，返回空字符串（不设置默认值）
                    self.logger.warning(f"无法将 '{default_value}' 识别为有效的布尔值，将不设置默认值")
                    return ""

        # 对于日期时间类型
        if 'DATE' in sqlite_type_upper or 'TIME' in sqlite_type_upper or 'TIMESTAMP' in sqlite_type_upper:
            # 检查是否是CURRENT_TIMESTAMP这样的特殊值
            if str(default_value).upper() in ('CURRENT_TIMESTAMP', 'NOW()', 'DATETIME("NOW")', 'DATE("NOW")'):
                return "DEFAULT CURRENT_TIMESTAMP"
            else:
                # 验证日期格式
                import re
                date_pattern = r'^\d{4}-\d{2}-\d{2}$'
                datetime_pattern = r'^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$'

                str_val = str(default_value)
                if re.match(date_pattern, str_val) or re.match(datetime_pattern, str_val):
                    # 是有效的日期格式
                    return f"DEFAULT '{str_val}'"
                else:
                    # 不是有效的日期格式，跳过默认值
                    self.logger.warning(f"日期类型列的默认值 '{default_value}' 格式无效，将忽略默认值")
                    return ""

        # 对于NULL值
        if str(default_value).upper() == 'NULL':
            return "DEFAULT NULL"

        # 其他情况都加上引号
        # 转义字符串中的单引号
        escaped_value = str(default_value).replace("'", "''")
        return f"DEFAULT '{escaped_value}'"

    def create_mysql_table_from_sqlite(self, mysql_cur, table_name: str, sqlite_columns_info: List[Tuple]):
        """
        根据SQLite表结构创建MySQL表
        """
        columns_def = []
        primary_keys = []

        # 分析列信息
        for col_info in sqlite_columns_info:
            col_name = col_info[1]
            col_type = col_info[2] if col_info[2] else 'TEXT'
            not_null = 'NOT NULL' if col_info[3] == 1 else ''
            default_val = self.format_default_value(col_info[4], col_type) if col_info[4] is not None else ''
            is_primary = col_info[5] == 1  # 主键标识

            if is_primary:
                primary_keys.append(col_name)

            # 检查是否为主键的TEXT类型列，如果是则转换为VARCHAR
            mysql_type = self.get_mysql_column_type(col_type)
            if is_primary and col_type.upper() in ['TEXT', 'CLOB']:
                # 对于主键的TEXT类型，使用VARCHAR(255)替代
                mysql_type = 'VARCHAR(255)'
                self.logger.info(f"将主键列 '{col_name}' 的类型从 TEXT 转换为 VARCHAR(255)")

            column_def = f"`{col_name}` {mysql_type} {not_null} {default_val}".strip()
            columns_def.append(column_def)

        # 检查表是否已存在
        check_table_sql = """
        SELECT COUNT(*) 
        FROM information_schema.tables 
        WHERE table_schema = DATABASE() AND table_name = %s
        """
        mysql_cur.execute(check_table_sql, (table_name,))
        if mysql_cur.fetchone()[0] > 0:
            self.logger.info(f"表 {table_name} 已存在，跳过创建")
            return

        # 构建主键定义
        if primary_keys:
            primary_key_def = f", PRIMARY KEY(`{'`, `'.join(primary_keys)}`)"
        else:
            primary_key_def = ""

        # 创建表
        create_table_sql = f"CREATE TABLE `{table_name}` ({', '.join(columns_def)}{primary_key_def})"

        try:
            mysql_cur.execute(create_table_sql)
            self.logger.info(f"成功创建表: {table_name}")
        except Error as e:
            self.logger.error(f"创建表 {table_name} 失败: {e}")
            # 尝试不指定主键的方式创建
            create_table_sql = f"CREATE TABLE `{table_name}` ({', '.join(columns_def)})"
            try:
                mysql_cur.execute(create_table_sql)
                self.logger.info(f"使用备用方案创建表: {table_name}")
            except Error as e2:
                self.logger.error(f"备用方案创建表也失败: {e2}")
                raise e2

    def migrate_single_table(self, sqlite_cur, mysql_cur, table_name: str):
        """
        迁移单个表的数据
        """
        self.logger.info(f"\n正在处理表: {table_name}")

        # 获取SQLite表的结构信息
        sqlite_cur.execute(f"PRAGMA table_info({table_name})")
        columns_info = sqlite_cur.fetchall()

        if not columns_info:
            self.logger.warning(f"表 {table_name} 没有列信息，跳过")
            return

        # 根据SQLite表结构创建MySQL表
        self.create_mysql_table_from_sqlite(mysql_cur, table_name, columns_info)

        # 获取SQLite表的数据
        sqlite_cur.execute(f"SELECT * FROM `{table_name}`")
        rows = sqlite_cur.fetchall()

        if not rows:
            self.logger.info(f"表 {table_name} 没有数据")
            return

        # 准备插入语句
        column_names = [col[1] for col in columns_info]
        placeholders = ', '.join(['%s'] * len(column_names))

        # 使用 ON DUPLICATE KEY UPDATE 处理重复主键
        update_clause = ', '.join([f"`{col}`=VALUES(`{col}`)" for col in column_names])
        insert_sql = f"INSERT INTO `{table_name}` ({', '.join([f'`{col}`' for col in column_names])}) VALUES ({placeholders}) ON DUPLICATE KEY UPDATE {update_clause}"

        # 执行批量插入
        try:
            mysql_cur.executemany(insert_sql, rows)
            self.logger.info(f"成功插入或更新 {len(rows)} 条记录到表 {table_name}")
        except Error as e:
            self.logger.error(f"批量插入数据到表 {table_name} 失败: {e}")
            # 如果批量插入失败，尝试逐条插入找出具体哪条记录有问题
            successful_inserts = 0
            for i, row in enumerate(rows):
                try:
                    mysql_cur.execute(insert_sql, row)
                    successful_inserts += 1
                except Error as row_error:
                    self.logger.error(f"第 {i + 1} 条记录插入失败: {row_error}, 记录内容: {row}")

            self.logger.info(f"表 {table_name} 成功插入/更新 {successful_inserts}/{len(rows)} 条记录")

    def connect_databases(self):
        """
        连接两个数据库
        """
        # 连接 SQLite
        self.sqlite_conn = sqlite3.connect(self.sqlite_path)
        self.sqlite_cur = self.sqlite_conn.cursor()
        self.logger.info("成功连接到SQLite数据库")

        # 连接 MySQL
        self.mysql_conn = mysql.connector.connect(**self.mysql_config)
        self.mysql_cur = self.mysql_conn.cursor()
        self.logger.info("成功连接到MySQL数据库")

    def disconnect_databases(self):
        """
        断开数据库连接
        """
        if self.sqlite_conn:
            self.sqlite_conn.close()
            self.logger.info("SQLite连接已关闭")
        if self.mysql_conn:
            self.mysql_conn.close()
            self.logger.info("MySQL连接已关闭")

    def migrate_all_tables(self):
        """
        迁移所有表
        """
        try:
            self.connect_databases()

            # 获取SQLite中的所有表
            self.sqlite_cur.execute("SELECT name FROM sqlite_master WHERE type='table';")
            tables = self.sqlite_cur.fetchall()

            for table_tuple in tables:
                table_name = table_tuple[0]

                # 跳过SQLite系统表
                if table_name.startswith('sqlite_') or table_name == 'android_metadata':
                    self.logger.info(f"跳过系统表: {table_name}")
                    continue

                self.migrate_single_table(self.sqlite_cur, self.mysql_cur, table_name)

            # 提交事务
            self.mysql_conn.commit()
            self.logger.info("\n所有表迁移完成！")

        except Error as e:
            self.logger.error(f"数据库操作错误: {e}")
            if self.mysql_conn:
                self.mysql_conn.rollback()
        except Exception as e:
            self.logger.error(f"发生错误: {e}")
        finally:
            self.disconnect_databases()


def main():
    """
    主函数 - 数据库迁移入口
    """
    # SQLite 数据库路径
    sqlite_db_path = 'F:/lsq-DB/DB3K_508back.db3'

    # MySQL 配置
    mysql_config = {
        'host': '192.168.0.203',
        'port': 13306,
        'user': 'root',
        'password': 'root',
        'database': 'cmd-kaiyuan',
        'charset': 'utf8mb4',  # 推荐使用utf8mb4支持完整Unicode
        'autocommit': False
    }

    # 创建迁移实例并执行
    migrator = DatabaseMigration(sqlite_db_path, mysql_config)
    migrator.migrate_all_tables()


if __name__ == "__main__":
    main()