Queries on MySQL from Python Yielding Identical Data

Repeatedly querying a MySQL database from Python to retrieve dynamic data poses a challenge. It is known that merely iterating through a query in a loop does not suffice to fetch fresh data from the database.

Your provided code exemplifies this issue:

for i in range(listSize):
    #...
    mycursor = mydb.cursor(dictionary=True)
    mycursor.execute(sql)
    #...

Even with attempts using fetchall, fetchmany, and fetchone, the results remain stagnant.

The Solution: Committing the Connection

To resolve this, each query execution must be followed by a commit of the connection. This action concludes the ongoing transaction and initiates a new one, enabling the subsequent query to detect alterations made during the previous transaction.

while True:
    #...
    mycursor = mydb.cursor(dictionary=True)
    mycursor.execute(sql)
    #...
    mydb.commit()
    #...

Isolation Levels and the REPEATABLE READ Default

This concept is rooted in isolation levels. By default, MySQL employs REPEATABLE READ for InnoDB. This implies that within a transaction, subsequent reads will retain the snapshot established by the transaction's initial read. Hence, without committing, subsequent queries will not capture any alterations.