Why Does `fetchone()` Cause \'Unread Result Found\' in Python\'s MySQL Connector, and How Can Buffered Cursors Fix It?

Python MySQL Connector: Unread Result Found When Using fetchone

This question pertains to an issue encountered while inserting JSON data into a MySQL database using the Python MySQL connector. Upon inserting higher-level details and attempting to associate lower-level information with the parent based on origin and destination coordinates and time_stamp, the code would fail with the error "Unread result found."

The query used to search for the unique leg number for each set of coordinates and time_stamp is:

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

Initially, an attempt was made to discard 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))

However, the error persisted.

Upon further investigation, it was discovered that the solution lies in specifying a buffered cursor. Adding buffered=True to the cursor initialization line resolved the issue:

cursor = cnx.cursor(buffered=True)

This ensures that all rows are fetched behind the scenes, preventing the error from occurring.

The above is the detailed content of Why Does `fetchone()` Cause \'Unread Result Found\' in Python\'s MySQL Connector, and How Can Buffered Cursors Fix It?. For more information, please follow other related articles on the PHP Chinese website!