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

Question

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

3

你好，我目前正在尝试将四个Pandas数据帧中的数据写入到我本地的MySQL数据库中。我的机器需要32秒才能插入20,000条记录（每个表格5000条）。代码如下：

表格： 1）文章 2）文章统计 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服务器，有没有加快插入速度的方法，或者是否有任何不同的方法可以以更快的速度存储数据，例如使用批量插入，请给出建议。

- Aadi

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Nihal Sangeeth · Accepted Answer

问题在于对于每一行数据，都会进行一次插入查询，然后在下一行插入之前等待确认。

在导入 pandas 库之前尝试运行此代码片段。

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提供的针对 to_sql 插入逐行插入的补丁。这是github问题页面。如果您可以放弃使用 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