Retrieving Out Params From a Stored Procedure With Python

http://www.rodneyoliver.com/blog/2013/08/08/retrieving-out-params-from-a-stored-procedure-with-python/

AUG 8TH, 2013

I was hacking some python today which calls a stored procedure that sends back an out parameter to indicate whether or not it completed is task succesfully. Now, calling a stored procedure in python is pretty straight forward

1	cursor.callproc("StoredProcName", (param1, param2, etc..))

I needed to grab the out parameter in my code and execute some logic based on the response returned. I’m a python noob and suprisingly I didn’t find any thing that really gave a good example. I found a few posts on Stack Overflow like this and this. Which gave me some clues. Then I found a post that led me to the mysql-python documentation about cursor objects. When I read the following snippet it clicked for me

callproc(procname, args)

Calls stored procedure procname with the sequence of arguments in args. Returns the original arguments. Stored procedure support only works with MySQL-5.0 and newer.

Compatibility note: PEP-249 specifies that if there are OUT or INOUT parameters, the modified values are to be returned. This is not consistently possible with MySQL. Stored procedure arguments must be passed as server variables, and can only be returned with a SELECT statement. Since a stored procedure may return zero or more result sets, it is impossible for MySQLdb to determine if there are result sets to fetch before the modified parmeters are accessible.

The parameters are stored in the server as @procnamen, where n is the position of the parameter. I.e., if you cursor.callproc(‘foo’, (a, b, c)), the parameters will be accessible by a SELECT statement as @foo_0, @foo_1, and @_foo_2.

Compatibility note: It appears that the mere act of executing the CALL statement produces an empty result set, which appears after any result sets which might be generated by the stored procedure. Thus, you will always need to use nextset() to advance result sets

The key part for me was:

The parameters are stored in the server as @procnamen, where n is the position of the parameter. I.e., if you cursor.callproc(‘foo’, (a, b, c)), the parameters will be accessible by a SELECT statement as @foo_0, @foo_1, and @_foo_2.

So what I need to do is perform an cursor.execute on the server variable @_procname_n. The results are tuples so accessing their values should be as simple as result[0]. Here is what I came up with. This is acutal code from the project I’m working on so I know it works.

1 2 3 4 5 6 7 8 9 10

cursor.callproc("DeleteUser", (user[0].rstrip(), out_error)) # This is how we have to get the out params in python. See PEP-249 cursor.execute("select @_DeleteUser_1") result = cursor.fetchall() if result[0]: #in this case a non-null response denotes a problem "user profile inavlid" print "Not Found: ", user[0].rstrip(), " ", user[1] else: print user[0].rstrip(), " ", user[1]

If I need to get more out parameters I just do this:

1 2 3 4

cursor.execute("select @_DeleteUser_2") cursor.execute("select @_DeleteUser_3") #etc... result = cursor.fetchall()

I’m sure there is a more elegant “pythonic” way to do this but its cool to figure it out yourself and its a great learning experience.