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>
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")
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.
Related posts:
