Python calls a MySQL stored procedure to return a result set

Stored procedures:

Delimiter|  Create procedureGet_product_info (inchIMiDint(Ten),intIuservarchar( -))begin      Select *  fromTb_testwhereMid=IMiD and User =Iuser;End;|delimiter;

Python calls:

ImportMysql.connector CNX= Mysql.connector.connect (user='Test', password='xxxx', host='192.168.10.194', database='Test')  #cnx._open_connection ()cursor =cnx.cursor ()Try: Args= (1,'op') Cursor.callproc ("Get_product_info", args)#Stored_results () return all queries    #result gets the query once     forResultinchcursor.stored_results ():#RS returns one query resultrs =Result.fetchall ()exceptException as E:Print(e)

Refrence:

16.3. Using Stored Programs with MySQLdb

The techniques for calling stored programs and MySQLdb differ only slightly from those for using traditional SQL Statements. That's, we create a cursor, execute the SQL to call the stored program, and iterate through result sets. The both key differences is, we must potentially deal with multiple result sets and so we may has to retrieve OUTPU t parameters from the stored program call.

If you read the Python DB API specification, you might notice the specification includes a cursor method for directly Calling stored programsthe callproc cursor method. The Callprocmethod is not implemented in MySQLdb as we went to press, although the maintainer of My SQLdb, Andy Dustman, is working on a implementation that would likely be available by the time you read this. Check out the book's Web site (see the preface) for an update. This method isn't implemented in MYSQLDB (version 1.2, at least). Luckily, everything you need to call stored programs are 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, 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 () me Thod. In Example 16-19, we define a Python function this 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 was 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 of error at this point ( PROCEDURE X can ' t return a result set in the given context) and then it is a indication that 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 NEX T result set.

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

Example 16-21. Stored procedure that returns the 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 both 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 results from a stored procedure

: print "%d%s"% (row["customer_id"], row["customer_name"]) Cursor.close ()

16.3.4. Retrieving Dynamic Result Sets

It ' s not at all uncommon-stored programs to return multiple result sets and for the result set structures to be unpred Ictable. To process the output of such a stored program, we need to combine the Nextset () method with the Cursor.desc Ription Property described in the "Getting Metadata" sections earlier in this chapter. The Nextset () method returns a None object If there is no further result sets, so we can keep calling Nextset () until all of the result sets has 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 whi Le true:6 data = Cursor.fetchall () 7 if cursor.description: #Make sure there is a result 8 rs_id+=1 9 print "\nresult SE T%3d "% (rs_id) print"--------------\ n "One names = [] lengths = [] rules = [] + for field_description in Curso R.description:15 field_name = field_description[0] Names.append (field_name) field_length = field_description[2] or Field_length = Max (Field_length, Len (field_name)) lengths.append (field_length) rules.append ('-' * field_lengt h) + format = "". Join (["%%-%SS"% l for L in lengths]): result = [format% tuple (names), format% tuple (rules)] + F Or row in data:24 result.append (format% tuple (row)) print "\ n". Join (Result) if Cursor.nextset () ==none:27 break 2 8 print "All rowsets returned" Cursor.close ()

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

Let's step through this code:

Line (s)	Explanation
2	rs_id is a numeric variable, that would 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 a argument to the Python function.
5	Commence the loop, that would be, used to loop through all of the result sets, the stored procedure call might return.
6	Fetch The result set from the cursor.
7	Ensure that there was a result set from the stored procedure call by checking the value of cursor.description . This was a workaround to a minor bug in the mysqldbimplementation (version 1.2) in which Nextset () retur NS true even if there is no next result set, and only returns False Once an attempt have been made to RET Rieve that null result. This bug was expected to being 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 was 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 Noneand 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.
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 being returned by this stored procedure would vary depending on the status of T He employee_id provided to the procedure.

Example 16-24. Stored procedure with dynamic result sets

CREATE PROCEDURE sp_em Ployee_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 EMP LOYEE_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 was 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 (Cust omer_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 was 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------------- ---------------KING Result set 3-------------- customer_name customer_status--------------------------- -------------------Graphix ZONE INC DE none WASHINGTON M Aaaiswaabaaansjaas None

16.3.5. Obtaining Output Parameters

As know, stored procedures can include out or INOUT parameters, which can pass data back to the call ING program. The mysqldb extension does not provide a method to natively retrieve output parameters, but can access their Values through a simple workaround.

Earlier, in Example 16-24, we showed a stored procedure the returned multiple result sets, but also included an output PA Rameter. We supplied a MySQL user variable (prefixed by the @symbol) to receive the value of the parameter. All we need to does now, in Example 16-26, are to retrieve the value of this 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's about INOUT parameters? This is a little trickier, although luckily we don't think you'll useINOUT parameters very much (it ' s usually bet ter 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 this user parameter. Code that wraps the stored procedure-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])

Python calls a MySQL stored procedure to return a result set