Python loop querying MySQL - query results not updated?

Question

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?)

Carles Mitjans · Accepted Answer · 2016-11-23 14:14:32Z

1

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.

answered Nov 23, 2016 at 14:14

Carles Mitjans

4,8663 gold badges22 silver badges39 bronze badges

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

1 Comment

user1676300 Over a year ago

Thanks, this appears to work. I am still a little confused since my code doesn't write to the database (and I thought a commit was only to apply changes). I'm assuming for now, that even though this code is not making any changes, the commit is 'completing/closing' the current query such that when the query is repeated (in my code), I am looking at a fresh set of results and not the results of the previous query.

Collectives™ on Stack Overflow

Python loop querying MySQL - query results not updated?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related