Python MySQLdb handle multiple cursors: Command out of sync

Question

I have a database connection method that creates the connection and sets the cursor as a part of init process.

Then I have following method that uses the cursor:

def calculatePercentile(self):
    user_edits = ur'''SELECT /* SLOW_OK_LIMIT: 1800 */ user_id, user_editcount from user'''
    num_user_per_percentile = ur'''SELECT /* SLOW_OK_LIMIT: 1800 */ count(user_id) from user where user_editcount = %(count)s'''        
    lang_edit_count_dictionary = {}
    lang_edit_count_dictionary[self.language] = []
    edit_count_list = []
    p = [10, 20, 30, 40, 50, 60, 70, 80, 90, 95]
    bot_users = self._getBotUsers()
    success = False
    attempts = 0
    while attempts < 3 and not success:
        try:
            self.dbCursor.execute(user_edits)
            for user in self.dbCursor.fetchall():
                user_id = user['user_id']
                user_editcount = user['user_editcount']
                if user_id not in bot_users:
                    edit_count_list.append(user_editcount)
            edit_count_list.sort()
            for i in p:
                lang_edit_count_dictionary[self.language].append(np.percentile(edit_count_list, i))
            success = True
        except MySQLdb.OperationalError, sqlEx:
            attempts += 1
            if sqlEx[0] == 2006:
                logging.info("Caught the MySQL server gone away exception")
                logging.error(sqlEx)
                time.sleep(10)
                self.connectServer()
        except Exception, e:
            traceback.print_exc()
            logging.exception(e)
    for key, values in lang_edit_count_dictionary.iteritems():
        print key
        for value in values:
            self.dbCursor.execute(num_user_per_percentile, {"count":value})
            uEditCount = self.dbCursor.fetchone()
            print uEditCount

What this method does is, it execute one query gets its data and dump that data in to and then uses the same cursor to execute another query inside:

for key, values in lang_edit_count_dictionary.iteritems():
    print key
    for value in values:
        self.dbCursor.execute(num_user_per_percentile, {"count":value})
        uEditCount = self.dbCursor.fetchone()
        print uEditCount

What happens is that I get the following error:

    self.dbCursor.execute(num_user_per_percentile, {"count":value})
  File "/home/auduwage/code/vInterLang/local/lib/python2.7/site-packages/MySQLdb/cursors.py", line 201, in execute
    self.errorhandler(self, exc, value)
  File "/home/auduwage/code/vInterLang/local/lib/python2.7/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
_mysql_exceptions.ProgrammingError: (2014, "Commands out of sync; you can't run this command now")
Exception _mysql_exceptions.ProgrammingError: (2014, "Commands out of sync; you can't run this command now") in <bound method SSDictCursor.__del__ of <MySQLdb.cursors.SSDictCursor object at 0x2716ed0>> ignored

Prior to this I have use this same cursor in different methods, I don't think this is related to MySql not allowing me to run multiple queries one after the other using same cursor? or is it? What would be the solution?

panofish · Accepted Answer · 2014-10-14 14:46:09Z

3

You can't run compound sql statements. You need to split them up.

This will return that error:

cursor.execute("drop database x; drop table y;")

This will work fine:

cursor.execute("drop database x;")
cursor.execute("drop table y;")

edited Oct 14, 2014 at 14:46

answered Dec 12, 2013 at 22:39

panofish

7,95913 gold badges62 silver badges100 bronze badges

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

Collectives™ on Stack Overflow

Python MySQLdb handle multiple cursors: Command out of sync

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related