How to Retrieve Result Sets from MySQL Stored Procedures in Python?

Retrieving Results from MySQL Stored Procedures Using Python

Despite making a successful call to a MySQL stored procedure via Python, an error occurs when attempting to fetch the results using cursor.fetchall(). This error message, "mysql.connector.errors.InterfaceError: No result set to fetch from," indicates that the expected result set is not available.

To resolve this issue, consider using the cursor.stored_results() method instead. This method retrieves any available result sets from the stored procedure. In this case, there should be only one result set, which can be assigned to a variable and subsequently iterated over to obtain the desired data.

Here is a modified code snippet that demonstrates the use of cursor.stored_results():

import mysql.connector

cnx = mysql.connector.connect(user='root', host='127.0.0.1', database='mytestdb')
cnx._open_connection()
cursor = cnx.cursor()

cursor.callproc("getperson", [1])

for result in cursor.stored_results():
    people = result.fetchall()

for person in people:
    print(person)

cnx.close()

This approach successfully retrieves the results from the stored procedure and iterates over them to display the person's details. Note that the cmd_query_iter and multi arguments are not necessary since we are only executing a single query.

The above is the detailed content of How to Retrieve Result Sets from MySQL Stored Procedures in Python?. For more information, please follow other related articles on the PHP Chinese website!