Python MySQL不刷新 [英] Python MySQL not refreshing

问题描述

我有两个程序:一个填充和更新数据库，另一个每 10 秒从数据库中选择一次信息.

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

我使用 Pymysql.

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!

除了 SELECT 之外，我是否需要进行特殊查询?我需要在所有查询之前关闭连接并重新打开它吗?

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

我在启动程序时创建了 GetData 类，并且每 10 秒调用一次 get_data.

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

填充数据库的程序:

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

推荐答案

为什么你没有看到更新:

该行为的原因是 InnoDB 的默认隔离级别 可重复阅读.使用 REPEATABLE READ，第一个非锁定 SELECT 会建立一个快照，表示该时间点的数据.所有连续的非锁定 SELECT 都从同一个快照中读取.其他事务对数据库的更新不会反映在该快照中，因此保持透明.

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.

提交事务(或关闭事务并创建一个新事务)将导致在下一个查询中创建一个新快照，表示在那个时间点数据库中的数据.这就是 MySQL 实现 Consistent Nonlocking Reads 的方式作为他们 ACID 合规策略的一部分.

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.

为什么 with self.conn 有效以及它的作用:

Why with self.conn works and what it does:

在 PyMySQL 中，有两个(相关的)上下文管理器实现，一个在 光标(或多或少'记录')和一个 连接(可以在代码中找到:D).

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

当您使用 和 self.conn.cursor() 作为光标时: 是光标的实现生效.进入上下文返回self(从self.conn上的cursor()方法返回的游标对象)；离开上下文最终关闭了该游标.对交易没有影响.

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.

当使用 self.conn 作为光标 时，有效的是连接的实现.进入上下文从调用 self.cursor() 中返回光标；离开上下文会在事务上执行 commit 或 rollback.游标也隐式关闭.

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.

因此，在离开连接实现的上下文时对 self.commit 的隐式调用会使事务中的现有快照过期"并强制在下一次迭代中创建一个新快照循环，它可能包含您的插入，只要它们的提交在创建所述新快照之前完成.

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.

这篇关于Python MySQL不刷新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！