Python + MySQL-批量插入 [英] Python+MySQL - Bulk Insert

问题描述

我正在使用Python中的MySQLdb模块与数据库进行交互.我遇到的情况是有一个非常大的列表(成千上万个元素)，需要将它们作为行插入到表中.

I'm working with the MySQLdb module in Python to interact with a database. I have a situation where there is a very large list (tens of thousands of elements) which I need to insert as rows into a table.

我现在的解决方案是生成一个大的INSERT语句作为字符串并执行它.

My solution right now is to generate a large INSERT statement as a string and execute it.

有没有更聪明的方法?

推荐答案

有一种更聪明的方法.

There is a smarter way.

批量插入的问题是默认情况下自动提交已启用从而导致每个insert语句在下一次插入可以启动之前被保存到稳定存储中.

The problem with bulk insertions is that by default autocommit is enabled thus causing each insert statement to be saved to stable store before the next insert can initiate.

如手册页所述:

默认情况下，MySQL以自动提交方式运行 模式已启用.这意味着尽快 当您执行一条语句时 更新(修改)表，MySQL 将更新存储在磁盘上以使其更新 永恒的.要禁用自动提交模式， 使用以下语句:

By default, MySQL runs with autocommit mode enabled. This means that as soon as you execute a statement that updates (modifies) a table, MySQL stores the update on disk to make it permanent. To disable autocommit mode, use the following statement:

SET autocommit=0; 

禁用后 通过设置自动提交模式 自动提交变量为零，更改 到交易安全表(例如 InnoDB，BDB或NDBCLUSTER的那些) 不是立即成为永久的. 您必须使用COMMIT来存储您的 更改磁盘或回滚以忽略 变化.

After disabling autocommit mode by setting the autocommit variable to zero, changes to transaction-safe tables (such as those for InnoDB, BDB, or NDBCLUSTER) are not made permanent immediately. You must use COMMIT to store your changes to disk or ROLLBACK to ignore the changes.

这是RDBMs系统的一个相当普遍的功能，它假定数据库的完整性至关重要.它确实使批量插入的时间约为每个插入1s，而不是1ms.另一种选择是使用超大插入语句来尝试实现此单次提交，但有可能使SQL解析器超载.

This is a pretty common feature of RDBMs systems which presume that database integrity is paramount. It does make bulk inserts take on the order of 1s per insert instead of 1ms. The alternative of making an overlarge insert statement tries to achieve this single commit at risk of overloading the SQL parser.

这篇关于Python + MySQL-批量插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！