user1676300 user1676300 - 17 days ago 5
MySQL Question

Python loop querying MySQL - query results not updated?

I've got a small bit of python code that queries mySQL,

while True:
print "running SQL query"
cursor.execute('select * from sites where stage="NEW"')
results = cursor.fetchall() #Fetch results
for row in results:
print "change detected" #simplified line for testing
print "sleeping"
sleep(10)


When no records match output is :

running SQL query
sleeping
running SQL query
sleeping


Manually create SQL record that will match (allow time for loop to repeat), output is still :

running SQL query
sleeping
running SQL query
sleeping


Stop script and restart, test output is as expected :

running SQL query
change detected
sleeping


So the results are being cached? or the query not being re-run?

How would I deal with this, bearing in mind the query is run quite frequently (I did wonder about closing the DB connection and re-opening everytime, but this seems excessive?)

Answer

If you look the Documentation for commit() it can give a hint on why your code doesn't work.

Try using database.commit() after sleep(10) and see if it works.