Python的MySqlDB无法更新行 [英] Python's MySqlDB not getting updated row

问题描述

我有一个脚本，等待直到数据库中的某些行被更新:

I have a script that waits until some row in a db is updated:

con = MySQLdb.connect(server, user, pwd, db)

脚本开始时，该行的值为"running"，并等待该值变为"finished"

When the script starts the row's value is "running", and it waits for the value to become "finished"

while(True):
    sql = '''select value from table where some_condition'''
    cur = self.getCursor()
    cur.execute(sql)
    r = cur.fetchone()
    cur.close()
    res = r['value']
    if res == 'finished':
        break
    print res
    time.sleep(5)

当我运行此脚本时，它将永远挂起.即使我在查询表时看到该行的值已更改为"finished"，该脚本的打印输出仍为"running".

When I run this script it hangs forever. Even though I see the value of the row has changed to "finished" when I query the table, the printout of the script is still "running".

有没有我未设置的设置吗?

Is there some setting I didn't set?

python脚本仅查询表.对表的更新是由tomcat Web应用程序使用JDBC进行的，该应用程序是在自动提交时设置的.

The python script only queries the table. The update to the table is carried out by a tomcat webapp, using JDBC, that is set on autocommit.

推荐答案

这是一个InnoDB表，对吗? InnoDB是事务存储引擎.将autocommit设置为true可能会为您解决此问题.

This is an InnoDB table, right? InnoDB is transactional storage engine. Setting autocommit to true will probably fix this behavior for you.

conn.autocommit(True)

或者，您可以更改事务隔离级别.您可以在此处了解更多信息: http://dev.mysql.com/doc/refman/5.0 /en/set-transaction.html

Alternatively, you could change the transaction isolation level. You can read more about this here: http://dev.mysql.com/doc/refman/5.0/en/set-transaction.html

此行为的原因是在单个事务内读取必须保持一致.同一事务中的所有一致读取均读取由第一次读取建立的快照.即使您的脚本仅读取表，这也被视为事务.这是InnoDB中的默认行为，您需要对其进行更改或在每次读取后运行conn.commit().

The reason for this behavior is that inside a single transaction the reads need to be consistent. All consistent reads within the same transaction read the snapshot established by the first read. Even if you script only reads the table this is considered a transaction too. This is the default behavior in InnoDB and you need to change that or run conn.commit() after each read.

此页面对此进行了更详细的说明: http: //dev.mysql.com/doc/refman/5.0/en/innodb-consistent-read.html

This page explains this in more details: http://dev.mysql.com/doc/refman/5.0/en/innodb-consistent-read.html

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