Python's MySQLdb 2014 Error – Commands out of sync

By eric

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

1
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:

1
2
3
4
5
6
7
8
9
10
11
[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:

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

But this will work:

1
2
3
4
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.

Follow My Travels

Buy My Book

Archives

  • 2019
  • 2017
  • 2014
  • 2013
  • 2012
  • 2011
  • 2010
  • 2009
  • 2008
  • 2007
  • 2006

New Posts By Email

writing