Python+MySQL - 批量插入

Question

Python+MySQL - 批量插入

pythonmysql

23

我正在使用Python中的MySQLdb模块与数据库进行交互。我遇到了这样一种情况：有一个非常大的列表（成千上万个元素），我需要将其作为行插入到表格中。

目前，我的解决方案是生成一个大的INSERT语句，并执行它。

有更聪明的方法吗？

- Mike

3个回答

14

如果你需要插入非常大量的数据，为什么要试图在一个单独的insert中插入所有数据？这会不必要地给内存带来负担，在制作这个大的insert字符串和执行它时也会如此。如果要插入的数据非常非常大，这也不是一个很好的解决方案。

为什么不将每行数据放在一个insert命令中，并使用for...loop将所有行放入数据库，最后提交所有更改呢？

con = mysqldb.connect(
                        host="localhost",
                        user="user",
                        passwd="**",
                        db="db name"
                     )
cur = con.cursor()

for data in your_data_list:
    cur.execute("data you want to insert: %s" %data)

con.commit()
con.close()

相信我，这真的非常快，但如果您得到更慢的结果，则意味着您的autocommit必须为True。像msw说的那样将它设置为False。

- Pushpak Dagade

1

如果每次插入都有一个语句，那么速度不会很慢吗？我不介意使用内存。它只有几兆字节，所以我不担心。 - Mike

不会变慢，这就是我想说的...只要你不在循环中间提交。试试两种方法，看看你是否相信我... - Pushpak Dagade

在我使用的MyISAM引擎中，执行后不会隐式地提交吗？ - Mike

2

我不清楚MYISAM是否如此，因为我不使用它，但如果是的话，您可以通过在mysql shell中执行此行 SET autocommit = 0; 来关闭提交（或者在您的python程序中这样做，请使用cur.execute('SET autocommit = 0'); con.commit()）。 - Pushpak Dagade

1

我意识到这篇文章可能已经很老了，在它被写出来的时候也许并非如此，但是文档建议使用多行插入来减少通信开销。http://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-bulk-data-loading.html - Dean MacGregor

1

只是一个小提示：当想要像这样向插入语句添加数据时： for data in your_data_list: cur.execute("data you want to insert: %s" %data)，会打开 SQL 注入攻击的漏洞。最好使用以下方式：for data in your_data_list: cur.execute("data you want to insert: %s",item 1, item 2) （在 SQL 语句字符串后面的每个逗号都将使用项目替换 SQL 字符串中的 ?）请参阅 https://mkleehammer.github.io/pyodbc/，查看“插入数据”下的更多信息。愉快编码！ - Colby

1

只要您将其作为单个插入而不是成千上万的单独插入进行，那么这确实是最佳方法。注意不要超过mysql的最大数据包大小，并在必要时进行调整。例如，此命令将服务器数据包最大值设置为32Mb。您还需要在客户端上执行相同的操作。

mysqld --max_allowed_packet=32M

- justinhj

这是绕过事务机制的一种方法，它只是解决了症状而没有解决根本问题。 - msw

你能详细说明一下吗？或者说你会做什么代替它？ - justinhj

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

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

- msw · Accepted Answer

有一种更聪明的方法。

批量插入的问题在于，默认情况下，自动提交已启用，这导致每个insert语句在开始下一个插入之前都会保存到稳定存储器中。

正如手册页所述：

默认情况下，MySQL 运行时启用自动提交模式。这意味着一旦您执行更新（修改）表的语句，MySQL 就会将更新保存到磁盘上以使其永久化。要禁用自动提交模式，请使用以下语句：

SET autocommit=0;

将autocommit变量设置为零以禁用自动提交模式后，对于事务安全表（如InnoDB、BDB或NDBCLUSTER），所做的更改不会立即永久保存。您必须使用COMMIT将更改存储到磁盘上，或者使用ROLLBACK忽略更改。

这是关系型数据库系统的常见功能，它默认保证了数据库的完整性。这会使得批量插入操作需要大约1秒每次而不是1毫秒。另一种方法是尝试制作一个过大的插入语句，以此冒着超载SQL解析器的风险来实现单个提交。