SELECT in a while loop in python with mysql

Question

I am trying to find the latest entry in a MySQL database by using a query with SELECT MAX(id). I already get the latest id, so I know the query works, but now I want to use it in a while loop so I keep getting the latest entry with each iteration.

This is what I have so far:

import pymysql

con = pymysql.connect(host='.....', user='.....',
                      password='.....', database='.....')

cur = con.cursor()

while True:
    query = "SELECT MAX(id) FROM reports"
    cur.execute(query)
    data = cur.fetchall()
    last = (data[0][0])
    print(last)

The problem is that I keep getting the same result after updating the database. For instance, right now I have 45 entries, so my script prints '45' in a while loop. But after I add another row to the table it keeps printing '45' instead of the '46' I would expect. When I stop the script and run it again, it will print '46' and keep printing this even after I add another row.

I have only started working with MySQL about two weeks ago, so I don't have all that much knowledge about it. I feel like I'm missing something really small here. What should I do? Any help would be greatly appreciated.

Try putting a pause in the loop. It's running so fast that it will do hundreds or thousands of queries before you update the database. You're seeing the output from all those old queries. — Barmar, Jan 31 '17 at 21:56
You have to create a new cursor to pull new data. Or as suggested by this answer http://stackoverflow.com/a/23683656/3901060, commit the query. — FamousJameous, Jan 31 '17 at 21:56
@FamousJameous That answer was what I was looking for. I didn't realise I had to commit SELECT statements as well. Thank you! — EerlijkeDame, Jan 31 '17 at 22:08

score 3 · Answer 1 · answered Apr 24 '19 at 14:51

3

I had this same problem, and just wanted to make it clear for anyone else searching for the solution.

Setting autocommit to True solved my issue and didn't require calling a commit after each query.

import pymysql

con = pymysql.connect(host='.....', user='.....',
                  password='.....', database='.....')
con.autocommit = True
cur = con.cursor()

while True:
    query = "SELECT MAX(id) FROM reports"
    cur.execute(query)
    data = cur.fetchall()
    last = (data[0][0])
    print(last)

Here is a link to the documentation

answered Apr 24 '19 at 14:51

Mark Hebert

919
1
9
15

yeah Thank You so much is it working autocommit not required after each query – Saquib Azam Dec 28 '20 at 19:44
1

Although it works, why would autocommit make any sense for a SELECT? Commit makes sense for updates, but what is the point here? – ACV Jun 09 '21 at 21:33
1

Ha. Found the answer to my question above: "When the autocommit is turned off, you must commit transactions when using transactional storage engines such as InnoDB or NDBCluster." https://dev.mysql.com/doc/connectors/en/connector-python-api-mysqlconnection-autocommit.html Seems strange.. – ACV Jun 09 '21 at 21:43

SELECT in a while loop in python with mysql

1 Answers1