Python’s MySQLdb 2014 Error – Commands out of sync

18 Dec 2009 — eric

While writing a simple Python script to access and process data in a database, I came across an error that said:

Error 2014: Commands out of sync; you can't run this command now

After quite a bit of Googling and with very little findings, I had to dive in a little and try to figure out what was going on. The whole error looked like this:

[elubow@web7 scripts]$ ./2014_test.py
Traceback (most recent call last):
  File "build/bdist.linux-x86_64/egg/MySQLdb/cursors.py", line 173, in execute
    self.errorhandler(self, exc, value)
  File "build/bdist.linux-x86_64/egg/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
ProgrammingError: (2014, "Commands out of sync; you can't run this command now")
Error 2014: Commands out of sync; you can't run this command now
Exception _mysql_exceptions.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'test_table' at line 1") in
<bound method Cursor.__del__ of <MySQLdb.cursors.Cursor object at 0x2aac002f9d90>> ignored
</bound>

The issue turned out to be a very simple one to fix. Apparently MySQLdb doesn’t support compound MySQL statements. That is to say that this won’t work:

cursor.execute('SELECT COUNT(*) FROM people; SHOW TABLES LIKE addresses;')

But this will work:

cursor.execute("SELECT COUNT(*) FROM people")
cursor.close()
cursor = conn.cursor()
cursor.execute("SHOW TABLES LIKE addresses")

Make sure you break up your SQL statements into separate execute statements and you should avoid this MySQL 2014 error.

No related posts.

Posted in python. Tags: , . 6 Comments »
  • pete

    fyi, you don't have to use the close() method and redefine
    cursor.execute(sql1)
    and cursor.execute(sql2)
    works too.

  • Robin

    Thanks a lot! I've pretty much driving myself trying to find the solution, lol.

  • Will

    thanks eric, i had this problem too.

    theMySQLdb docs really could do with improving ^_^

  • RenderRob

    This issue is still not resolved for me. For one thing, I’m not using a cursor object and I’d like to keep it that way, since I hear they are 30 times slower than the alternative. Besides that, they make very little sense to me.

    I have written a primitive query editor in wxpython, but this stupid error keeps biting me in the @$$. If I do I transaction then I can’t do anything else after it, all I get is the error.

    If I insert data into a table and then call commit, I get the error again!

    How do I make it go away? This is ridiculous.

  • Cals Lundin

    inline comments are ok within the statement.  however, make sure that you dont have any comments after the end delimiter ‘;’. i.e. If you read your sql from a file and have the last line like

    – */it will trigger the same error

  • Jamie Simon

    Very useful, solved my issue. Thanks!

«

»