Python callproc invoke Mysql stored Procedure Tutorial instance

First, the advantages and disadvantages of the stored procedure (stored procedure)

Advantages: 1. Multiple extraction, reduced compile time, 2. Because each fetch requires an incoming SQL statement, and if invoked with the stored procedure name, the access traffic is reduced by 3. Increased reuse (which can be compared to the effects of functions on programming)
Disadvantages: 1. Stored procedures will occupy memory space, and complex process operations require a certain CPU 2. Stored procedures are difficult to debug, and if the stored procedure is too complex, it is not conducive to business logic 3. Advanced stored procedures, difficult to maintain

DELIMITER $$//Set Note
CREATE PROCEDURE countorderbystatus (
in Orderstatus VARCHAR (25),//require a given deposit using the parameters
OU T total INT)//The parameter that requires a given output is
BEGIN
SELECT count (ordernumber) into total from
orders
W Here status = Orderstatus;
end$$
DELIMITER;

This stored procedure accepts two process parameters, one is the input parameter orderstatus, the other is the output parameter total can finally be invoked using call Countorderbystatus (' d ', @title), and the variable is preceded by @.

Parameter type

In Parameter name parameter type: Indicates that the parameter needs to be given when creating a storage type to use in the following statement

Official examples:

　　

DELIMITER//
CREATE PROCEDURE getofficebycountry (in CountryName VARCHAR (255))
BEGIN
SELECT *
out ICES
WHERE country = countryname;
End//
DELIMITER;

Call Getofficebycountry (' USA ')/

Out parameter name parameter type: This parameter can be returned after all parameter statements have been executed.

INOUT parameter Name argument type: The argument is different in that the parameters it passes in need to be assigned, and after callprogram his parameter values will be modified to return to the variable value.

DELIMITER $$
CREATE PROCEDURE set_counter (INOUT count Int (4), in Inc INT (4))//pass in a INOUT parameter and an in parameter
BEGIN
set Count = Count + Inc;
end$$
DELIMITER;

Now try calling this stored procedure

SET @counter = 1;
Call Set_counter (@counter, 1);  --2 call
Set_counter (@counter, 1);--The value of 3//@count has been modified call
Set_counter (@counter, 5);--8
SELECT @counter; --8

Predicate logic:

IF (Basic)

If if_expression THEN commands
[ELSEIF elseif_expression THEN commands]
[ELSE commands] End
if;

While loop

While expression does
statements end while
REPEAT
statements;
UNTIL expression End
REPEAT

Case

Case Case_expression when
when_expression_1 THEN commands when
when_expression_2 THEN commands
... 
   ELSE commands end case
;

Note: Can use case with case, can simplify to simplify

Focus

How Python invokes Callproc to invoke the stored procedure

1. Create a complete MySQL database connection
2. Initialize database cursors using cursor ()
3. Use cursors to invoke the CALLPROC function to add variables that need to be passed in such as Callproc (Name,args) name= "Proc_user", args=[', syh];
3.cursor can pass a series of result sets, use Storeresult to get a series of iterator point to result set
4. Using the Fetchall method to obtain the result

Callproc cannot get the out and inout variables directly, but the variables exist in the server and can be obtained by @_procname_n to get the value of the variable, which can be obtained at the position of the incoming parameter, as in the 1th SELECT @_procname_0

From mysql.connector import mysqlconnection, error from python_mysql_dbconfig  Import read_db_config   DEF CALL_FIND_ALL_SP ():     try:          db_config = read_db_config ()          conn = mysqlconnection (**db_config)         cursor
 = conn.cursor ()           cursor.callproc (' Find_all ')           # print out the result          for result in cursor.stored_results ():              print (Result.fetchall ())        Except error as e:         print (e)        finalLy:         cursor.close ()          conn.close ()   if __name__ ==  ' __main__ ':     call_find_all_sp ()

Python invokes MySQL stored procedure instance

Environment:

1.mysql5.0 or above to support a stored procedure version
2. Install Mysql-python, currently support to 2.x

Steps:

I. Database preparation

1. Create a table

CREATE TABLE ' account ' (
' id ' BIGINT "not NULL auto_increment,
' Sm_accountname ' VARCHAR (MB) COLLATE Chinese_ci NOT null DEFAULT ',
' Sm_password ' TEXT COLLATE gbk_chinese_ci not NULL,
' sm_onlinetime ' BIGINT UN Signed not NULL DEFAULT ' 0 ',
PRIMARY key (' id '),
UNIQUE key ' Accountnameindex ' (' sm_accountname ')
engine= InnoDB

2. Establish a stored procedure

CREATE PROCEDURE ' Proctest ' (in i_id BIGINT, at I_onlinetime BIGINT, out O_accname VARCHAR (a), out O_accpwd VARCHAR (50)) C0/>not deterministic
CONTAINS SQL
SQL security definer
COMMENT '
BEGIN
Select Sm_accountname, Sm_password into
o_accname,o_accpwd from
' Tbl_account ' where id=i_id and sm_onlinetime=i_onlinetime limit 1;
End;

3. Insert partial data

INSERT into ' account ' (' id ', ' sm_accountname ', ' Sm_password ', ' sm_onlinetime ') VALUES
(1, ' Luoshulin ', ' Asdfsdf ', 0) ,
(2, ' Test ', ' 1 ', 0),
(3, ' adsfasd ', ' asdf ', 1);

Here, the database is ready to go and start writing Python scripts.

Two. Python scripts

#!/usr/bin/env python # -*- coding: utf8 -*-import mysqldb import time Import
 os, sys, string Def callproc (id,onlinetime): "" Call stored procedures, input parameters: number, online time, output: account number, password; Use output parameter mode ' accname= ' accpwd= ' conn = mysqldb.connect (host= ' localhost ', user= ' root ', passwd= ' 111111 ', db = ' Ceshi ') cur =conn.cursor () cur.callproc (' Proctest ', (id,onlinetime,accname,accpwd)) Cur.execute (' Select
 @_proctest_2,@_proctest_3 ') Data=cur.fetchall () if data:for rec in data:accname=rec[0]
Accpwd=rec[1] Cur.close () conn.close ();
Return accname,accpwd def callproct (id,onlinetime): "" Call stored procedures, input parameters: number, online time, output: account number, password; Use Select to return recording mode ' accname= ' accpwd= ' conn = mysqldb.connect (host= ' localhost ', user= ' root ', passwd= ')
111111 ', db= ' Ceshi ') cur =conn.cursor () Cur.nextset (Cur.execute) call ptest ("%s,%s") Data=cur.fetchall () if data:for rec in data:accname=rec[0] Accpwd=rec[1] Cur.close () conn.close (); Return accname,accpwd name,pwd=callproct (1,0) print name,pwd

three. Test

Save Python script as and execute can see results

[Root@redhat-dev python]# python The pycallproc.py
Luoshulin asdfsdf

Test uses the way that select returns records, as is the case for returning results using output parameters.