MySQLdb正在缓存SELECT结果吗? [英] MySQLdb is caching SELECT results?

问题描述

我正在循环运行SELECT查询.

i'm running a SELECT query in a loop.

偶尔，数据库表会被另一个程序更新.

Once in a while, the database table is updated (by another program).

第一个SELECT检索正确的数据，但是循环中的其他调用返回第一个值.

The first SELECT retrieves the correct data, but further calls in the loop return the first values.

如何检索最新数据?

到目前为止，我发现的唯一解决方法是每次迭代都重新连接到数据库！在我的示例中，取消注释两个注释#1#和#2#.仅取消注释#2#是不够的(即，重新创建了游标)，结果仍被缓存.

The only workaround I found so far, is reconnect to the DB on each iteration! In my example, uncommenting BOTH comment #1# and #2#. Uncommenting only #2# is not enough (i.e., cursor is recreated), the results are still cached.

这是一个给出错误的工作示例.

Here's a working sample that gives the error.

import MySQLdb
from time import sleep

class DB:
    def __init__(self):
        self.connection = MySQLdb.connect(mysql_host, mysql_user, mysql_pass, mysql_db)
        self.cursor = self.connection.cursor()

    def get(self):
            sql = ''' SELECT id, message FROM mps_messages
                      WHERE topic=%s ORDER BY id LIMIT 1  '''
            #1# self.connect()
            #2# self.cursor = self.connection.cursor()
            self.cursor.execute(sql, ("topic",) )
            rec = self.cursor.fetchone()
            print rec

    def loop(self):
        while True:
            self.get()
            sleep(4)

db=DB()
db.loop()

• 操作系统:ubuntu，
• python:2.7.4
• mysqldb:1.2.3
• mysql:5.5.34
• OS: ubuntu,
• python: 2.7.4
• mysqldb: 1.2.3
• mysql: 5.5.34

推荐答案

我必须添加

connection.autocommit(True)

添加SQL_NO_CACHE对当前情况没有影响，显然是因为不涉及缓存.

Adding SQL_NO_CACHE had no effect on the presented case, apparently because there was no caching involved.

我仍然不明白为什么SELECT需要COMMIT.

I still don't understand why a SELECT needs COMMIT.

我将对此提出一个新问题.

I'll open a new question about it.

这篇关于MySQLdb正在缓存SELECT结果吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！