title: Ways to avoid MySQLdb's "Commands out of sync; you can't run this command now" (2014) exception
body: <p>Following code, using python 2.6.6 and MySQLdb 1.2.2 causes <em>Commands out of sync; you can't run this command now</em> MySQLdb exception:</p>
  
  <pre><code>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;" )
  </code></pre>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>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?</p>
  
  <p>Thanks in advance for your time and help :)</p>
  
  <p><strong>Edit:</strong>
  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:</p>
  
  <pre><code>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 );
  </code></pre>
  
  <p>The problem is that I don't know what does it do (it returns <code>1</code> two times and <code>None</code> after that). Should I dig into this direction? I mean, should I understand concept of these sets to find solution to my problem?</p>