Why Do Subsequent Queries in a Python Loop Return the Same Data from a MySQL Database?

Retrieving Dynamic Data from MySQL Using Python Queries

When periodically querying a rapidly changing MySQL database from Python, you might expect a loop-based approach to consistently fetch the latest data. However, you may encounter a situation where the same data is repeatedly returned.

This issue stems from MySQL's default isolation level, REPEATABLE READ. With this level, reads within a transaction use a snapshot of the data as it existed at the start of the transaction. Subsequent queries within the same transaction will read from this snapshot rather than updating it.

To resolve this, you need to commit the connection after each query, ensuring that the next transaction will retrieve the latest data. Here's how you can modify your Python code:

<code class="python"># Main loop
while True:

    # SQL query
    sql = "SELECT * FROM table"

    # Read the database, store as a dictionary
    mycursor = mydb.cursor(dictionary=True)
    mycursor.execute(sql)

    # Store data in rows
    myresult = mycursor.fetchall()

    # Transfer data into list
    for row in myresult:
        myList[int(row["rowID"])] = (row["a"], row["b"], row["c"])

        print(myList[int(row["rowID"])])

    # Commit !
    mydb.commit()
    print("---")
    sleep (0.1)</code>

By adding mydb.commit() after each query, you force MySQL to commit the transaction and update the snapshot. This allows subsequent queries to access the freshest data.

The above is the detailed content of Why Do Subsequent Queries in a Python Loop Return the Same Data from a MySQL Database?. For more information, please follow other related articles on the PHP Chinese website!