Menu
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.
929
Steps for using fetchone() in Mysql using Python: Next, create a cursor object with the cursor() method. Now create and execute the query using “SELECT *” statement with execute() method to retrieve the data. Use fetchone() method on the result variable. print the result.
It is an object that is used to make the connection for executing SQL queries. It acts as middleware between SQLite database connection and SQL query. It is created after giving connection to SQLite database.
Just an empty list ( [] ) for cursor. fetchall() and None for cursor. fetchone() . For any other statement, e.g. INSERT or UPDATE , that doesn't return a recordset, you can neither call fetchall() nor fetchone() on the cursor.
fetchall() fetches all the rows of a query result. An empty list is returned if there is no record to fetch the cursor. fetchone() method returns one row or a single record at a time. It will return None if no more rows / records are available.
Okay, db.autocommit(True) solved my problem.
63
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.
38
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With