How Can I Retrieve Results from MySQL Stored Procedures Using Python?

Retrieving Results from Stored Procedures Using Python Cursor

In Python, connecting to a MySQL database and executing stored procedures can be achieved using the mysql.connector module. However, there can be challenges in retrieving results from stored procedures.

To resolve this, the cursor used to call the stored procedure must be explicitly instructed to retrieve the results using the stored_results() method. This method returns an iterator of result sets, which allows for the retrieval of multiple result sets if the stored procedure returns them.

In the example provided, the stored procedure getperson returns a single result set. The following code shows how to retrieve its 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()

In this code, the stored procedure is called with a parameter of 1 and the stored_results() method is used to retrieve the single result set. The results are then iterated over and printed.

This solution overcomes the issues encountered in the code provided, such as the "No result set to fetch from" error and the improper handling of multiple result sets. By explicitly retrieving the result set using stored_results(), the code can access the results of the stored procedure call as expected.

The above is the detailed content of How Can I Retrieve Results from MySQL Stored Procedures Using Python?. For more information, please follow other related articles on the PHP Chinese website!