Ways to avoid MySQLdb's "Commands out of sync; you can't run this command now" (2014) exception

Question

Following code, using python 2.6.6 and MySQLdb 1.2.2 causes Commands out of sync; you can't run this command now MySQLdb exception:

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.

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?

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:

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 1 two times and None after that). Should I dig into this direction? I mean, should I understand concept of these sets to find solution to my problem?

Answer 1

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:

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...