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.

Answer 1

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