cursor.fetchone() returns None but row in the database exists

user_1082727 0
Viewed 23253

I am writing a program on python which interacts with MySQL database. For sql queries I use MySQLdb. The problem is that fetchone() returns None but with the database browser I can see that that row exists. This piece of code:

query = "SELECT * FROM revision WHERE rev_id=%s;" 
cursor.execute(query % revision_id)
row = cursor.fetchone()
if row == None:
    raise Exception("there isn't revision with id %s" % revision_id)

I have no idea what is going on here. Any ideas?

EDIT: okay, in some cases it works in some cases it doesn't but anyway when it does not work the row exists in the table. I am passing a cursor object to a function and the code above is in the function. The problem is connected with this cursor object. Could the problem be that I pass the cursor as an argument to the function? How can I test it?

EDIT2: yes, the problem is that cursor does not work after I use it several times. Wether because other program connects to the DB or I am doing something wrong. I have while loop in which I call a function to get info from the DB. After some iterations it does not work again. There is another program which writes to the DB while while loop works.

mysql-python mysql python
3 Answers

This is related to transaction isolation level on your MySQL server. In the case of REPEATABLE_READ which is the default level for InnoDb, a snapshot is created at the time of first read, and subsequent read by the same cursor are made from this snapshot. Read more about isolation levels here

What we usually require while reusing the same cursor to run multiple queries, is READ_COMMITTED. Thankfully, if you can not change this on your SQL server, you can set your cursor to a particular isolation level.

cur = conn.cursor()
cur.execute("SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED")

This makes sure that every query you make, there is a fresh latest committed snapshot is used.

user_26481660

Best Practice is to commit db, after all query executed db.commit()

user_84433570
Related