I'm experiencing strange behavior using MySQLdb.
If I turn off autocommit for my connection, I get stale data when I perform
multiple and identical SELECT's. If autocommit is enabled, I follow my SELECT
statement with a COMMIT statement or I create a new connection I don't get this
behavior.
Example:
TABLE EMPLOYEES (InnoDB)
|| ID | NAME ||
|| 1 | bob ||
import MySQLdbdb = MySQLdb.connect(host=host, user=user, passwd=passwd,
db=schema)cursor = db.cursor()cursor.execute("SELECT @@autocommit")autocommit =
cursor.fetchall()cursor.execute("SELECT name FROM EMPLOYEES WHERE
id=1")firstResult = print cursor.fetchall()cursor.close()# I now go and change
'bob' to 'dave' using some other MySQL client. I also confirm that the data was
changed with that client.cursor = db.cursor()
cursor.execute("SELECT name FROM EMPLOYEES WHERE id=1")secondResult =
cursor.fetchall()
cursor.close()# I append a COMMIT statement to the SELECT
cursor = db.cursor()
cursor.execute("SELECT name FROM EMPLOYEES WHERE id=1; COMMIT;")thirdResult =
cursor.fetchall()
cursor.close()
print autocommit
print firstResult
print secondResult
print thirdResult>>((0),)>>((bob),)>>((bob),)>>((dave),)
It doesn't make sense to me that a COMMIT should affect the behavior of a
SELECT statement at all. I tried replicating this using the MySQL Query browser
but that gives me the expected result (ie: I see the change). I know that the
second SELECT was successful because MySQL increments its SELECT counter.
I realize the easy fix is to just add a COMMIT to all my SELECT statements but
I'm trying to understand why it's doing this.
Python v2.4.3 (WinXP 64-bit)
MySQLdb v 1.2.2 (also tried v1.2.1)
MySQL 4.1.12 and 5.0.45 (Linux)
thanks in advance!
Jesse
[EMAIL PROTECTED]
_________________________________________________________________
Send a smile, make someone laugh, have some fun! Start now!
http://www.freemessengeremoticons.ca/?icid=EMENCA122--
http://mail.python.org/mailman/listinfo/python-list
