在Python中使用MySQLdb的长期陈旧结果 [英] Chronic stale results using MySQLdb in Python
问题描述
我的Python程序在MySQL数据库中查询一组表，休眠30秒，然后再次查询，依此类推.有问题的表是由第三方不断更新的，(显然)我希望看到每30秒就有一次新结果.
My Python program queries a set of tables in a MySQL DB, sleeps for 30 seconds, then queries them again, etc. The tables in question are continuously updated by a third-party, and (obviously) I would like to see the new results every 30 seconds.
假设我的查询如下:
"select * from A where A.key > %d" % maxValueOfKeyFromLastQuery
通常，即使表中存在新行 ，我的程序也会在一次或两次迭代后停止查找新结果.我知道表中存在新行，因为当我从交互式mysql(即不是来自Python)发出相同的查询时，可以看到它们.
Regularly I will see that my program stops finding new results after one or two iterations, even though new rows are present in the tables. I know new rows are present in the tables because I can see them when I issue the identical query from interactive mysql (i.e. not from Python).
我发现，如果在每次查询后终止与数据库的连接，然后为下一个查询建立新的连接，则Python中的问题就消失了.
I found that the problem goes away in Python if I terminate my connection to the database after each query and then establish a new one for the next query.
I thought maybe this could be a server-side caching issue as discussed here: Explicit disable MySQL query cache in some parts of program
但是:
-
当我检查交互式mysql shell时，它说缓存已打开. (因此，如果这是一个缓存问题，那么交互式shell为何不会受到它的困扰?)
When I check the interactive mysql shell, it says that caching is on. (So if this is a caching problem, how come the interactive shell doesn't suffer from it?)
如果我从Python程序中明确执行SET SESSION query_cache_type = OFF，问题仍然会发生.
If I explicitly execute SET SESSION query_cache_type = OFF from within my Python program, the problem still occurs.
为每个查询创建新的数据库连接是我解决问题的唯一方法.
Creating a new DB connection for each query is the only way I've been able to make the problem go away.
如何从Python获取查询以查看我知道的新结果?
How can I get my queries from Python to see the new results that I know are there?
推荐答案
本网站和此网站包含有关同一问题的信息.为了使表保持最新状态，您必须提交事务.使用db.commit()来执行此操作.
This website and this website contain information on the same problem. In order to keep your tables up to date, you must commit your transactions. Use db.commit() to do this.
如我下面的帖子所述，您可以通过启用自动提交来消除对此的需要.这可以通过运行db.autocommit(True)
As mentioned by the post below me, you can remove the need for this by enabling auto-commit. this can be done by running db.autocommit(True)
此外，在交互式外壳程序中启用了自动提交功能，因此这说明了您在那里没有问题的原因.
Also, auto-commit is enabled in the interactive shell, so this explains why you didn't have the problem there.
这篇关于在Python中使用MySQLdb的长期陈旧结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
