[SOLVED] MySQLdb Python repeating the same query returns the same data

tadeas · 12-05-2011, 08:10 AM

Hello,
I have a quite serious problem with the MySQLdb library in Python (2.6 in Debian Stable as well as 2.7 in OpenSuse 12.1). An example will be probably the easiest way to show what I mean:

Code:

connection = MySQLdb.connect(...)
sql = 'SELECT * FROM file WHERE disk > 4000 and disk < 5000'
cursor = connection.cursor()
cursor.execute(sql)
data = cursor.fetchall()
...

Now change data in the server that satisfy the WHERE condition. We can test it with some other client like phpmyadmin or the CLI client. Now try to get the data in code again:

Code:

...
cursor = connection.cursor()
cursor.execute(sql)
data = cursor.fetchall()

cursor.fetchall() returns the same data as before the changes! It probably doesn't actually query the MySQL server, but returns some cached result instead?

Is this the intended behaviour? And how can I change it other than disconnect and connect again?
BTW does anyone know how to contact the MySQLdb author? I couldn't find any contact, either on PyPi as well as SF (where MySQLdb is hosted). Also, could anyone point me to the bug database?

Proud · 12-05-2011, 08:27 AM

http://stackoverflow.com/questions/5...qldb-in-python

Quote:

connection.autocommit(True)

tadeas · 12-05-2011, 09:57 AM

Quote:

Originally Posted by Proud

...

Thanks a million times! It works.

Strange though, that I didn't find this myself.
I suppose I should not wonder why MySQL needs to commit SELECT statements...

Thanks again!

vharishankar · 12-05-2011, 10:29 AM

While this works, in general don't use autocommit in code. Always commit in a bunch after confirming that the queries executed are correct. Auto-commit inserting and deleting makes destructive changes. So avoid auto-committing and keep the option for a rollback after proper error-checking.