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

2021-11-20 00:00:00 python mysql stored-procedures

出于某种奇怪的原因，我无法从 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: Use cmd_query_iter for statements with multiple queries"，然后是mysql.connector.errors.InterfaceError: Use multi=True when execution multiple statements"，尽管事实上我只返回一个查询结果而不是多个结果集.MySQL Python 连接器是否将存储过程上的执行调用视为双重查询?我怎样才能调用存储过程并取回我的结果?我真的不想在我的代码中使用动态 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()

相关文章