无法使用Python游标从存储过程中返回结果 [英] Cannot return results from stored procedure using Python cursor

问题描述

出于某种奇怪的原因，我无法从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()

这篇关于无法使用Python游标从存储过程中返回结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！