mysql - Cannot return results from stored procedure using Python cursor

Question

For some odd reason I can't get results from a callproc call in a Python test app. The stored procedure in MqSQL 5.2.47 looks like this:

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

Now, using PyCharm with Python 3.3, I can't seem to retrieve anything when calling this stored procedure. This code gets me the desired results:

import mysql.connector

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

cursor.execute("select * from person where person.person_id = 1")
people = cursor.fetchall()

for person in people:
    print(person)

cnx.close()

But this code with either cursor.fetchall() or cursor.fetchone()...

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

... returns "mysql.connector.errors.InterfaceError: No result set to fetch from." There's an additional odd behavior using the cursor.execute() method like so...

import mysql.connector

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

cursor.execute("call getperson(1)")
people = cursor.fetchall()

for person in people:
    print(person)

cnx.close()

... because it yields "mysql.connector.errors.InterfaceError: Use cmd_query_iter for statements with multiple queries" followed by "mysql.connector.errors.InterfaceError: Use multi=True when executing multiple statements" despite the fact that I'm only returning one query result rather than multiple result sets. Is the MySQL Python connector treating the execute call on the stored procedure as a double query? How can I just call the stored procedure and get my results back? I really don't want dynamic SQL in my code. Thanks ahead for any advice!

Answer 1

Have you tried picking one of the resultsets?

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

It could be that it's allocating for multiple resultsets even though you only have one SELECT stmt. I know in PHP's MySQLi stored procedures do this to allow for INOUT and OUT variable returns (which again, you have none of, but maybe it's allocating anyways).

The complete code I'm using (which is working) is:

import mysql.connector

cnx = mysql.connector.connect(user='me',password='pw',host='localhost',database='mydb')
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()