How to properly handle empty data set with python mysql connector?

Question

I am using the python mysql connector in a little script. The problem I'm facing is: when executing a select statement that returns 0 rows, I'm unable to close the cursor. When closing the cursor, "mysql.connector.errors.InternalError: Unread result found" is triggered. However, calling fetchall() results in an "mysql.connector.errors.InterfaceError: No result set to fetch from." error.

So basically, I'm unable to close the cursor because of some unread data and I'm unable to read any data because there is no data to read.

Then it complains the next time I use the connection to open a new cursor. — Trinopoty
– Trinopoty, Commented Dec 12, 2016 at 12:53

Maurice Meyer · Accepted Answer · 2016-12-12 12:56:17Z

1

What about:

if cur.rowcount:
    cur.fetchall()
else:
    #whatever you want to do

answered Dec 12, 2016 at 12:56

Maurice Meyer

18.2k4 gold badges35 silver badges54 bronze badges

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

1 Comment

Trinopoty Over a year ago

That doesn't help with cleanly closing the cursor.

Trinopoty · Accepted Answer · 2017-11-02 14:47:25Z

1

It's been a while to this question but I'll post the solution I found anyway.

Using the official mysql connector for python, the rows are not fetched from the server until requested. As a result, if there are remaining rows, trying to close the connection throws an exception. One option is to use buffered cursor. A buffered cursor reads all rows from the server and the cursor can be closed at any point. However, this method comes with a memory cost.

There is a hard limit of one open cursor per connection for the mysql connector. Trying to open another cursor raised an exception.

answered Nov 2, 2017 at 14:47

Trinopoty

4234 silver badges16 bronze badges

Comments

Roy Holzem · Accepted Answer · 2016-12-12 15:59:55Z

0

I use this logic for dealing with empty SQL's:

import MySQLdb
cursor = db.cursor()
sql = "SELECT * FROM foobar"
dta = cursor.execute(sql)
if dta == 0:
    print "0 MySQL Entries found"
    cursor.close()

assuming I understood your question correctly, you can't close the cursor for w/e reason. You can "re-position" the cursor without closing it by simply re-assigning the cursor:

cursor = db.cursor()
doStuff()

answered Dec 12, 2016 at 15:59

Roy Holzem

87014 silver badges25 bronze badges

1 Comment

Trinopoty Over a year ago

I'm not using "MySQLdb" but the official mysql connector. And, I cannot create a 2nd cursor without closing the current one because that just throws the same error.

Collectives™ on Stack Overflow

How to properly handle empty data set with python mysql connector?

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related