无法使用Python游标从存储过程中返回结果

本文介绍了无法使用Python游标从存储过程中返回结果的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

出于某种奇怪的原因，我无法从Python测试应用程序中的callproc调用中获得结果. MqSQL 5.2.47中的存储过程如下所示:

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

现在，在Python 3.3中使用PyCharm时，在调用此存储过程时似乎无法检索任何内容.这段代码为我提供了预期的结果:

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

但是此代码带有cursor.fetchall()或cursor.fetchone()...

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

...返回"mysql.connector.errors.InterfaceError:无结果可取."使用cursor.execute()方法还有一个额外的奇怪行为，就像这样……

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

...，因为它会产生"mysql.connector.errors.InterfaceError:对具有多个查询的语句使用cmd_query_iter"，然后产生"mysql.connector.errors.InterfaceError:在执行多个语句时使用multi = True"，尽管事实是我只返回一个查询结果，而不是多个结果集. MySQL Python连接器是否将对存储过程的execute调用视为双重查询?我如何才能调用存储过程并取回结果?我真的不希望在代码中使用动态SQL.谢谢您的任何建议！

... 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!

推荐答案

您是否尝试过选择其中一个结果集?

Have you tried picking one of the resultsets?

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

即使您只有一个SELECT stmt，也可能正在分配多个结果集.我知道在PHP的MySQLi存储过程中，这样做是为了允许INOUT和OUT变量返回(这又一次，您什么也没有，但是也许无论如何都在分配).

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