当前位置: 澳门新濠3559 > 数据库 > 正文

Retrieving Out Params From a Stored Procedure With Python

时间:2019-10-06 19:46来源:数据库
AUG 8TH, 2013 I was hacking some python today which calls a stored procedure thatsends back an out parameter to indicate whether or not it completed istask succesfully. Now, calling a stored procedure in python is prettystraight forward 1 c

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.

Retrieving Out Params From a Stored Procedure With Python,retrievingparams

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.

Out Params From a Stored Procedure With Python,retrievingparams ...

 

Using Stored Programs with MySQLdb

The techniques for calling stored programs with MySQLdb differ only slightly from those for using traditional SQL statements. That is, we create a cursor, execute the SQL to call the stored program, and iterate through result sets. The two key differences are that we must potentially deal with multiple result sets and that we may have to retrieve output parameters from the stored program call.

If you read the Python DB API specification, you might notice that the specification includes a cursor method for directly calling stored programsthe callproc cursor method. The callproc method was not implemented in MySQLdb as we went to press, although the maintainer of MySQLdb, Andy Dustman, is working on an implementation that will likely be available by the time you read this. Check out the book's web site (see the Preface) for an update. This method is not implemented in MySQLdb (version 1.2, at least). Luckily, everything you need to call stored programs is available through other methods, so you don't need to wait for callproc to use stored programs with Python.

16.3.1. Calling Simple Stored Programs

The procedure for calling a simple stored programone that returns no result sets and takes no parametersis the same as for executing any non-SELECT statement. We create a cursor and execute the SQL text, as shown in Example 16-18.

Example 16-18. Executing a simple stored procedure

 

cursor1=conn.cursor( ) cursor1.execute("call simple_stored_proc( )") cursor1.close( ) 

 

If the stored procedure takes input parameters, we can supply them using the second argument to the execute( ) method. In Example 16-19, we define a Python function that accepts input parameters and applies them to the sp_apply_discount procedure.

Example 16-19. Supplying input parameters to a stored procedure

 

def apply_discount(p1,p2):  cursor1=conn.cursor( ) cursor1.execute("call sp_apply_discount(%s,%s)",(p1,p2)) cursor1.close( ) 

 

16.3.2. Retrieving a Single Stored Program Result Set

Retrieving a single result set from a stored program is exactly the same as retrieving a result set from a SELECT statement. Example 16-20 shows how to retrieve a single result set from a stored procedure.

Example 16-20. Retrieving a single result set from a stored procedure

 

cursor1=conn.cursor(MySQLdb.cursors.DictCursor) cursor1.execute("CALL sp_emps_in_dept(%s)",(1)) for row in cursor1: print "%d %s %s" %  (row['employee_id'],row['surname'],row['firstname']) cursor1.close( ) 

 

