Python MySQLdb empty select query although manual query execution delivers results

Question

I have written a python daemon that continuously polls a mysql database. It works fine when I continuously connect and reconnect to the database between queries as follows:

def connect(self):
    self.connection = MySQLdb.connect(...)  
    self.cursor = self.connection.cursor()  
    return self.cursor

def disconnect(self): ...
    self.cursor.close()
    self.connection.close()

def getData(); ....
   sqlcmd = """SELECT ...."""
   self.cursor.execute (sqlcmd % (params))
   result =  self.cursor.fetchall()
   return result

if __name__ == "__main__":
    db = prepaid_db.Database()
    while 1:
        dbConnection = db.connect()
        data = db.getData()
        ... do stuff
        db.disconnect

But when I try to keep the database connection open (as below) I get an empty query, even though, while it is running I can query the db manually, give it the same query and get the result I expect.

if __name__ == "__main__":
    db = prepaid_db.Database()
    dbConnection = db.connect()
    while 1:
        data = db.getData()
        ... do stuff
    db.disconnect

I have tried everything to understand why it would do this:

disabled query cache and added random x=x to the query in case mysql cache was confused by the similar queries
enabled mysql query logging: the query comes through but still returns an empty set
moved cursor.connect to database.connect, and back into getData(), no difference

I would love a clue as to what I am not understanding.

Does self.cursor.fetchall() return None the first time through the while-loop, or after many passes? — unutbu
– unutbu, Commented Oct 13, 2011 at 10:45
- it gave the right answer when first opened, but not during the while loop. Simon's answer below resolved the issue and makes perfect sense. — Michael Franze
– Michael Franze, Commented Oct 13, 2011 at 11:12
Man you read my mind, I was having the same problem but didn't think anyone else would have it :D — AliBZ
– AliBZ, Commented Jul 23, 2013 at 16:47

Simon · Accepted Answer · 2011-10-13 10:45:46Z

11

You're probably querying an InnoDB table where another process inserts new data in the meantime. If that is the case, the MySQL sever automatically starts a new transaction for your connection, and since you don't call dbConnection.commit() or .rollback()anywhere, you're forever stuck in that transaction. InnoDB's default settings make sure that whenever you query data, you'll always see the same result within one transaction. So whatever some other process is inserting into the table is hidden from your daemon's connection.

The solution is simple: Instead of calling db.disconnect(), call dbConnection.commit(), which ends the current and starts a new transaction.

answered Oct 13, 2011 at 10:45

Simon

12.5k4 gold badges37 silver badges39 bronze badges

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

2 Comments

Michael Franze Over a year ago

thanks Simon, that was the only InnoDB table in my DB and I didn't think that it would have an impact on a Select statement. But clearly it does, I added the commit() and now it works perfectly! thank you very, very much!

AliBZ Over a year ago

maaaaaaaan ! that was awesome, I thought db.commit() is only for updating stuff. That saved my life bro.

RedBaron · Accepted Answer · 2011-10-13 11:16:51Z

MySQLdb.cursor object probably do not support commit as given in MySQLDB manual. Connection objects, on the other hand, do.

Since you handle everything through Database class, I guess the commit code can go there.

Just to give a code for what Simon has said

    def connect(self):
        self.connection = MySQLdb.connect(...)  
        self.cursor = self.connection.cursor()

    def disconnect(self): ...
        self.cursor.close()
        self.connection.commit()
        self.connection.close()

    def commit(self):
        self.connection.commit()

    def getData(self): ....
       sqlcmd = """SELECT ...."""
       self.cursor.execute (sqlcmd % (params))
       result =  self.cursor.fetchall()
       return result

if __name__ == "__main__":
    db = prepaid_db.Database()
    db.connect()
    while 1:
        data = db.getData()
        ... do stuff
        db.commit()
    db.disconnect()

I am not sure but probably you can also do something like

db.connection.commit()

in while loop instead of calling the newly defined function

Collectives™ on Stack Overflow

Python MySQLdb empty select query although manual query execution delivers results

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related