将pandas Dataframe批量插入SQL Server的高效实现方法

需要将pandas DataFrame中存储的更新数据同步到SQL表，当前有近10万条数据需要遍历写入，执行耗时很长，需要找到提升代码效率的方案。同时需要确认是否需要先执行数据截断操作，当前表中大部分行数据与待写入数据一致。

原有实现代码 #

conn = pyodbc.connect ("Driver={xxx};"
            "Server=xxx;"
            "Database=xxx;"
            "Trusted_Connection=yes;")
cursor = conn.cursor()
cursor.execute('TRUNCATE dbo.Sheet1$') 

for index, row in df_union.iterrows():
    print(row)
    cursor.execute("INSERT INTO dbo.Sheet1$ (Vendor, Plant) values(?,?)", row.Vendor, row.Plant)

原有代码耗时高的核心原因有两个：一是iterrows逐行遍历DataFrame本身效率极低，二是单条数据单次提交写入，10万条数据需要和数据库交互10万次，网络IO和数据库执行开销极高。

关于数据截断操作：如果业务需求是全量覆盖同步SQL表内容，那截断是合理的，但如果仅需要更新差异数据，可以不用全量截断，改用增量更新逻辑进一步降低开销。

你最终采用的to_sql方案已经做了充分优化，核心优化点如下：

• 直接调用pandas原生to_sql方法，无需手动遍历DataFrame，底层内置批量写入逻辑
• chunksize=1000配置：将10万条数据拆分成分批写入，每批写入1000条，大幅减少和数据库的交互次数，同时避免单次写入数据量过大导致内存溢出
• method='multi'配置：开启多值插入模式，单条SQL语句插入多条数据，大幅降低SQL执行开销
• if_exists='replace'参数会自动完成旧表的覆盖操作，不需要手动执行TRUNCATE语句，减少手动操作出错概率

如果后续不需要全量覆盖，只想更新差异数据，可以先从SQL表读取现有数据，和待写入DataFrame做差异比对，仅将新增、修改的数据写入SQL，能进一步提升同步效率。

最终高效实现代码 #

params = urllib.parse.quote_plus(r'DRIVER={xxx};SERVER=xxx;DATABASE=xxx;Trusted_Connection=yes')

conn_str = 'mssql+pyodbc:///?odbc_connect={}'.format(params)
engine = create_engine(conn_str)
df = pd.read_excel('xxx.xlsx')
print("loaded")
df.to_sql(name='tablename',schema= 'dbo', con=engine, if_exists='replace',index=False, chunksize = 1000, method = 'multi')

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