Why Does My Python MySQL Query Keep Returning the Same Data?

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:

<code class="python">for i in range(listSize):
    #...
    mycursor = mydb.cursor(dictionary=True)
    mycursor.execute(sql)
    #...</code>

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.

<code class="python">while True:
    #...
    mycursor = mydb.cursor(dictionary=True)
    mycursor.execute(sql)
    #...
    mydb.commit()
    #...</code>

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.

The above is the detailed content of Why Does My Python MySQL Query Keep Returning the Same Data?. For more information, please follow other related articles on the PHP Chinese website!