MySQLdb Stored Procedure Out Parameter not working

Question

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

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.

For example, here is the example taken from here:

Definition of the multiply stored procedure:

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

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

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

I correctly get the result:

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

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

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

(5, 5, 0)

So, what am I doing wrong here?

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.

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

Answer 1

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