Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Python MySQL connector - unread result found when using fetchone

Tags:

python

mysql

I am inserting JSON data into a MySQL database

I am parsing the JSON and then inserting it into a MySQL db using the python connector

Through trial, I can see the error is 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) 

I have inserted higher level details and am now searching the database to associate this lower level information with its parent. The only way to find this unique value is to search via the origin and destination coordinates with the time_stamp. I believe the logic is sound and by printing the leg_no immediately after this section, I can see values which appear at first inspection to be correct

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

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

The issue seems similar to MySQL Unread Result with Python

Is the query too complex and needs splitting or is there another issue?

If the query is indeed too complex, can anyone advise how best to split this?

EDIT As per @Gord's help, Ive tried to dump 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)) 

But, I still get

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

scratches head

EDIT 2 - when I print the ie.msg, I get -

No result set to fetch from 
like image 942
LearningSlowly Avatar asked Apr 21 '15 12:04

LearningSlowly


People also ask

What is cur Fetchone ()?

Syntax: row = cursor. fetchone() This method retrieves the next row of a query result set and returns a single sequence, or None if no more rows are available. By default, the returned tuple consists of data returned by the MySQL server, converted to Python objects.

What does buffered true do?

If buffered is True , the cursor fetches all rows from the server after an operation is executed. This is useful when queries return small result sets. buffered can be used alone, or in combination with the dictionary or named_tuple argument.

What is a buffered cursor?

Cursor buffering is a way to obtain better throughput when fetching a large number of rows. It consists of having the lower layers of the database library fetch more than one row at a time from the database server. By default, rows are obtained one at a time from the database server.


2 Answers

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.

like image 168
LearningSlowly Avatar answered Sep 22 '22 22:09

LearningSlowly


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 
like image 31
Gord Thompson Avatar answered Sep 25 '22 22:09

Gord Thompson