Search code examples
pymysql

Why python script loop is not fetching updated data from DB?

I've two Python script. Both of them uses PYMYSQL to read / write data on a table of MySQL DB.

Script 1: It runs in a infinite while loop and fetches data from outside API in every second and updates the same to the table of MySQL DB via UPDATE SQL Query. It's working completely fine. I can see on DB level that data is getting updated every second.

Script 2: It runs in a infinite while loop and fetches data from above mentioned table of MySQL DB and prints it via SELECT SQL Query. While I run it first time, it fetches correct / updated / latest data from DB, but after that when it iterates on the while loop, it fetches the same data which it fetched at the first time of starting and does not fetch the updated data. But it should fetch the updated data as Script 1 is continuously updating the data on DB every second. But if I stop the script and restarts then it shows the updated data.

I just want to know the reason or fix for same. It seems that when DB connection is getting restarted for Script 2 when I restart the script, it fetches the latest data.

Kindly help on same.

Solution

  • I have found the fix. Any of below can be applied.

    1. Adding autocommit=True into the connect() parameters.
    2. Calling conn.commit() after the cursor.execute() command.

    It's repeatable read as mentioned here: https://dev.mysql.com/doc/refman/8.0/en/glossary.html#glos_consistent_read