How to Retrieve Results from a MySQL Stored Procedure using Python's `cursor.stored_results()`?

Retrieving Results from a Stored Procedure using Python Cursor

This article addresses the issue of retrieving results from a MySQL stored procedure call using a Python cursor. Despite a successful connection to the database and the use of cursor.execute() on a normal SQL query, attempts to fetch results from a stored procedure call using cursor.fetchall() or cursor.fetchone() return an error: "mysql.connector.errors.InterfaceError: No result set to fetch from."

Solution

The solution lies in retrieving the resultset using cursor.stored_results(). This method iterates through any available resultsets, allowing the programmer to work with the desired resultset.

The following code demonstrates the correct approach:

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 code successfully fetches the results from the stored procedure without encountering errors.

Consideration

It's worth noting that the MySQL Python connector may allocate multiple resultsets even in the absence of multiple SELECT statements. This allocation can occur due to the inclusion of INOUT and OUT variables in the stored procedure, which you do not have. Nonetheless, using cursor.stored_results() effectively handles this potential allocation and allows for successful result retrieval.

The above is the detailed content of How to Retrieve Results from a MySQL Stored Procedure using Python's `cursor.stored_results()`?. For more information, please follow other related articles on the PHP Chinese website!