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.

Answer 1

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.

Answer 2

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