Python MySQL Connector事务回滚失效问题求助

• DDL语句会隐式提交事务：MySQL中，CREATE DATABASE、DROP DATABASE、CREATE TABLE这类数据定义语言（DDL）执行时会自动触发COMMIT，强制提交当前事务。这意味着即使后续触发异常，这些DDL操作已经被永久提交，无法通过事务回滚撤销。
• 游标未初始化：原代码直接调用cursor.execute()但未通过conn.cursor()创建游标对象，会触发运行时错误。
• SQL语句拆分逻辑缺陷：简单用;拆分schema文件内容，会生成空语句（比如文件末尾的换行）或包含注释的无效语句，执行时可能抛出异常打断流程。

import mysql.connector
from mysql.connector.errors import Error

# 请替换为实际配置
USERNAME = "your_username"
PASSWORD = "your_password"
target_db_name = "new_db"
SCHEMA_LOCATION = "schema.sql"
QUERIES = ["INSERT INTO new_table SELECT * FROM main_db.old_table WHERE org_id = "]
org_Id = 123

conn = None
cursor = None

try:
    conn = mysql.connector.connect(
        host='localhost', 
        port=3306,
        user=USERNAME, 
        password=PASSWORD,
        autocommit=False
    )
    cursor = conn.cursor()

    # 执行DDL操作（这类操作会自动提交，需手动处理回滚）
    cursor.execute(f"DROP DATABASE IF EXISTS {target_db_name};")
    cursor.execute(f"CREATE DATABASE {target_db_name};")
    cursor.execute(f"USE {target_db_name};")

    # 启动事务，仅包裹可回滚的DML操作
    cursor.execute("START TRANSACTION;")

    # 改进SQL语句解析：过滤无效内容
    with open(SCHEMA_LOCATION, 'r') as f:
        schema_content = f.read()
        commands = []
        current_cmd = ""
        for line in schema_content.splitlines():
            line = line.strip()
            # 跳过空行和单行注释
            if not line or line.startswith('--'):
                continue
            # 简单处理块注释
            if '/*' in line:
                line = line.split('/*')[0].strip()
                if not line:
                    continue
            current_cmd += line + " "
            if ';' in current_cmd:
                full_cmd = current_cmd.split(';')[0].strip()
                if full_cmd:
                    commands.append(full_cmd)
                current_cmd = ""

    for cmd in commands:
        cursor.execute(cmd)

    # 执行数据复制语句
    for query in QUERIES:
        cursor.execute(f"{query}{org_Id};")

    conn.commit()

except Error as err:
    print(f"执行错误: {err}")
    if conn is not None:
        # 回滚未提交的DML操作
        conn.rollback()
        # 手动清理已创建的数据库（DDL操作无法通过事务回滚）
        try:
            cursor.execute(f"DROP DATABASE IF EXISTS {target_db_name};")
        except:
            pass
finally:
    # 关闭资源
    if cursor is not None:
        cursor.close()
    if conn is not None and conn.is_connected():
        conn.close()

• 分离DDL与事务：将DROP/CREATE DATABASE等无法回滚的DDL操作放在事务外，异常时通过手动执行DROP DATABASE清理残留资源。
• 处理DDL隐式提交：即使CREATE TABLE放在事务内，MySQL也会自动提交事务，因此若要完全回滚，需在异常时删除已创建的表或数据库。
• 完善SQL解析逻辑：过滤空语句、注释，避免无效SQL引发不必要的异常。
• 正确初始化资源：确保游标对象创建后再执行SQL操作，避免未定义错误。

内容的提问来源于stack exchange，提问作者vvs14