mysqldb cursor.close() throws ProgrammingError: (2014, "commands out of sync...") when executing sql read from file

Question

As part of a larger debugging effort, I've run into the following bug using mysqldb:

File "x.py" line x, in method
    cursor.close()
File "y.py" line 100, in close
    while self.nextset(): pass
File "z.py" line 137, in nextset
    self._waring_check()
...
Exception _mysql_exceptions.ProgrammingError: (2014, "Commands out of sync; you can't run this command now") in <bound method Cursor.__del__ of <MySQLdb.cursor.Cursor object at 0x000002373198>> ignored

The relevant pieces of my code are as follows:

connection = mysqldb.connect('localhost', 'root', 'password', 'db')
cursor = connection.cursor()
file = open(filename, 'r')
sql = s = " ".join(file.readlines)
cursor.execute(sql)
cursor.close()
...

The error is thrown on the cursor.close() line before I get into anything else, which makes no sense to me... Anybody know what I'm doing wrong? There's only a single thread used in all the code.

tokland · Accepted Answer · 2014-10-14 11:30:40Z

7

In case anybody else runs into this error, my problem was the the file I was reading had multiple sql statements in it (separated by ;s). The cursor.execute() could only handle one of them at a time, or something, and was freaking out when I tried to close it.

Solution:

connection = mysqldb.connect('localhost', 'root', 'password', 'db')
file = open(filename, 'r')
sql_statements = " ".join(file.readlines())
for sql in sql_statements.split(";"): //given file, may need ";\n"
    cursor = connection.cursor()
    cursor.execute(sql)
    cursor.close()

edited Oct 14, 2014 at 11:30

tokland

68.2k13 gold badges151 silver badges174 bronze badges

answered Nov 20, 2013 at 7:47

user2048643

2831 gold badge4 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Pikamander2 Over a year ago

Doing it one statement at a time fixed my problem, but I don't understand why. Sometimes I'm able to execute multiple statements at a time, and sometimes I'm not. Is there a way to execute multiple statements together without running into this error?

Collectives™ on Stack Overflow

mysqldb cursor.close() throws ProgrammingError: (2014, "commands out of sync...") when executing sql read from file

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related