Python cursor cannot retrieve results from stored procedure-PHP Chinese Network Q&A

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: <pre class="brush:php;toolbar:false;">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</pre> 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: <pre class="brush:php;toolbar:false;">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()</pre> But this code has cursor.fetchall() or cursor.fetchone()... <pre class="brush:php;toolbar:false;">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()</pre> ...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... <pre class="brush:php;toolbar:false;">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()</pre> ...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!

$ 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

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

reply all(2)

P粉5233350262023-08-25 11:31:10 2 floor

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

Like +0

Add Reply

P粉0054177482023-08-25 10:38:33 1 floor

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: