Mysql doesn't seem to recognize the WHERE statement in Python

Question

While creating a new program to handle a database I encountered a problem.

The mysql cursor doesn't seem to notice the WHERE statement in the following function:

def get_new_entries(self, delay):
start_time = t.time()
while True:
 cursor = self.cnx.cursor()
 check_delay = delay * 2
 current_time = datetime.datetime.now() - datetime.timedelta(seconds=check_delay)
 current_time = current_time.strftime("%Y-%m-%d %H:%M:%S")
 data = current_time
 print(current_time)
 query = """SELECT FILE_NAME, CREATION_TIME FROM `added_files` WHERE CREATION_TIME < %s"""
 cursor.execute(query, (data,))
 print(query)
 for (FILE_NAME, CREATION_TIME) in cursor:
 print(FILE_NAME)
 print(CREATION_TIME)
 cursor.close()
 cursor = None
 print("###################")
 t.sleep(delay - ((t.time() - start_time) % delay))

With this function I wish to achieve that every minute the function checks for new entries in the past minute. In the end I want it to send the filenames as a list to another class, so that class can use logic to handle the filenames.

However, the WHERE CREATION_TIME < '%s' doesnt seem to do anything. It either doesn't return any entry, while trying the same query in the mysql environment itself does what it should. If however the '<' is changed to '>' it suddenly returns all items, even those which should NOT be returned.

With that said, I have also used this part of code with only

cursor.execute(query)

while the query was changed to

query = "SELECT FILE_NAME, CREATION_TIME FROMadded_filesWHERE CREATION_TIME < {}".format(current_time).

This worked the first time, but the second time in the loop it didn't respond anything, even though I did add stuff to the database. I used the same datetime the program used in the mysql environment in the browser, which returned the correct results, but the program didn't.

So why doesn't it work? And what should I do to make it work?

• Justin: I can't seem to replicate your issue with the where statement returning nothing, the above code works for me. Any other information you can share that might help? Database details or connector version maybe?
• Superleggera: I found the solution. It had to do with autocommiting being set to false on default.

Answer 1

So after a while I solved the problem. It had nothing to do with the code I sent but had to do with the lack of autocommit = True of the MYSQL connection. I'll try and explain.

My application had to check a database that is automatically updated by another (C#) application. For every new file the C# app found it created a new entry in the database. Meanwhile this Python application checks for new filenames every delay (e.g. 60.0 seconds).

The Python application opens a connection via an mysql.connector and seems to keep hold on the state of the database at that exact moment. Anything added to it will not be found by any code you post, because it doesn't search in the actual database, it searches in it's own saved version of the database.

The fix would be to set the connection to autocommit. So you would do this:

self.cnx = mysql.connector.connect(user='root', password='', host='127.0.0.1', database='mydb')
self.cnx.autocommit = True

This will update the saved state of the database in the python app every time you execute an sql query.

So you don't get what you expect from you sql query in python, go try setting autocommit to true for your connection.