Python MySQLdb is an empty select query, although doing a manual query provides results

Question

I wrote a python daemon that continuously examines the mysql database. It works great when I constantly connect and connect 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 open a database connection (as shown below), I get an empty request, although although it works, I can request db manually, give it the same request and get the expected result.

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

I tried everything to understand why he would do this:

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

I would like to know what I do not understand.

Answer 1

You are probably querying the InnoDB table where another process is inserting new data during this time. If so, the MySQL server automatically starts a new transaction for your connection, and since you are not calling dbConnection.commit() or .rollback() anywhere, you are permanently stuck in this transaction. InnoDB's default settings ensure that whenever you request data, you will always see the same result in a single transaction. So any other process inserts into the table, hidden from your connection to the daemon.

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

Answer 2

The MySQLdb.cursor object probably does not support the commit specified in the MySQLDB manual . Connection objects, on the other hand, do.

Since you are processing everything through a database class, I assume that commit code can go there.

Just to give a code for what Simon 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'm not sure, but you can probably do something like

 db.connection.commit() 

in a while loop instead of calling a new function