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

Answer 2

Getting the result of a stored procedure after calling cursor.callproc depends on these factors:

• whether the result of calling the procedure is assigned to an INOUT or OUT parameter
• whether the result consists of a single row or a result set (or result sets)
• the python package used to make the call

The DBAPI spec has this to say on cursor.callproc:

Call a stored database procedure with the given name. The sequence of parameters must contain one entry for each argument that the procedure expects. The result of the call is returned as modified copy of the input sequence. Input parameters are left untouched, output and input/output parameters replaced with possibly new values.

The procedure may also provide a result set as output. This must then be made available through the standard .fetch*() methods.

In practice, using the return value of cursor.callproc can only work if the procedure returns a single row, with the number of columns matching the number of INOUT and OUT parameters, so there is some variation in how the results are handled.

---

Here is how these cases are handled by the principal MySQL Python connector packages - MySQL Connector, mysqlclient (MySQLdb) and PyMySQL.

Single row result, returned via 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 that the database is queried for the output parameters, and the results then fetched via the cursor; the value is a tuple of tuples. The parameter names to be queried are of 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 defined

• MySQL Connector exposes the result via the cursor's stored_results method (cursor.stored_results is not part of the DBAPI spec)

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

• mysqlclient and PyMySQL expose the result via the cursor's fetch* methods

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

Multiple result sets, no INOUT or OUT parameters defined

• MySQL Connector exposes the result via the cursor's stored_results method

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

• mysqlclient and PyMySQL require that each result set be fetched via the cursor, while calling cursor.nextset to advance to the next result set. Note that an extra, empty result set may be returned, a result of calling the procedure (this would also happen in the previous examples, if the result set were retrieved via cursor.nextset instead of only calling cursor.fetchall once).

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

---

Version Info

$ 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