i'm running a SELECT query in a loop.
Once in a while, the database table is updated (by another program).
The first SELECT retrieves the correct data, but further calls in the loop return the first values.
How can I retrieve up-to-date data?
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()
i had to add
connection.autocommit(True)
Adding SQL_NO_CACHE had no effect on the presented case, apparently because there was no caching involved.
I still don't understand why a SELECT needs COMMIT.
I'll open a new question about it.