Python cursor cannot retrieve results from stored procedure

Question

For some strange reason, I can't get the results from the callproc call in my Python test application. The stored procedure in MqSQL5.2.47 is as follows: CREATEPROCEDURE`mytestdb`.`getperson`(INpersonidINT)BEGINselectperson.person_id,person.person_fname,person.person_mi,person.person_lna

P粉523335026 · Answer

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

P粉005417748 · Answer

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