MySQLdb存储过程输出参数不起作用 [英] MySQLdb Stored Procedure Out Parameter not working

问题描述

我有一个托管在Google Cloud SQL上的数据库，以及一个用于查询它的python脚本.

I have a database hosted on Google Cloud SQL, and a python script to query it.

我正在尝试调用具有Out参数的存储过程. SP调用成功，但是Out参数的值似乎没有返回到我的python代码中.

I am trying to call a Stored Procedure that has an Out Parameter. The SP is called successfully, but the value of the Out Parameter doesn't seem to be returned to my python code.

例如，以下示例取自乘法存储过程的定义:

CREATE PROCEDURE multiply(IN pFac1 INT, IN pFac2 INT, OUT pProd INT)
BEGIN
  SET pProd := pFac1 * pFac2;
END

如果我这样从命令行调用SP:

If I call the SP from the command line like this:

CALL multiply(5, 5, @Result)
SELECT @Result

我正确地得到了结果:

+---------+
| @Result |
+---------+
|      25 |
+---------+

但是，如果我使用MySQLdb包通过python代码调用它，就像这样:

But if I call it with python code using the MySQLdb package, like this:

args = (5, 5, 0) # 0 is to hold value of the OUT parameter pProd
result = cursor.callproc('multiply', args)
print result

然后我的结果元组中没有out参数:

then I do not get the out parameter in my result tuple:

(5, 5, 0)

那么，我在这里做什么错了?

So, what am I doing wrong here?

更新: 刚刚在callproc代码中发现此警告:

UPDATE: Just found this warning in the callproc code:

    Compatibility warning: PEP-249 specifies that any modified
    parameters must be returned. This is currently impossible
    as they are only available by storing them in a server
    variable and then retrieved by a query. Since stored
    procedures return zero or more result sets, there is no
    reliable way to get at OUT or INOUT parameters via callproc.
    The server variables are named @_procname_n, where procname
    is the parameter above and n is the position of the parameter
    (from zero). Once all result sets generated by the procedure
    have been fetched, you can issue a SELECT @_procname_0, ...
    query using .execute() to get any OUT or INOUT values.

还要注意，callproc函数仅返回相同的输入arg元组.因此，这是不可能的.然后回到绘图板...

And also note that the callproc function merely returns the same input arg tuple. So bottom line is this is not possible. Back to the drawing board then ...

推荐答案

您所需要做的就是添加一个SELECT来访问输出值:

All you need is an additional SELECT to access the output values:

>>> curs.callproc('multiply', (5, 5, 0))
(5, 5, 0)
>>> curs.execute('SELECT @_multiply_0, @_multiply_1, @_multiply_2')
1L
>>> curs.fetchall()
((5L, 5L, 25L),)

这篇关于MySQLdb存储过程输出参数不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！