Mysql.connector.errors.internalerror unread result found

cursor = cnx.cursor(buffered = True)

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

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()

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;"

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
)

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)

cursor.MySQLCursorBuffered Class , cursor.MySQLCursorBufferedDict Class , cursor.MySQLCursorBufferedRaw Class , The MySQLCursorBuffered class inherits from MySQLCursor.

Example:

import mysql.connector

cnx = mysql.connector.connect()

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

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

Python- not equal operator is not working

for ind in df.index:
   sel = "select * from  data where `date`='{0}'".format(df['date'][ind])
cursor2.execute(sel)
data = cursor2.fetchone()
if len(data) == 0:
   print("list")
else :
   print("Already exists")

cursor2 = db.cursor(buffered = True)

import mysql.connector
db = mysql.connector.connect()
crs = db.cursor(buffered = True)

1 day ago 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. ,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., 6 days ago Aug 13, 2021  · Python - mysql.connector.errors.InternalError: Unread result found - Array Overflow. ... pass parameter buffered= True. buffered= True says that it should read result every time fresh otherwise with fetchone() method , it will not read same data over and over again . … , 2 days ago Jul 27, 2020  · In line 1, we call fetchone() to read first row from the result set. Next, we call fetchmany() to read the next 2 rows and finally we call fetchall() to fetch the remaining row. The result set is empty now, so the next call to fetchmany() returns an empty list.. Buffered and Unbuffered Cursor #. By default, the cursor object is unbuffered.

for steps in result['routes'][0]['legs'][0]['steps']: query = ('SELECT leg_no FROM leg_data WHERE travel_mode = %s AND Orig_lat = %s AND Orig_lng = %s AND Dest_lat = %s AND Dest_lng = %s AND time_stamp = %s') if steps['travel_mode'] == "pub_tran": travel_mode = steps['travel_mode'] Orig_lat = steps['var_1']['dep']['lat'] Orig_lng = steps['var_1']['dep']['lng'] Dest_lat = steps['var_1']['arr']['lat'] Dest_lng = steps['var_1']['arr']['lng'] time_stamp = leg['_sent_time_stamp']
if steps['travel_mode'] == "a_pied": query = ('SELECT leg_no FROM leg_data WHERE travel_mode = %s AND Orig_lat = %s AND Orig_lng = %s AND Dest_lat = %s AND Dest_lng = %s AND time_stamp = %s') travel_mode = steps['travel_mode'] Orig_lat = steps['var_2']['lat'] Orig_lng = steps['var_2']['lng'] Dest_lat = steps['var_2']['lat'] Dest_lng = steps['var_2']['lng'] time_stamp = leg['_sent_time_stamp'] cursor.execute(query, (travel_mode, Orig_lat, Orig_lng, Dest_lat, Dest_lng, time_stamp)) leg_no = cursor.fetchone()[0] print(leg_no)

cursor = cnx.cursor(buffered = True)

for steps in result['routes'][0]['legs'][0]['steps']: query = ('SELECT leg_no FROM leg_data WHERE travel_mode = %s AND Orig_lat = %s AND Orig_lng = %s AND Dest_lat = %s AND Dest_lng = %s AND time_stamp = %s') if steps['travel_mode'] == "pub_tran": travel_mode = steps['travel_mode'] Orig_lat = steps['var_1']['dep']['lat'] Orig_lng = steps['var_1']['dep']['lng'] Dest_lat = steps['var_1']['arr']['lat'] Dest_lng = steps['var_1']['arr']['lng'] time_stamp = leg['_sent_time_stamp']
if steps['travel_mode'] == "a_pied": query = ('SELECT leg_no FROM leg_data WHERE travel_mode = %s AND Orig_lat = %s AND Orig_lng = %s AND Dest_lat = %s AND Dest_lng = %s AND time_stamp = %s') travel_mode = steps['travel_mode'] Orig_lat = steps['var_2']['lat'] Orig_lng = steps['var_2']['lng'] Dest_lat = steps['var_2']['lat'] Dest_lng = steps['var_2']['lng'] time_stamp = leg['_sent_time_stamp'] cursor.execute(query, (travel_mode, Orig_lat, Orig_lng, Dest_lat, Dest_lng, time_stamp)) leg_no = cursor.fetchone()[0] print(leg_no)

    raise errors.InternalError("Unread result found.") mysql.connector.errors.InternalError: Unread result found.

