cursor fetch wrong records from mysql

Question

    >>> _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

How did you do the second insert? From Python or the MySQL shell? — Burhan Khalid
– Burhan Khalid, Commented Mar 19, 2012 at 4:31

Kien Truong · Accepted Answer · 2012-03-19 05:02:57Z

2

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.

answered Mar 19, 2012 at 5:02

Kien Truong

11.4k2 gold badges34 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

stutiredboy Over a year ago

thanks. You are right. after fetchall, run commit is ok. or SET TRANSACTION ISOLATION LEVEL READ COMMITTED is ok too.

NPatel · Accepted Answer · 2012-03-19 05:30:49Z

0

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()

answered Mar 19, 2012 at 5:30

NPatel

21.4k17 gold badges104 silver badges166 bronze badges

Collectives™ on Stack Overflow

cursor fetch wrong records from mysql

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related