If you receive a 1312 error at this point (PROCEDURE X can't return a result set in the given context), then it is an indication that you need to specify the CLIENT.MULTI_RESULTS flag in your connection, as outlined in "Creating a Connection" earlier in this chapter.

16.3.3. Retrieving Multiple Stored Program Result Sets

Unlike other SQL statements, stored programs can return multiple result sets. To access more than one result set, we use the nextset( ) method of the cursor object to move to the next result set.

For instance, suppose that we have a stored procedure that returns two result sets, as shown in Example 16-21.

Example 16-21. Stored procedure that returns two result sets

 

CREATE PROCEDURE sp_rep_report(in_sales_rep_id int) READS SQL DATA BEGIN  SELECT employee_id,surname,firstname FROM employees WHERE employee_id=in_sales_rep_id;  SELECT customer_id,customer_name FROM customers WHERE sales_rep_id=in_sales_rep_id;  END; 

 

To retrieve the two result sets, we fetch the first result set, call nextset( ), then retrieve the second result set. Example 16-22 shows this technique.

Example 16-22. Retrieving two results from a stored procedure

 

cursor=conn.cursor(MySQLdb.cursors.DictCursor) cursor.execute("CALL sp_rep_report(%s)",(rep_id)) print "Employee details:" for row in cursor: print "%d %s %s" % (row["employee_id"], row["surname"], row["firstname"]) cursor.nextset( ) print "Employees customers:" for row in cursor: print "%d %s" % (row["customer_id"], row["customer_name"]) cursor.close( ) 

 

16.3.4. Retrieving Dynamic Result Sets

It's not at all uncommon for stored programs to return multiple result sets and for the result set structures to be unpredictable. To process the output of such a stored program, we need to combine the nextset( ) method with the cursor.description property described in the "Getting Metadata" section earlier in this chapter. The nextset( ) method returns a None object if there are no further result sets, so we can keep calling nextset( ) until all of the result sets have been processed. Example 16-23 illustrates this technique.

Example 16-23. Retrieving dynamic result sets from a stored procedure

 

1 def call_multi_rs(sp): 2 rs_id=0; 3 cursor = conn.cursor( ) 4 cursor.execute ("CALL " sp) 5 while True: 6 data = cursor.fetchall( ) 7 if cursor.description: #Make sure there is a result 8 rs_id =1 9 print "nResult set =" % (rs_id) 10 print "--------------n" 11 names = [] 12 lengths = [] 13 rules = [] 14 for field_description in cursor.description: 15 field_name = field_description[0] 16 names.append(field_name) 17 field_length = field_description[2] or 12 18 field_length = max(field_length, len(field_name)) 19 lengths.append(field_length) 20 rules.append('-' * field_length) 21 format = " ".join(["%%-%ss" % l for l in lengths]) 22 result = [ format % tuple(names), format % tuple(rules) ] 23 for row in data: 24 result.append(format % tuple(row)) 25 print "n".join(result) 26 if cursor.nextset( )==None: 27 break 28 print "All rowsets returned" 29 cursor.close( ) 

 

Example 16-23 implements a Python function that will accept a stored procedure name (together with any arguments to the stored procedure), execute the stored procedure, and retrieve any result sets that might be returned by the stored procedure.

Let's step through this code:

澳门新濠3559, 

Line(s)

Explanation

2

rs_id is a numeric variable that will keep track of our result set sequence.

34

Create a cursor and execute the stored procedure call. The sp variable contains the stored procedure text and is passed in as an argument to the Python function.

5

Commence the loop that will be used to loop over all of the result sets that the stored procedure call might return.

6

Fetch the result set from the cursor.

7

Ensure that there is a result set from the stored procedure call by checking the value of cursor.description. This is a workaround to a minor bug in the MySQLdbimplementation (version 1.2) in which nextset( ) returns true even if there is no next result set, and only returns False once an attempt has been made to retrieve that null result. This bug is expected to be resolved in an upcoming version of MySQLdb.

1122

Determine the structure of the result set and create titles and formats to nicely format the output. This is the same formatting logic we introduced in Example 16-17.

2325

Print out the result set.

26

Check to see if there is another result set. If there is not, nextset( ) returns None and we issue a break to exit from the loop. If there is another result set, we continue the loop and repeat the process starting at line 6.

28 and 29

Acknowledge the end of all result sets and close the cursor.

 

 

Example 16-24 shows a stored procedure with "dynamic" result sets. The number and structure of the result sets to be returned by this stored procedure will vary depending on the status of theemployee_id provided to the procedure.

Example 16-24. Stored procedure with dynamic result sets

 

CREATE PROCEDURE sp_employee_report (in_emp_id INTEGER, OUT out_customer_count INTEGER) BEGIN  SELECT employee_id,surname,firstname,date_of_birth FROM employees WHERE employee_id=in_emp_id;  SELECT department_id,department_name FROM departments WHERE department_id= (select department_id FROM employees WHERE employee_id=in_emp_id);  SELECT COUNT(*) INTO out_customer_count FROM customers WHERE sales_rep_id=in_emp_id;  IF out_customer_count=0 THEN SELECT 'Employee is not a current sales rep'; ELSE SELECT customer_name,customer_status FROM customers WHERE sales_rep_id=in_emp_id;  SELECT customer_name,SUM(sale_value) as "TOTAL SALES", MAX(sale_value) as "MAX SALE" FROM sales JOIN customers USING (customer_id) WHERE customers.sales_rep_id=in_emp_id GROUP BY customer_name; END IF; END 

 

We can use the Python function shown in Example 16-23 to process the output of this stored procedure. We would invoke it with the following command:

call_multi_rs("sp_employee_report(1,@out_customer_count)") 

 

We pass in 1 to produce a report for employee_id=1; the @out_customer_count variable is included to receive the value of the stored procedure's output parameter (see the next section, "Obtaining Output Parameters"). Partial output from this procedure is shown in Example 16-25.

Example 16-25. Output from a dynamic stored procedure call

 

Result set 1 --------------  employee_id surname firstname date_of_birth ----------- ------- --------- ------------------- 1 FERRIS LUCAS 1960-06-21 00:00:00  Result set 2 --------------  department_id department_name ------------- --------------- 14 KING  Result set 3 --------------  customer_name customer_status ------------------------------- --------------- GRAPHIX ZONE INC DE None WASHINGTON M AAAIswAABAAANSjAAS None 

 

16.3.5. Obtaining Output Parameters

As you know, stored procedures can include OUT or INOUT parameters, which can pass data back to the calling program. The MySQLdb extension does not provide a method to natively retrieve output parameters , but you can access their values through a simple workaround.

Earlier, in Example 16-24, we showed a stored procedure that returned multiple result sets, but also included an output parameter. We supplied a MySQL user variable (prefixed by the @ symbol) to receive the value of the parameter. All we need to do now, in Example 16-26, is to retrieve the value of that user variable using a simple SELECT.

Example 16-26. Retrieving the value of an output parameter

 

call_multi_rs("sp_employee_report(1,@out_customer_count)") cursor2=conn.cursor( ) cursor2.execute("SELECT @out_customer_count") row=cursor2.fetchone( ) print "Customer count=%s" % row[0] cursor2.close( ) 

 

What about INOUT parameters? This is a little trickier, although luckily we don't think you'll use INOUTparameters very much (it's usually better practice to use separate IN and OUT parameters). Consider the stored procedure in Example 16-27.

Example 16-27. Stored procedure with an INOUT parameter

 

CREATE PROCEDURE randomizer(INOUT a_number FLOAT) NOT DETERMINISTIC NO SQL SET a_number=RAND( )*a_number; 

 

To handle an INOUT parameter, we first issue a SQL statement to place the value into a user variable, execute the stored procedure, and then retrieve the value of that user parameter. Code that wraps the stored procedure call in a Python function is shown in Example 16-28.

Example 16-28. Handling an INOUT stored procedure parameter

 

def randomizer(python_number): cursor1=conn.cursor( ) cursor1.execute("SET @inoutvar=%s",(python_number)) cursor1.execute("CALL randomizer(@inoutvar)") cursor1.execute("SELECT @inoutvar") row=cursor1.fetchone( ) cursor1.close( ) return(row[0]) 

 

编辑:数据库 本文来源:Retrieving Out Params From a Stored Procedure With Python

关键词: 澳门新濠3559