cursor.execute(query, (leg_travel_mode, leg_Orig_lat, leg_Orig_lng, leg_Dest_lat, leg_Dest_lng)) leg_no = cursor.fetchone()[0]
try: cursor.fetchall() except mysql.connector.errors.InterfaceError as ie: if ie.msg == 'No result set to fetch from.': passelse: raise cursor.execute(query, (leg_travel_mode, leg_Orig_lat, leg_Orig_lng, leg_Dest_lat, leg_Dest_lng, time_stamp))

raise errors.InternalError("Unread result found.") mysql.connector.errors.InternalError: Unread result found.[Finished in 3.3 s with exit code 1]

All that was required was for buffered anycodings_python to be set to true!,This method is faster using anycodings_python "buffered=True",Alternatively, you could enable buffer anycodings_python for any cursor you use:,Is the query too complex and needs splitting anycodings_mysql or is there another issue?

Through trial, I can see the error is anycodings_mysql associated with this piece of code

for steps in result['routes'][0]['legs'][0]['steps']:
   query = ('SELECT leg_no FROM leg_data WHERE travel_mode = %s AND Orig_lat = %s AND Orig_lng = %s AND Dest_lat = %s AND Dest_lng = %s AND time_stamp = %s')
if steps['travel_mode'] == "pub_tran":
   travel_mode = steps['travel_mode']
Orig_lat = steps['var_1']['dep']['lat']
Orig_lng = steps['var_1']['dep']['lng']
Dest_lat = steps['var_1']['arr']['lat']
Dest_lng = steps['var_1']['arr']['lng']
time_stamp = leg['_sent_time_stamp']
if steps['travel_mode'] == "a_pied":
   query = ('SELECT leg_no FROM leg_data WHERE travel_mode = %s AND Orig_lat = %s AND Orig_lng = %s AND Dest_lat = %s AND Dest_lng = %s AND time_stamp = %s')
travel_mode = steps['travel_mode']
Orig_lat = steps['var_2']['lat']
Orig_lng = steps['var_2']['lng']
Dest_lat = steps['var_2']['lat']
Dest_lng = steps['var_2']['lng']
time_stamp = leg['_sent_time_stamp']
cursor.execute(query, (travel_mode, Orig_lat, Orig_lng, Dest_lat, Dest_lng, time_stamp))
leg_no = cursor.fetchone()[0]
print(leg_no)

However, when added to the rest of the code, anycodings_mysql it causes subsequent sections where more anycodings_mysql data is inserted using the cursor to fail anycodings_mysql with this error -

    raise errors.InternalError("Unread result found.")
    mysql.connector.errors.InternalError: Unread result found.

EDIT As per @Gord's help, Ive tried to dump anycodings_mysql any unread results

cursor.execute(query, (leg_travel_mode, leg_Orig_lat, leg_Orig_lng, leg_Dest_lat, leg_Dest_lng))
leg_no = cursor.fetchone()[0]
try:
cursor.fetchall()
except mysql.connector.errors.InterfaceError as ie:
   if ie.msg == 'No result set to fetch from.':
   pass
else :
   raise
cursor.execute(query, (leg_travel_mode, leg_Orig_lat, leg_Orig_lng, leg_Dest_lat, leg_Dest_lng, time_stamp))

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

cursor = cnx.cursor(buffered = True)

I was able to recreate your issue. MySQL anycodings_python Connector/Python apparently doesn't like anycodings_python it if you retrieve multiple rows and anycodings_python don't fetch them all before closing the anycodings_python cursor or using it to retrieve some anycodings_python 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 anycodings_python row then you can put a LIMIT on your anycodings_python 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 anycodings_python any unread results after you have anycodings_python finished working with the rows you anycodings_python 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

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

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

The problem is about the buffer, maybe anycodings_python you disconnected from the previous MySQL anycodings_python connection and now it cannot perform the anycodings_python next statement. There are two ways to anycodings_python give the buffer to the cursor. First, anycodings_python only to the particular cursor using the anycodings_python 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 anycodings_python 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()

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

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