How to Retrieve Results from MySQL Stored Procedures in Python?-Mysql Tutorial-php.cn

How to Retrieve Results from MySQL Stored Procedures in Python?

Patricia Arquette

Release： 2024-12-07 01:10:14

Original

617 people have browsed it

How to Retrieve Results from MySQL Stored Procedures in Python?

Retrieving Results from Stored Procedures in Python

Problem:

Attempts to retrieve results from a MySQL stored procedure using the Python MySQL Connector fail with the error "No result set to fetch from."

Stored Procedure:

CREATE PROCEDURE `mytestdb`.`getperson` (IN personid INT)
BEGIN
   select person.person_id,
          person.person_fname,
          person.person_mi,
          person.person_lname,
          person.persongender_id,
          person.personjob_id
     from person
    where person.person_id = personid;
END

Copy after login

Python Code:

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])
people = cursor.fetchall()

for person in people:
    print(person)

cnx.close()

Copy after login

Cause:

The MySQL Connector allocates multiple result sets for stored procedures, even when returning a single SELECT statement result.

Solution:

Iterate over the result sets and fetch data from the appropriate one:

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

Copy after login

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