blocks|key|51719|text|一次执行所有操作并在流程结束时提交总是更有效。如果性能重要，commit会引发额外的处理，您不希望对每一行重复这些处理。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|51720|您执行的操作越多，性能效益就越大。另一方面，你需要考虑长期手术的副作用。例如，如果有多个进程同时插入，那么死锁的风险就会增加--特别是当出现重复的关键错误时。中间方法是分批插入。您可能想看看关于锁定机制的MYSQL文档。|51721|MySQL文档中有一个关于语句的有趣部分--以下是一些选择：|51722|load+data语法是最快可用的选项。|unordered-list-item|51723|使用多个values()列表也比运行多个inserts要快得多|51724|entityMap|0|LINK|mutability|MUTABLE|url|https://dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html|1|https://dev.mysql.com/doc/refman/8.0/en/insert-optimization.html^0|U|6|0|2N|E|0|0|D|2|1|0|0|9|0|4|8|K|7|0^^$0|@$1|2|3|4|5|6|7|Y|8|@$9|Z|A|10|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|11|8|@]|D|@$9|12|A|13|1|14]]|E|$]]|$1|H|3|I|5|6|7|15|8|@]|D|@$9|16|A|17|1|18]]|E|$]]|$1|J|3|K|5|L|7|19|8|@$9|1A|A|1B|B|C]]|D|@]|E|$]]|$1|M|3|N|5|L|7|1C|8|@$9|1D|A|1E|B|C]|$9|1F|A|1G|B|C]]|D|@]|E|$]]|$1|O|3|-4|5|6|7|1H|8|@]|D|@]|E|$]]]|P|$Q|$5|R|S|T|E|$U|V]]|W|$5|R|S|T|E|$U|X]]]]

It is always more efficient to perform all operations at once, and commit at the end of the process. <code>commit</code> incurs additional processing that you don't want to repeat for each and every row, if performance matters.

The more operations you perform, the greater the performance benefit. On the other hand, you need to consider the side effect of a long lasting operation. As an example, if you have several processes inserting concurrently, the risk of deadlock increases - especially if duplicate key errors arise. An intermediate approach is to insert in batches. You may want to have a look at <a href="https://dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html" rel="nofollow noreferrer">the MYSQL documentation on locking mechanisms</a>.

The MySQL documentation has an interesting section about <a href="https://dev.mysql.com/doc/refman/8.0/en/insert-optimization.html" rel="nofollow noreferrer">how to optimize <code>insert</code> statements</a> - here are a few picks:

<ul>
<li>the <code>load data</code> syntax is the fastest available option</li>
<li>using multiple <code>values()</code> lists is also quite faster than running multiple <code>inserts</code></li>
</ul>

blocks|key|896466|text|在python中，它应该是无动于衷的，因为必须在插入数据之前对其进行压缩。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|896467|因此，执行和执行之间应该没有什么区别，但是正如所述的这里|offset|length|896468|mysql主页也是州|896469|使用executemany()方法，不可能在操作参数中指定要执行的多个语句。这样做会引发InternalError异常。考虑在multi=True中使用execute()。|blockquote|896470|因此，如果您对性能有疑问，您可以查看一下金炼金术似乎要快一些，但要让它开始工作需要时间。|896471|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/3945642/why-is-executemany-slow-in-python-mysqldb|1|https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-executemany.html|2|https://www.sqlalchemy.org/^0|0|Q|2|0|0|9|1|1|0|0|K|4|2|0^^$0|@$1|2|3|4|5|6|7|Y|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Z|8|@]|9|@$D|10|E|11|1|12]]|A|$]]|$1|F|3|G|5|6|7|13|8|@]|9|@$D|14|E|15|1|16]]|A|$]]|$1|H|3|I|5|J|7|17|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|18|8|@]|9|@$D|19|E|1A|1|1B]]|A|$]]|$1|M|3|-4|5|6|7|1C|8|@]|9|@]|A|$]]]|N|$O|$5|P|Q|R|A|$S|T]]|U|$5|P|Q|R|A|$S|V]]|W|$5|P|Q|R|A|$S|X]]]]

In python should it be indifferent, because the data must be comited before they are inserted.

so there should be little difference between execute and executemanybut as stated <a href="https://stackoverflow.com/questions/3945642/why-is-executemany-slow-in-python-mysqldb">here</a>

the mysql homepage also <a href="https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-executemany.html" rel="nofollow noreferrer">states</a> 

<blockquote>
 With the executemany() method, it is not possible to specify multiple statements to execute in the operation argument. Doing so raises an InternalError exception. Consider using execute() with multi=True instead. 
</blockquote>

So if you have doubts about performance you can have a look at <a href="https://www.sqlalchemy.org/" rel="nofollow noreferrer">sqlalchemy</a> is seems to be a bit faster, but takes time to get it to work

blocks|key|896494|text|这里有一些提示:在/etc/+mysql+/my.cnf+(用于Ubuntu)中调优Mysql设置可以大大提高Mysql的性能。更多的memory%2Bcache通常更适合查询。创建一个非常长的文本与许多插入查询和分号将大大提高你的速度。将整个数据库保存在内存中可以获得最大的速度，但不适合大多数项目。mysql调优技巧见：https://duckduckgo.com/?q=mysql%2Btune%2Bfor%2Bspeed&t=newext&atb=v275-1&ia=web。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|896495|entityMap|0|LINK|mutability|MUTABLE|url|https://duckduckgo.com/?q=mysql%2Btune%2Bfor%2Bspeed&t=newext&atb=v275-1&ia=web^0|4G|21|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@$A|M|B|N|1|O]]|C|$]]|$1|D|3|-4|5|6|7|P|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]]]

Here some tips: Tune the mysql settings in /etc/mysql/my.cnf (for Ubuntu) can increase the performance of Mysql a lot. More memory+cache is usually better for queries. Create a very long text with many insert queries and semicolons will improve your speed a lot. Keeping the entire database in memory gives maximum speed, but not suitable for most projects. Tips for mysql tuning are at: <a href="https://duckduckgo.com/?q=mysql+tune+for+speed&amp;t=newext&amp;atb=v275-1&amp;ia=web" rel="nofollow noreferrer">https://duckduckgo.com/?q=mysql+tune+for+speed&amp;t=newext&amp;atb=v275-1&amp;ia=web</a>.

I am currently writing code to insert a bunch of object data into a mysql database through a plain python script. The amount of rows I need to insert is on the order of a few thousand. I want to be able to do this as fast as possible, and wanted to know if there is a performance difference between calling executeMany() on a bunch fo rows and then calling commit(), vs calling execute() many times and then calling commit()

Python mysql executemany() and commit vs many execute() and commit

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我目前正在编写代码，通过普通的python脚本将一堆对象数据插入mysql数据库。我需要插入的行数大约是几千行。我希望能够尽可能快地做到这一点，并想知道在一堆fo行上调用executeMany()与调用commit()、vs多次调用execute()、然后调用commit()之间是否存在性能差异。

问Python ()和commit与许多execute()和commit
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Python ()和commit与许多execute()和commitEN