在 python 中获取更新的 MySQL 表条目而不关闭连接 [英] Get updated MySQL table entries in python without closing connection

问题描述

我有 2 个 python 程序在运行.Script1 定期将条目写入一个表，Script2 从同一个 MySQL 表中读取.两者同时运行.Script2 必须获取表的最后一个(最新添加的)条目.

I have 2 python programs running. Script1 writes entries to a table periodically, and Script2 reads from the same MySQL table. Both are running simultaneously. Script2 must get the last (latest added) entry of the table.

现在的问题是，Script1 正在完美地向表中添加条目，但 Script2 每次都无法读取最新的条目.只有在我阅读完关闭连接时才读取最新的条目，当我想再次阅读时重新打开它.

Now, the problem is that Script1 is adding entries to the table perfectly, but Script2 is unable to read the latest entry each time. It reads the latest entry only when I close the connection after reading, and re-open it when I want to read again.

这是唯一可以采取的方法吗?有没有办法在不需要每次关闭和打开连接的情况下获取更新的值?访问不断更新的数据库时，程序员遵循的最佳实践是什么?

更详细:

下面的代码工作正常，但无法显示更新的值.它第一次成功显示了最后一个条目，但接下来的几次 readComm() 被调用，尽管表已被更新，但再次显示相同的条目.

The code below works fine, but is unable to display updated values. It shows the last entry successfully the first time, but the next few times readComm() is called, the same entry is shown again, despite the table having being updated.

import MySQLdb
import time

db = MySQLdb.connect("localhost", "root", "abc", "abc")
cursor=db.cursor()

def readComm():
    sql = "SELECT * FROM my_table ORDER BY id DESC LIMIT 1;"
    try:
        cursor.execute(sql)
        # Fetch all the rows in a list of lists.
        results = cursor.fetchall()
        print '~~~~', results
    except:
        print "Error! Unable to fetch data" 
    return
for i in range(5):
    readComm()
    time.sleep(10)

如果我修改它，代码会显示更新的值，以便在我每次进入和退出 readComm() 时分别打开和关闭数据库.

The code shows updated values if I modify it so that it opens and closes DB each time I enter and exit readComm() respectively.

推荐答案

这只是因为事务内部的读隔离.在每次循环后执行 db.commit().

This is just because of read isolation inside a transaction. Do db.commit() after every loop.

这篇关于在 python 中获取更新的 MySQL 表条目而不关闭连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！