Python cursor cannot retrieve results from stored procedure

Question

For some strange reason, I can't get results from a callproc call in a Python test application. 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 and Python 3.3, I can't seem to retrieve anything when calling this stored procedure. This code gets the result I want:

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 has 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: There is no result set to obtain from." There is an additional strange behavior using the cursor.execute() method, like this...

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 produces "mysql.connector.errors.InterfaceError: using cmd_query_iter for statements with multiple queries" followed by "mysql.connector.errors.InterfaceError: using multi=True when executing multiple statements ”, despite the fact that I am only returning a single query result rather than multiple result sets. Does the MySQL Python connector treat execute calls to stored procedures as double queries? How do I call a stored procedure and get the results? I really don't want to use dynamic SQL in my code. Thanks in advance for any advice!

Answer 1

The result of obtaining the stored procedure after calling cursor.callproc depends on the following factors:

• Whether the result of the calling procedure is assigned to the INOUT or OUT parameter
• Whether the result consists of a single row or a result set (or multiple result sets)
• Python package used to make calls

DBAPI Specification There is this statement on cursor.callproc:

In fact, using the return value from Cursor.callproc only works if the procedure returns a single row and the number of columns matches the number of INOUT and OUT parameters, so there are some changes in how the results are handled.

---

Here's how the main MySQL Python connector packages handle these situations - MySQL Connector, mysqlclient (MySQLdb) and PyMySQL.

Single row result, returned through INOUT or OUT parameters

• MySQL Connector Returns a modified copy of the input sequence as the return value of cursor.callproc; the value is a tuple.

  params = [in_param, out_param1, out_param2]
  in_, out1, out2 = cursor.callproc("test_proc", params)

• mysqlclient and PyMySQL require querying the database for output parameters and then obtaining the results via a cursor; the value is a tuple of tuples. The parameter name to be queried is in the form '@_{procedure_name}_{params.index(param)}'

  cursor.callproc("test_proc", params)
  cursor.execute("""SELECT @_test_proc_0, @_test_proc_1""")
  result = cursor.fetchall()

One or more rows in a single result set, no INOUT or OUT parameters are defined

• MySQL Connector Via the cursor's stored_results method (cursor.stored_results is not part of the DBAPI specification)

  cursor.callproc("test_proc", params)
  results = [r.fetchall() for r in cursor.stored_results()]

• mysqlclient and PyMySQL expose the results through the cursor’s fetch* methods

  cursor.callproc("test_proc", params)
  results = cursor.fetchall()

Multiple result sets, no INOUT or OUT parameters are defined

• MySQL Connector Expose results through the cursor’s stored_results method

  cursor.callproc("test_proc", params)
  results = [r.fetchall() for r in cursor.stored_results()]

• mysqlclient and PyMySQL are required to advance to the next result set before calling cursor.nextset. Note that an additional empty result set may be returned as a result of the calling procedure (if the result set is retrieved via cursor.nextset instead of just calling cursor.fetchall once).

  cursor.callproc("test_proc", params)
  results = [cursor.fetchall()]
  while cursor.nextset():
      results.append(cursor.fetchall())

---

Version Information

$ mysql --version
mysql  Ver 15.1 Distrib 10.1.41-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

$ pip list | grep -i mysql
mysql-connector-python 8.0.18 
mysqlclient            1.4.6  
PyMySQL                0.9.3

Answer 2

Have you tried selecting one of the result sets?

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

Even if you only have one SELECT stmt, it may allocate multiple result sets. I know this is done in PHP's MySQLi stored procedures to allow INOUT and OUT variables to be returned (again, you don't, but maybe it's being allocated anyway).

The complete code I'm using (running) 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()