MySQLdb Stored Exit Procedure Parameter Does Not Work

Question

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

I am trying to call a stored procedure with an Out parameter. The SP was called successfully, but the value of the Out parameter is not like my python code.

For example, here is an example taken from here :

Multiple stored procedure definition:

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

If I call SP from the command line as follows:

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

I get the result correctly:

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

But if I call it using python code using the MySQLdb package, for example:

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

then I don't get the out parameter in my result cortel:

 (5, 5, 0) 

So what am I doing wrong here?

UPDATE: Just found this warning in 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. 

Also note that the callproc function simply returns the same input tuple. Therefore, the bottom line is not possible. Go back to the drawing board, then ...

Answer 1

All you need is an extra 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),) 

Answer 2

Check this out, just remember the database connection setup just to initialize the MYSQL database and try something like:

Just to find out what to say, definition of a database table:

 CREATE TABLE table_tmp ( data1 INT(11), data2 VARCHAR(10), data3 TINYINT(1) -- This will be the output value ); 

Database Definition Procedure:

 DROP PROCEDURE IF EXISTS sp_test_tmp; CREATE DEFINER=`<user_in_the_db>`@`%` PROCEDURE `sp_test_tmp`( IN in_data1 INT , IN in_data2 VARCHAR(10) , IN in_data3 BOOL , OUT result BOOL ) BEGIN INSERT INTO table_tmp ( data1 ,data2 ,data3 ) VALUES ( in_data1 ,in_data2 ,in_data3 ); SET result = FALSE; -- Setting the output to our desired value COMMIT; -- This will help to update the changes in the database, with variable -- the row never will get updated (the select/get a little -- complex less) END; 

Python code Using a list of parameters, I think of a generic function;)

 TRUE = 1 -- My own definition, for make compatible Mysql and Python Boolean data representation FALSE = 0 def execute_procedure(pname='sp_test_tmp',pargs=(1,' [e-mail] ',TRUE,FALSE)): try: cursor = mysql.connect().cursor() status = cursor.callproc(pname, pargs) cursor.execute('SELECT @_sp_test_tmp_3') # This is the magic result = cursor.fetchone() # Get the Values from server if result[0] == TRUE: print ("The result is TRUE") resp = True elif result[0] == FALSE: resp = False print("The result is FALSE") else: resp = False print("This is crazy!!!") return str(resp) except Exception as inst: exception = type(inst) print(exception) return "DON'T" finally: cursor.close()