Using Stored Programs with MySQLdb, storedmysqldb

Using Stored Programs with MySQLdb, storedmysqldb

Http://flylib.com/books/en/1.142.1.125/1/

 

Using Stored Programs with MySQLdb

The techniques for calling stored programsMySQLdbDiffer 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 between des a cursor method for directly calling stored programstheCallprocCursor method.CallprocMethod was not implemented inMySQLdbAs we went to press, although the maintainerMySQLdb, 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 inMySQLdb(Version 1.2, at least). Luckily, everything you need to call stored programs is available through other methods, so you don't need to waitCallprocTo 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-SELECTStatement. 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 toExecute ()Method. In Example 16-19, we define a Python function that accepts input parameters and applies them toSp_apply_discountProcedure.

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 fromSELECTStatement. 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 specifyCLIENT. MULTI_RESULTSFlag 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 useNextset ()Method ofCursorObject 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, callNextset (), 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 combineNextset ()Method withCursor. descriptionProperty described in the "Getting Metadata" section earlier in this chapter.Nextset ()Method returnsNoneObject if there are no further result sets, so we can keep callingNextset ()Until all of the result sets have been processed. Example 16-23 analyze strates 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 %3d" % (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:

 

Line (s)	Explanation
2	Rs_idIs a numeric variable that will keep track of our result set sequence.
34	Create a cursor and execute the stored procedure call.SpVariable 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 valueCursor. description. This is a workaround to a minor bug inMySQLdbImplementation (version 1.2) in whichNextset ()ReturnsTrueEven if there is no next result set, and only returnsFalseOnce an attempt has been made to retrieve that null result. This bug is expected to be resolved in an upcoming versionMySQLdb.
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 ()ReturnsNoneAnd we issueBreakTo 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 ofEmployee_idProvided 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 wocould invoke it with the following command:

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

 

We pass in1To produce a reportEmployee_id = 1;@ Out_customer_countVariable is wrongly ded 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 includeOUTOrINOUTParameters, which can pass data back to the calling program.MySQLdbExtension 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 encoded an output parameter. We supplied a MySQL user variable (prefixed by@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 simpleSELECT.

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 aboutINOUTParameters? This is a little trickier, although luckily we don't think you'll useINOUTParameters very much (it's usually better practice to use separateINAndOUTParameters). 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 handleINOUTParameter, 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])