Python MySQL connector - unread result found when using fetchone

The problem is about the buffer, maybe you disconnected from the previous MySQL connection and now it cannot perform the next statement. There are two ways to give the buffer to the cursor. First, only to the particular cursor using the following command:

import mysql.connector

cnx = mysql.connector.connect()

# Only this particular cursor will buffer results
cursor = cnx.cursor(buffered=True)

Alternatively, you could enable buffer for any cursor you use:

import mysql.connector

# All cursors created from cnx2 will be buffered by default
cnx2 = mysql.connector.connect(buffered=True)

cursor = cnx.cursor()

In case you disconnected from MySQL, the latter works for you. Enjoy coding

I was able to recreate your issue. MySQL Connector/Python apparently doesn't like it if you retrieve multiple rows and don't 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

cursor.reset() is really what you want.

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

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 won't complain.