Pandas DataFrame.to_sql()在SQLAlchemy2.0.1上下文管理器中失效无报错

这是因为SQLAlchemy 2.0 对事务机制做了变更：engine.connect() 返回的连接上下文默认会开启一个事务，但不会自动提交。而 pandas 1.5.3 并未适配这个新特性，执行完to_sql后不会主动提交事务，当上下文退出时事务会被自动回滚，所以数据没写入表中。

以下是几种可行的解决方法：

方法1：在上下文内手动提交事务 #

直接在to_sql执行完成后调用连接的commit()方法，主动提交事务：

# python 3.10.6
import pandas as pd # 1.5.3
import psycopg2 # '2.9.5 (dt dec pq3 ext lo64)'
from sqlalchemy import create_engine # 2.0.1


def connector():
    return psycopg2.connect(**DB_PARAMS)

engine = create_engine('postgresql+psycopg2://', creator=connector)

with engine.connect() as connection:
    df.to_sql(
        name='my_table',
        con=connection,
        if_exists='replace',
        index=False,
    )
    connection.commit()  # 手动提交事务

方法2：使用engine.begin()替代engine.connect() #

engine.begin() 返回的上下文管理器会自动处理事务：执行成功时自动提交，出错时自动回滚，是更贴合这种场景的用法：

# python 3.10.6
import pandas as pd # 1.5.3
import psycopg2 # '2.9.5 (dt dec pq3 ext lo64)'
from sqlalchemy import create_engine # 2.0.1


def connector():
    return psycopg2.connect(**DB_PARAMS)

engine = create_engine('postgresql+psycopg2://', creator=connector)

# 改用engine.begin()
with engine.begin() as connection:
    df.to_sql(
        name='my_table',
        con=connection,
        if_exists='replace',
        index=False,
    )

方法3：配置连接为自动提交模式（不推荐） #

可以在创建引擎时设置隔离级别为自动提交，这样所有操作都会自动提交，但这种方式会失去事务的原子性保障，仅适合不需要事务的场景：

engine = create_engine(
    'postgresql+psycopg2://',
    creator=connector,
    isolation_level="AUTOCOMMIT"  # 开启自动提交
)

with engine.connect() as connection:
    df.to_sql(
        name='my_table',
        con=connection,
        if_exists='replace',
        index=False,
    )

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