Python MySQL not refreshing

Question

I have two programs: One that fill and updates a database and another that selects info from the database every 10 seconds.

I use Pymysql.

When I update the database I commit the data, I can see the results in the database with command lines, but the other program has the same output and doesn't get the new data!

Do I need to make a special query other than SELECT? Do I need to close the connection and reopen it before all query?

I create the GetData class when starting the program and get_data is called every 10 seconds.

class GetData:
    def __init__(self):
        self.conn = pymysql.connect(host='localhost', user='root', password='', db='mydb', charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)

    def get_data(self, data):
        with self.conn.cursor() as cursor:
            self.sql = "SELECT id_data, somedata FROM mytable WHERE (%s = 'example');"
            cursor.execute(self.sql, (data,))
        return cursor.fetchall()

    def close_conn(self):
        self.conn.close()

The program that fills the database:

class FillDb:
    def __init__(self):
        self.conn = pymysql.connect(host='localhost', user='root', password='', db='mydb', charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)
        #added this line but doesen't help!
        self.conn.autocommit(True)

    def add_in_db(self, data):
        with self.conn.cursor() as cursor:
            self.sql = "INSERT INTO mytable (somedata) VALUES (%s);"
            cursor.execute(self.sql, (data,))
            self.conn.commit()

Answer 1

Why you did not see the updates:

The cause of the behavior is InnoDB's default isolation level REPEATABLE READ. With REPEATABLE READ, the first nonlocking SELECT establishes a snapshot representing the data at that point in time. All consecutive nonlocking SELECTs read from that same snapshot. Updates to the DB from other transactions are not reflected in that snapshot, thus remaining transparent.

Committing the transaction (or closing it and creating a new one) will cause a new snapshot to be created with the next query, representing the data in the DB at that point in time. This is how MySQL implements Consistent Nonlocking Reads as part of their ACID compliance strategy.

Why with self.conn works and what it does:

In PyMySQL, there's two (relevant) contextmanager implementations, one on the Cursor (more or less 'documented') and one on the Connection (can be found in the code :D).

When you used with self.conn.cursor() as cursor: it was the cursor's implementation that was in effect. Entering the context returned self (the cursor object returned from the cursor() method on self.conn); leaving the context ultimately closed that cursor. It has no effect on the transaction.

When using with self.conn as cursor it is the connection's implementation that is in effect. Entering the context returns the cursor from calling self.cursor(); leaving the context does a commit or rollback on the transaction. The cursor is closed implicitly as well.

So, the implicit call to self.commit when leaving the context of the connection's implementation 'expires' the existing snapshot in your transaction and forces the creation of a new one in the next iteration of your loop, which potentially contains your inserts, as long as their commit has completed before the creation of said new snapshot.