python 调用mysql存储过程返回结果集

存储过程：

delimiter | create procedure get_product_info(in imid int(10),int iuser varchar(20))begin select * from tb_test where mid = imid and user = iuser;end; |delimiter ;

python调用：

import mysql.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() 返回所有查询#result 获取一次查询for result in cursor.stored_results():#rs返回一次查询结果rs =result.fetchall() except Exception as e:print(e)

Refrence:

16.3. Using Stored Programs with MySQLdb

The techniques for calling stored programs withMySQLdbdiffer 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 programsthecallproccursor method. Thecallprocmethod was not implemented inMySQLdbas we went to press, although the maintainer ofMySQLdb, 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 wait forcallprocto 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

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

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

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 aSELECTstatement. 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

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 theCLIENT.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 use thenextset( )method of thecursorobject 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

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

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 thenextset( )method with thecursor.descriptionproperty described in the "Getting Metadata" section earlier in this chapter. Thenextset( )method returns aNoneobject if there are no further result sets, so we can keep callingnextset( )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

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:

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_idprovided to the procedure.

Example 16-24. Stored procedure with dynamic result sets

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 in1to produce a report foremployee_id=1; the@out_customer_countvariable 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

16.3.5. Obtaining Output Parameters

As you know, stored procedures can includeOUTorINOUTparameters, which can pass data back to the calling program. TheMySQLdbextension 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 simpleSELECT.

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

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

To handle anINOUTparameter, 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

如果觉得《python 调用mysql存储过程返回结果集》对你有帮助，请点赞、收藏，并留下你的观点哦！