Python MySQL connector – unread result found when using fetchone

Python MySQL connector – unread result found when using fetchone

All that was required was for buffered to be set to true!

cursor = cnx.cursor(buffered=True)

The reason is that without a buffered cursor, the results are lazily loaded, meaning that fetchone actually only fetches one row from the full result set of the query. When you will use the same cursor again, it will complain that you still have n-1 results (where n is the result set amount) waiting to be fetched. However, when you use a buffered cursor the connector fetches ALL rows behind the scenes and you just take one from the connector so the mysql db wont complain.

I was able to recreate your issue. MySQL Connector/Python apparently doesnt like it if you retrieve multiple rows and dont fetch them all before closing the cursor or using it to retrieve some other stuff. For example

import mysql.connector
cnxn = mysql.connector.connect(
    host=127.0.0.1,
        user=root,
        password=whatever,
        database=mydb)
crsr = cnxn.cursor()
crsr.execute(DROP TABLE IF EXISTS pytest)
crsr.execute(
CREATE TABLE pytest (
    id INT(11) NOT NULL AUTO_INCREMENT,
    firstname VARCHAR(20),
    PRIMARY KEY (id)
    )
)
crsr.execute(INSERT INTO pytest (firstname) VALUES (Gord))
crsr.execute(INSERT INTO pytest (firstname) VALUES (Anne))
cnxn.commit()
crsr.execute(SELECT firstname FROM pytest)
fname = crsr.fetchone()[0]
print(fname)
crsr.execute(SELECT firstname FROM pytest)  # InternalError: Unread result found.

If you only expect (or care about) one row then you can put a LIMIT on your query

crsr.execute(SELECT firstname FROM pytest LIMIT 0, 1)
fname = crsr.fetchone()[0]
print(fname)
crsr.execute(SELECT firstname FROM pytest)  # OK now

or you can use fetchall() to get rid of any unread results after you have finished working with the rows you retrieved.

crsr.execute(SELECT firstname FROM pytest)
fname = crsr.fetchone()[0]
print(fname)
try:
    crsr.fetchall()  # fetch (and discard) remaining rows
except mysql.connector.errors.InterfaceError as ie:
    if ie.msg == No result set to fetch from.:
        # no problem, we were just at the end of the result set
        pass
    else:
        raise
crsr.execute(SELECT firstname FROM pytest)  # OK now

Python MySQL connector – unread result found when using fetchone

cursor.reset() is really what you want….

fetch_all() is not good because you may end up moving unnecessary data from the database to your client