如何用Python将Pandas数据分批次批量插入SQL Server表

方案1：手动分批次批量插入（支持自定义每批10000行） #

原有代码性能极低的核心原因是逐行调用execute()执行单条INSERT语句，178万行数据会产生178万次数据库交互，必然会超时或卡顿。使用executemany批量提交+分批次处理即可解决：

cursor2 = conn2.cursor()
# 开启pyodbc批量插入优化开关，性能可提升数十倍
cursor2.fast_executemany = True

# 清空目标表
cursor2.execute("truncate table APriori_test")
conn2.commit()

# 预处理插入参数
insert_params = dataset[["antecedents", "consequents", "support", "confidence"]].values.tolist()
# 每批次写入行数
batch_size = 10000

# 分批次插入
for i in range(0, len(insert_params), batch_size):
    current_batch = insert_params[i:i+batch_size]
    cursor2.executemany(
        "INSERT INTO APriori_test(antecedents,consequents,support,confidence) VALUES (?,?,?,?)",
        current_batch
    )
    # 每批次提交一次，避免事务过大导致数据库卡顿
    conn2.commit()

cursor2.close()

方案2：Pandas内置to_sql方法（代码最简洁） #

如果不需要自定义特殊插入逻辑，直接用Pandas封装好的to_sql方法即可，无需手动处理批量拆分：

from sqlalchemy import create_engine

# 替换为你自己的数据库连接信息，驱动版本与你本地安装的ODBC驱动保持一致即可
engine = create_engine("mssql+pyodbc://你的用户名:你的密码@SQL服务器地址/数据库名?driver=ODBC+Driver+17+for+SQL+Server")

# 清空目标表
with engine.connect() as conn:
    conn.execute("TRUNCATE TABLE APriori_test")
    conn.commit()

# 直接写入SQL Server
dataset.to_sql(
    name="APriori_test",
    con=engine,
    if_exists="append",
    index=False, # 不写入Pandas索引列
    chunksize=10000 # 每批次写入10000行
)

其他优化建议 #

• 插入前可暂时关闭表上的非聚集索引、触发器，插入完成后再重建，可进一步提升写入速度
• 提前给SQL Server对应的数据库文件、事务日志文件预分配足够存储空间，避免插入过程中自动扩容拖慢性能
• 若后续数据量进一步增大，可优先考虑SQL Server原生的bcp工具或BULK INSERT语句：先将DataFrame导出为无冗余格式的CSV文件，再调用原生批量导入命令写入，速度比普通批量INSERT高2~3倍

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