Python Mysql Connector无法获取新内容 [英] Python Mysql Connector not getting new content

问题描述

我制作了一个简单的python脚本，该脚本每x秒检查一次mysql表并将结果打印到控制台.我使用MySQL连接器驱动程序.

但是，运行脚本仅显示初始值.意思是说，如果我在脚本运行时更改数据库中的值，则脚本不会注册它，并且会继续写入初始值.

在while循环中检索值的代码如下:

def get_fans():
    global cnx
    query = 'SELECT * FROM config'
    while True:
        cursor = cnx.cursor()
        cursor.execute(query)
        for (config_name, config_value) in cursor:
            print config_name, config_value
        print "\n"
        cursor.close()
        time.sleep(3)

为什么会这样?

解决方案

最有可能是自动提交的问题. MySQL连接器驱动程序文档状态 ，表明它已关闭自动提交功能.确保在更改表时提交隐式事务.同样是因为默认隔离是 REPEATABLE READ 您有

同一事务中的所有一致读取均读取快照 由一读建立.

因此，我想您甚至必须为轮询脚本管理事务.或将隔离级别更改为READ COMMITTED.

不过，更好的方法是恢复到MySQL客户端默认的自动提交模式.即使 PEP 249 指南最初将其禁用，但这只是一个建议，最有可能是严重的设计错误.这不仅使新手对未提交的更改感到好奇，甚至使您的只读工作负载变慢，它使数据层设计复杂化，并打破了 explicit比隐式 Python zen更好的方法.甚至像Django这样的呆滞东西也要重新考虑./p>

I making a simple python script which checks a mysql table every x seconds and print the result to the console. I use the MySQL Connector Driver.

However, running the script only prints the initalial values. By that I mean, that if I change the values in the database while my script is running, it's not registered by the script and it's keeps on writing the initial values.

The code which retrieves the values in a while loop is as follows:

def get_fans():
    global cnx
    query = 'SELECT * FROM config'
    while True:
        cursor = cnx.cursor()
        cursor.execute(query)
        for (config_name, config_value) in cursor:
            print config_name, config_value
        print "\n"
        cursor.close()
        time.sleep(3)

Why is this happening?

解决方案

Most likely, it's an autocommit issue. MySQL Connector Driver documentation states, that it has autocommit turned off. Make sure you commit your implicit transactions while changing your table. Also because default isolation is REPEATABLE READ you have:

All consistent reads within the same transaction read the snapshot established by the first read.

So I guess you have to manage transaction even for your polling script. Or change isolation level to READ COMMITTED.

Though, the better way is to restore to MySQL client default autocommit-on mode. Even though PEP 249 guides to have it initially disabled, it's mere a proposal and most likely a serious design mistake. Not only it makes novices wonder about uncommited changes, makes even your read-only workload slower, it complicates data-layer design and breaks explicit is better than implicit Python zen. Even sluggish things like Django have it rethought.

这篇关于Python Mysql Connector无法获取新内容的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！