Python 避免 MySQLdb 的“命令不同步；您现在无法运行此命令”（2014）异常的方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4707957/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Ways to avoid MySQLdb's "Commands out of sync; you can't run this command now" (2014) exception
提问by Dariusz Walczak
Following code, using python 2.6.6 and MySQLdb 1.2.2 causes Commands out of sync; you can't run this command nowMySQLdb exception:
以下代码，使用 python 2.6.6 和 MySQLdb 1.2.2 导致命令不同步；你现在不能运行这个命令MySQLdb 异常：
import MySQLdb
conn = MySQLdb.connect( db="test", user="root" )
cursor = conn.cursor( MySQLdb.cursors.DictCursor )
cursor.execute( "BEGIN; CREATE TABLE t1 ( t1_id INT PRIMARY KEY AUTO_INCREMENT ); COMMIT;" )
cursor.execute( "BEGIN; CREATE TABLE t2 ( t2_id INT PRIMARY KEY AUTO_INCREMENT ); COMMIT;" )
The exception is raised during execution of the second query. As I read, the exception is generally caused by limitations of MySQL's C API implementation, which disallow concurrent query execution.
在执行第二个查询期间引发异常。正如我所读到的，异常通常是由 MySQL 的 C API 实现的限制引起的，这些限制不允许并发查询执行。
If I recreate cursor object between above two queries, the problem is worked around but unfortunatelly the solution doesn't seem perfect to me. I have a very simple abstration over database connection and query execution and would prefer not to recreate the cursor after each query execution as it will (as far as I understand it) commit the current transaction and potentially have other side effects.
如果我在上述两个查询之间重新创建游标对象，问题就解决了，但不幸的是，该解决方案对我来说似乎并不完美。我对数据库连接和查询执行有一个非常简单的抽象，并且不希望在每次查询执行后重新创建游标，因为它会（据我所知）提交当前事务并可能产生其他副作用。
Therefore, my question is: What are other ways of avoiding this exception? How to prepare the cursor object for execution of next query? Maybe there is some method expected by the Python DB API, which would be relatively neutral when using other database interfaces and will work around the problem in case of MySQLdb?
因此，我的问题是：还有哪些其他方法可以避免此异常？如何准备游标对象以执行下一个查询？也许 Python DB API 期望有一些方法，在使用其他数据库接口时会相对中立，并且在 MySQLdb 的情况下会解决问题？
Thanks in advance for your time and help :)
提前感谢您的时间和帮助:)
Edit:After I posted the question, I started to read through the Python DB API spec to read about side effects of cursor destruction (I am not so sure about transaction commit anymore :)) and I found following, alternative work around:
编辑：发布问题后，我开始通读 Python DB API 规范以了解游标破坏的副作用（我不再确定事务提交了 :)），我发现以下替代解决方法：
cursor.execute( "BEGIN; CREATE TABLE t1 ( t1_id INT PRIMARY KEY AUTO_INCREMENT ); COMMIT;" )
while cursor.nextset() is not None: pass
cursor.execute( "BEGIN; CREATE TABLE t2 ( t2_id INT PRIMARY KEY AUTO_INCREMENT );
The problem is that I don't know what does it do (it returns 1two times and Noneafter that). Should I dig into this direction? I mean, should I understand concept of these sets to find solution to my problem?
问题是我不知道它做了什么（它返回1两次，None之后返回）。我应该深入这个方向吗？我的意思是，我应该理解这些集合的概念来找到我的问题的解决方案吗？
采纳答案by Glenn Maynard
DB-API tries to handle transactions on its own, starting a transaction on the first command and having its own API call to commit it, so:
DB-API 尝试自己处理事务，在第一个命令上启动一个事务并有自己的 API 调用来提交它，所以：
cursor.execute( "CREATE TABLE t1 ( t1_id INT PRIMARY KEY AUTO_INCREMENT )" )
cursor.commit()
cursor.execute( "CREATE TABLE t2 ( t2_id INT PRIMARY KEY AUTO_INCREMENT )" )
cursor.commit()
In my opinion, this is a serious, glaring design error of Python's DB-API, making it a serious hassle to execute commands outside of transactions and to have proper control over transactions, eg. to use things like SQLite's BEGIN EXCLUSIVE TRANSACTION. It's as if someone with no real database experience was allowed to design the API...
在我看来，这是 Python 的 DB-API 的一个严重的、明显的设计错误，使得在事务之外执行命令并对事务进行适当的控制变得非常麻烦，例如。使用诸如 SQLite 的BEGIN EXCLUSIVE TRANSACTION. 就好像没有真正的数据库经验的人被允许设计 API 一样......
