Python MySQL connector - unread result found when using fetchone

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.

If you want to get only one result from a request, and want after to reuse the same connexion for other requests, limit your sql select request to 1 using "limit 1" at the end of your request.

ex "Select field from table where x=1 limit 1;"

This method is faster using "buffered=True"

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

Set the consume_results argument on the connect() method to True.

cnx = mysql.connector.connect(
    host="localhost",
    user="user",
    password="password",
    database="database",
    consume_results=True
)

Now instead of throwing an exception, it basically does fetchall().
Unfortunately this still makes it slow, if you have a lot of unread rows.

There is also a possibility that your connection to MySQL Workbench is disconnected. Establish the connection again. This solved the problem for me.

cursor.reset() and then create tables and load entries

I had the same issue. My goal was to run a SELECT on a large number of rows, fetch them one by one, and do an INSERT in another table, for each row returned by the SELECT.

The only solution I found was to open 2 connections (so 2 cursors).

Hope it helps.

Would setting the cursor within the for loop, executing it, and then closing it again in the loop help? Like:

for steps in result['routes'][0]['legs'][0]['steps']:
    cursor = cnx.cursor()
    ....
    leg_no = cursor.fetchone()[0]
    cursor.close()
    print(leg_no)