CODEWITHSUNDEEP
pythonmysqlcursormysql-python
add-semi-colons
add-semi-colons

Reputation: 18810

Python MySQLdb handle multiple cursors: Command out of sync

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?

Upvotes: 1

Views: 1387

Answers (1)

panofish
panofish

Reputation: 7889

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;")

Upvotes: 3

Related Questions