SQLAlchemy导入Access数据到SQL Server时遇IDENTITY_INSERT错误

问题核心是SET IDENTITY_INSERT的作用域仅限当前事务，而默认情况下df.to_sql会开启独立事务，导致之前的设置失效。以下是几种可行的解决方法：

方法一：共享连接与手动事务控制 #

直接使用同一个数据库连接执行SET IDENTITY_INSERT和数据插入，手动管理事务生命周期：

import pandas as pd
from sqlalchemy import create_engine

# 1. 从Access读取并处理数据（你的原有代码逻辑）
access_engine = create_engine("access+pyodbc:///path/to/your/access/db.accdb")
df = pd.read_sql("SELECT * FROM source_table", access_engine)
# 处理空值、重命名列、添加固定日期列...

# 2. 连接SQL Server并手动控制事务
sql_server_engine = create_engine(
    "mssql+pyodbc://user:password@server/database?driver=ODBC+Driver+17+for+SQL+Server",
    fast_executemany=True  # 保留该设置提升插入速度
)

with sql_server_engine.connect() as conn:
    trans = conn.begin()
    try:
        # 开启IDENTITY_INSERT
        conn.execute("SET IDENTITY_INSERT tblsummary ON")
        # 用同一个连接执行数据插入，if_exists按需设置为append/replace等
        df.to_sql(
            name="tblsummary",
            con=conn,
            if_exists="append",
            index=False,
            chunksize=1000  # 可选：分块插入缓解内存压力
        )
        # 关闭IDENTITY_INSERT
        conn.execute("SET IDENTITY_INSERT tblsummary OFF")
        trans.commit()
    except Exception as e:
        trans.rollback()
        raise e

方法二：自定义to_sql插入方法 #

通过method参数将IDENTITY_INSERT的设置嵌入插入流程：

def insert_with_identity(conn, table, keys, data_iter):
    # 开启IDENTITY_INSERT
    conn.execute(f"SET IDENTITY_INSERT {table.name} ON")
    # 调用Pandas默认批量插入逻辑
    from pandas.io.sql import SQLTable
    SQLTable(table.name, conn, keys=keys, table=table).insert(data_iter)
    # 关闭IDENTITY_INSERT
    conn.execute(f"SET IDENTITY_INSERT {table.name} OFF")

# 调用to_sql时指定自定义方法
df.to_sql(
    name="tblsummary",
    con=sql_server_engine,
    if_exists="append",
    index=False,
    method=insert_with_identity
)

方法三：跳过标识列（无需保留原有值时） #

如果目标表的标识列可由SQL Server自动生成，直接从DataFrame中移除该列即可：

# 假设标识列名为'id'，从DataFrame中删除
df = df.drop(columns=["id"])
# 直接执行插入
df.to_sql(
    name="tblsummary",
    con=sql_server_engine,
    if_exists="append",
    index=False,
    fast_executemany=True
)

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