CODEWITHSUNDEEP
pythonmysqlmysql-python
ashim
ashim

Reputation: 25590

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

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.

Upvotes: 10

Views: 27314

Answers (3)

Siddharth Gupta
Siddharth Gupta

Reputation: 1613

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.

Upvotes: 3

selvakumar
selvakumar

Reputation: 652

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

Upvotes: 0

ashim
ashim

Reputation: 25590

Okay, db.autocommit(True) solved my problem.

Upvotes: 7

Related Questions