如何加快 pandas.DataFrame .to_sql 的插入速度

您好，我目前正在尝试将四个 pandas 数据帧中的数据写入本地计算机上的 mysql ，我的计算机需要 32 秒插入 20,000 条记录（每个表 5000 条）代码-

表格 - 1）帖子 2）post_stats 3) 后语言 4) 帖子标签

engine = create_engine("mysql+mysqldb://root:dbase@123@localhost/testDb")

startTime=time.time()

dfstat.to_sql('post_stats', con=engine, if_exists='append', index=False)
for i in range(0, dfp.shape[0]):
ss = str(dfp.iloc[i][0])
sss = 'Select id from post_stats where post_id =\"%s\"' % (ss)
#print(sss)
rss = engine.execute(sss)
x = rss.fetchone()
dfp['stats_id'][i] = x[0]
dfp.to_sql('posts', con=engine, if_exists='append', index=False)
dfl.to_sql('post_languages', con=engine, if_exists='append', index=False)
dftagv.to_sql('post_tags', con=engine, if_exists='append', index=False)


endTime=time.time()
diff=endTime-startTime 
print(diff)

当前我将数据存储在本地计算机中，但将来我必须将数据发送到 mysql 服务器，有什么方法可以加快插入速度 或者是否有任何不同的方法，以便我可以以更快的速度存储数据，例如使用批量插入。请建议

这里的问题是对每一行进行插入查询，然后在下一行插入之前等待 ACK。

尝试先运行此代码片段import pandas as pd

from pandas.io.sql import SQLTable

def _execute_insert(self, conn, keys, data_iter):
    print("Using monkey-patched _execute_insert")
    data = [dict((k, v) for k, v in zip(keys, row)) for row in data_iter]
    conn.execute(self.insert_statement().values(data))

SQLTable._execute_insert = _execute_insert

这是一个补丁nhockham https://github.com/nhockham在 to_sql insert 上逐行插入。这是 github 问题。 https://github.com/pandas-dev/pandas/issues/8953

如果您可以放弃使用 pandas.to_sql 我建议您尝试 sql-alchemy 批量插入或只是编写脚本自己进行多行查询。

编辑： 为了澄清我们正在修改 pandas.io.sql 中 SQLTable 类的 _execute_insert 方法 因此，必须在导入 pandas 模块之前将其添加到脚本中。

最后一行是更改。

conn.execute(self.insert_statement(), data)已更改为：

conn.execute(self.insert_statement().values(data))

第一行将逐行插入，而最后一行将在一个 sql 语句中插入所有行。

更新：对于较新版本的 pandas，我们需要对上述查询进行轻微修改。

from pandas.io.sql import SQLTable

def _execute_insert(self, conn, keys, data_iter):
    print("Using monkey-patched _execute_insert")
    data = [dict(zip(keys, row)) for row in data_iter]
    conn.execute(self.table.insert().values(data))

SQLTable._execute_insert = _execute_insert