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:

# 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)

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.