使用SQLAlchemy Session时Pandas to_sql()抛出IntegrityError问题

核心原因 #

SQLAlchemy Session采用**延迟刷写（Lazy Flush）**的工作机制：你在Session中插入Master、level1、level2对象后，这些操作不会立即发送到数据库，仅保存在Session的内存缓存中。而Pandas的df.to_sql()是直接通过底层数据库连接执行批量INSERT，它无法感知Session缓存中未刷写的数据。当to_sql()执行时，数据库中还不存在外键依赖的Master/level1/level2记录，因此触发PostgreSQL的外键约束校验，抛出IntegrityError: reference object not in table。

你尝试的session.bind或session.get_bind()只是让to_sql()使用Session的底层连接，但无法解决Session缓存未同步到数据库的问题——连接本身没问题，问题是Session还没把之前的插入操作发送给数据库。

解决方法 #

方法1：手动刷写Session缓存（最可靠） #

在调用to_sql()前，手动执行session.flush()，将Session缓存中的所有插入/更新操作同步到数据库（但不提交事务）。此时数据库的当前事务中已存在外键依赖的记录，to_sql()的外键校验就能通过。

示例代码：

# 1. 向Session中添加级联表数据
master = Master(...)
level1 = Level1(master_id=master.id, ...)
level2 = Level2(level1_id=level1.id, ...)

session.add(master)
session.add(level1)
session.add(level2)

# 2. 手动刷写Session缓存到数据库（关键步骤）
session.flush()

# 3. 执行Pandas to_sql插入大表
df.to_sql(
    name='big_table',
    con=session.bind,
    if_exists='append',
    index=False
)

# 4. 提交整个事务
session.commit()

方法2：开启Session的自动刷写（可选） #

可以在创建Session时开启autoflush=True，这样当Session感知到需要访问数据库时（比如执行查询）会自动刷写缓存。但注意：to_sql()不属于Session的查询操作，因此这种方式不一定能触发自动刷写，仍推荐手动调用flush()。

示例代码：

from sqlalchemy.orm import sessionmaker

Session = sessionmaker(bind=engine, autoflush=True)
session = Session()

# 后续操作同方法1，可省略手动flush，但仍建议保留以确保可靠性

方法3：将to_sql操作纳入Session事务上下文（进阶） #

通过session.connection()获取当前事务的连接，并显式使用该连接执行to_sql，同时确保所有操作在同一个事务中。本质上和方法1类似，但更强调事务上下文的一致性：

with session.connection() as conn:
    # 先插入级联表数据并刷写
    session.add(master)
    session.add(level1)
    session.add(level2)
    session.flush()
    # 在同一个连接事务中执行to_sql
    df.to_sql('big_table', con=conn, if_exists='append', index=False)
session.commit()

补充说明 #

原脚本使用engine直接操作时正常，是因为engine的连接执行插入时，会立即将SQL发送到数据库（即使在事务中，当前连接的事务也能看到这些未提交的修改），不存在Session缓存延迟的问题。改用Session后，必须遵循其Unit of Work的工作模式，手动管理缓存刷写。

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