游标从mysq获取错误记录

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'},)
    >>> 

第一次，我跑光标.执行以及游标.fetchall，我得到了正确的结果。在

在第二次运行execute和fetchall之前

我在mysql中插入id为36的数据，并在mysql中运行commit命令

但是cursor.execute/fetchall在没有新数据的情况下，不能只获取数据

Answer 1

我猜你在用InnoDB。这是InnoDB事务的默认值。在

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”.

我还没有测试过，但是通过在当前连接上发出commit()来强制MySQLdb启动一个新事务，或者创建一个新的连接，可能会解决这个问题。在

Answer 2

我试了一下，结果就出来了

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