如何禁用pandas DataFrame.to_sql的自动提交以实现事务回滚？

问题根源 #

你之前的上下文管理器错误地将SQLite连接设置为isolation_level = None（自动提交模式），这会导致每次执行SQL语句后自动提交事务——包括begin;之后的to_sql操作，所以到commit时已经没有打开的事务，从而抛出错误。同时，pandas使用原生sqlite3连接时，默认会遵循连接的自动提交特性，导致事务提前结束。

解决方案 #

方案1：修正原生sqlite3连接的事务上下文管理器 #

不要将连接设置为自动提交模式，保留或显式设置事务隔离级别，确保to_sql的操作被包含在手动事务中：

import sqlite3
from contextlib import contextmanager

@contextmanager
def transaction(connection: sqlite3.Connection):
    # 保存原隔离级别，避免影响其他操作
    original_isolation = connection.isolation_level
    try:
        # 显式设置事务隔离级别（SQLite默认是DEFERRED，可省略）
        connection.isolation_level = 'DEFERRED'
        yield connection
        connection.commit()
    except Exception:
        connection.rollback()
        raise
    finally:
        # 恢复原隔离级别
        connection.isolation_level = original_isolation

# 使用示例
conn = sqlite3.connect('local.db')
with transaction(conn):
    # 读取Oracle数据
    df = pd.read_sql_query("SELECT * FROM oracle_table", oracle_conn)
    # 写入SQLite，操作处于事务中，不会自动提交
    df.to_sql('target_table', conn, if_exists='replace', index=False)
    # 执行验证逻辑
    count_result = conn.execute("SELECT COUNT(*) FROM target_table").fetchone()[0]
    if count_result != len(df):
        raise Exception("数据行数不匹配，验证失败")
conn.close()

方案2：使用SQLAlchemy连接（更推荐） #

SQLAlchemy对事务的支持更稳定，且pandas与SQLAlchemy集成时，默认不会自动提交事务，完全由上下文管理器控制：

from sqlalchemy import create_engine
import pandas as pd

# 创建SQLAlchemy引擎
sqlite_engine = create_engine('sqlite:///local.db')

# 使用engine.begin()自动管理事务：无异常则提交，有异常则回滚
with sqlite_engine.begin() as conn:
    # 从Oracle读取数据
    oracle_df = pd.read_sql_query("SELECT * FROM oracle_table", oracle_conn)
    # 写入SQLite，操作被包含在事务中
    oracle_df.to_sql('target_table', conn, if_exists='replace', index=False)
    # 验证数据
    verify_count = pd.read_sql_query("SELECT COUNT(*) FROM target_table", conn).iloc[0,0]
    if verify_count != len(oracle_df):
        raise ValueError("数据验证失败，回滚事务")

关键说明 #

• 禁止在事务上下文里设置isolation_level = None，这会强制SQLite进入自动提交模式，破坏手动事务逻辑。
• 使用SQLAlchemy的方式无需手动处理commit/rollback，上下文管理器会自动完成，代码更简洁可靠。

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