Retrieving Out Params From a Stored Procedure…

2018-06-17 23:00:31来源:未知 阅读 ()

新老客户大回馈,云服务器低至5折

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(), "\t", user[1]
 else:
    print user[0].rstrip(), "\t", 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.

标签:

版权申明:本站文章部分自网络,如有侵权,请联系:west999com@outlook.com
特别注意:本站所有转载文章言论不代表本站观点,本站所提供的摄影照片,插画,设计作品,如需使用,请与原作者联系,版权归原作者所有

上一篇:Snippet: Fetching results after calling stored procedures us

下一篇:MySQL带参数的存储过程小例子