Shulou(Shulou.com)06/01 Report--

MySQL stored procedures of Python full Stack path Series

A stored procedure is a collection of SQL statements, and when the stored procedure is called actively, the internal SQL statements are executed logically.

Parameters received by the stored procedure

Parameter description in is only used for incoming parameters. Out is only used for return values. Inout can be passed in or used as a return value.

Create a stored procedure

Create a simple stored procedure

-- modify the Terminator of SQL statement to% delimiter%-- delete DROP PROCEDURE IF EXISTS proc_p1% CREATE PROCEDURE proc_p1 () first to create this stored procedure-- start BEGIN-- SQL statement block select * from color;-- end END%-- change the Terminator of SQL statement to; delimiter

Calling stored procedures through call

Call proc_p1 ()

Output as

+-+-+ | nid | title | +-+-+ | 1 | red | | 2 | yellow | +-+-+ 2 rows in set (0.00 sec) Query OK, 0 rows affected (0.01 sec)

Delete stored procedure

DROP PROCEDURE proc_p1; instance

Create a stored procedure and receive a parameter, which is the number of data displayed

Delimiter% DROP PROCEDURE IF EXISTS proc_p1% create PROCEDURE proc_p1 (--i1 is the passed parameter, the passed data type must be int type in i1 int) BEGIN-defines two local variables D1 and D2, both data types are int, D1 default value is empty, D2 default value is 1 DECLARE D1 int; DECLARE D2 int DEFAULT 1 -- the value of D1 is equal to the passed-in i1 plus the value of the defined local variable D2 SET D1 = i1 + D2;-- find the data in the person_ info table where nid is greater than D1 SELECT * FROM person_info WHERE nid > D1 | end% delimiter

Query, enter the defined parameters in parentheses

CALL proc_p1 (4)

Display the results

+-+ | nid | name | email | phone | part_nid | position | caption | color_nid | +- -+-+ | 6 | w | as@anshengme.com | 13800138000 | 5 | Python | NULL | NULL | | 9 | | aa | a@ansheng.me | 13800138000 | 3 | DBA | NULL | 2 | | 10 | b | b.ansheng.me | 13800138000 | 3 | DBA | NULL | 1 | +-| -+ 3 rows in set (0.00 sec) Query OK 0 rows affected (0.01 sec)

This time, we output all the data whose nid is greater than 5, and the value passed in is 4, so we let 4% 1 internally, so it is the data greater than 5.

Delimiter% DROP PROCEDURE IF EXISTS proc_p1% create PROCEDURE proc_p1 (--received three parameters of type int in i1 int, inout ii int, out i2 int) BEGIN-defines a local variable d2, default value of 3, data type of int DECLARE d2 int DEFAULT 3;-- ii = ii + 1 set ii = ii + 1 -- if incoming i1 equals 1 IF i1 = 1 THEN-- i2 = 100 + d2 set i2 = 100 + D2;-- if incoming i1 equals 2 ELSEIF i1 = 2 THEN-- i2 = 200 + D2 set i2 = 200 + D2;-- otherwise ELSE-- i2 = 1000 + D2 set i2 = 1000 + D2; END IF;END% delimiter

View data

Set @ o = 5 th call proc_p1 (1); SELECT @ omeme

Results displayed

+-+-+ | @ o | @ u | +-+-+ | 6 | 103 | +-+-+ 1 row in set (0.00 sec)

Using the pymysql module to operate stored procedures

The Python code is:

Import pymysqlconn = pymysql.connect (host= "127.0.0.1", port=3306, user='root', passwd='as', db= "dbname") cursor= conn.cursor (cursor=pymysql.cursors.DictCursor) # execute stored procedure row = cursor.callproc ("proc_p2", (1) proc_p2 2) # query result of stored procedure selc = cursor.fetchall () print (selc) # get stored procedure return effect_row = cursor.execute ('select @ _ proc_p2_0, @ _ proc_p2_1 @ _ proc_p2_2') # take the stored procedure return value result = cursor.fetchone () print (result) conn.commit () cursor.close () conn.close ()

Results displayed

C:\ Python\ Python35\ python.exe Pycharmprojects _ stored procedure. Py [{'nid': 1,' name': 'man1'}, {' nid': 2, 'name':' man2'}, {'nid': 3,' name': 'man3'}] {' @ _ proc_p2_1': 3,'@ _ proc_p2_0': 1,'@ _ proc_p2_2': 103} Process finished with exit code 0

Stored procedures use into

Into actually takes the execution result of one select as a parameter of another select, such as the following example:

Delimiter% DROP PROCEDURE IF EXISTS proc_p2% CREATE PROCEDURE proc_p2 () BEGIN-- defines a local variable n of type int DECLARE n int;-- gets the data of color_nid = 2 and assigns it to n SELECT color_nid into n FROM person_info where color_nid = 2;-- outputs the data of nid = n SELECT * from color WHERE nid = nintend% delimiter

Execution

Call proc_p2 ()

Result

+-+-+ | nid | title | +-+-+ | 2 | yellow | +-+-+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.01 sec)

# Python full Stack Road

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.