MySQLdb is caching SELECT results?
Question: 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()
    

  * OS: ubuntu,
  * python: 2.7.4
  * mysqldb: 1.2.3
  * mysql: 5.5.34


Answer: 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.

