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 " Result set %3d" % (rs_id) 10 print "-------------- " 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 " ".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_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 MySQLdb implementation (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 the employee_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 INOUT parameters 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]) |