如何在Python中批量执行多段SQL并实现事务回滚？

这是个很典型的事务处理场景——你需要保证每次循环里的多个SQL操作要么全部成功提交，要么全部失败回滚，也就是数据库事务的原子性要求。你的现有代码核心问题是没在出错时回滚已执行的操作，下面给你梳理问题并给出修改方案：

现有代码的核心问题 #

1. 语法错误：Exceptiion拼写错误（少了一个o），会直接导致代码运行报错；fail_list.append('error':err.msg)是非法语法，应该用字典格式。
2. 事务未回滚：默认情况下mysql.connector会关闭自动提交，但当某段SQL执行失败时，之前已执行的操作仍停留在未提交的事务中，若不手动回滚，这些操作可能会在后续流程中被意外提交，或者影响下一次循环的事务状态。

修改后的代码实现 #

import mysql.connector
from mysql.connector import Error  # 捕获更精准的数据库错误

# 初始化数据库连接（请补充你的连接参数）
mydb = mysql.connector.connect(
    host="your_host",
    user="your_user",
    password="your_password",
    database="your_database"
)
cursor = mydb.cursor()
fail_list = []  # 初始化错误记录列表

for country in country_list:
    try:
        # 注意：存储过程调用需用正确语法，带参数的话用占位符
        query1 = "CALL sp_insert_sth_1(%s)"
        query2 = "CALL sp_insert_sth_2(%s)"
        query3 = "CALL sp_insert_sth_3(%s)"
        
        # 按顺序执行SQL，传入参数（如果存储过程需要的话）
        cursor.execute(query1, (country,))
        cursor.execute(query2, (country,))
        cursor.execute(query3, (country,))
        
        # 所有操作成功后，才提交事务
        mydb.commit()
    except Error as db_err:
        # 数据库错误：回滚当前循环的所有操作
        mydb.rollback()
        # 记录错误详情及对应的国家
        fail_list.append({
            "country": country,
            "error": f"Database error: {str(db_err)}"
        })
        continue
    except Exception as general_err:
        # 其他意外错误：同样回滚并记录
        mydb.rollback()
        fail_list.append({
            "country": country,
            "error": f"Unexpected error: {str(general_err)}"
        })
        continue

# 清理资源：关闭游标和连接
cursor.close()
mydb.close()

关键优化点说明 #

• 原子性保证：每个循环对应一个独立事务，所有SQL执行成功才commit()；一旦出错立即rollback()，彻底撤销当前循环内的所有操作，确保数据一致性。
• 精准错误捕获：优先用mysql.connector.Error捕获数据库相关错误（比如存储过程不存在、约束冲突、参数错误等），再用Exception兜底其他意外问题，便于排查。
• 参数化执行：用占位符%s传递参数，既避免SQL注入风险，也保证存储过程调用的语法正确性。
• 资源管理：最后显式关闭游标和连接，避免数据库资源泄漏。

额外注意事项 #

• 确保你的存储过程内部没有手动执行COMMIT，否则会破坏外层事务的原子性（存储过程的操作会提前提交）。
• 如果country_list数据量极大，可以考虑拆分批量事务，但核心逻辑仍需保证每组操作的原子性。

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