CODEWITHSUNDEEP
pythonmysql-python
stutiredboy
stutiredboy

Reputation: 357

cursor fetch wrong records from mysql


    >>> _cursor.execute("select * from bitter.test where id > 34")
    1L
    >>> _cursor.fetchall()
    ({'priority': 1L, 'default': 0, 'id': 35L, 'name': 'chinanet'},)
    >>> _cursor.execute("select * from bitter.test where id > 34")
    1L
    >>> _cursor.fetchall()
    ({'priority': 1L, 'default': 0, 'id': 35L, 'name': 'chinanet'},)
    >>>

the first time, i run cursor.execute and cursor.fetchall, i got the right result.

before the second time i run execute and fetchall

i insert data into mysql which id id 36, i also run commit command in mysql

but cursor.execute/fetchall counld only get the data before without new data

Upvotes: 2

Views: 730

Answers (2)

Neel
Neel

Reputation: 21317

I tried this and got the result

import MySQLdb


conn = MySQLdb.connect('localhost', 'test', 'test', db='test')

cur = conn.cursor()

result = cur.execute("select * from users where id > 7")

print "RESULT :", result
print "DATA :", cur.fetchall()


cur.execute("insert into users(day) values('2012-03-15')")
conn.commit()

result = cur.execute("select * from users where id > 7")

print "RESULT :", result
print "DATA :", cur.fetchall()

Upvotes: 0

Kien Truong
Kien Truong

Reputation: 11381

I guess you're using InnoDB. This is default for an InnoDB transaction.

REPEATABLE READ

This is the default isolation level for InnoDB. For consistent reads, there is an important difference from the READ COMMITTED isolation level: All consistent reads within the same transaction read the snapshot established by the first read. This convention means that if you issue several plain (nonlocking) SELECT statements within the same transaction, these SELECT statements are consistent also with respect to each other. See Section 13.2.8.2, “Consistent Nonlocking Reads”.

I haven't tested yet but forcing MySQLdb to start a new transaction by issuing a commit() on the current connection or create a new connection might solve the issue.

Upvotes: 2

Related Questions