1. Introduction

Hello, I’m Angol!

I’ve written a series of articles about data handling in Python

Most comprehensive summary | about Python data processing buckets (Mysql)

Most comprehensive summary | about Python data processing buckets (Sqlite)

Most comprehensive summary | about Python data processing buckets (mongo)

Most comprehensive summary | about Python data processing buckets (Redis)

Most comprehensive summary | about Python data processing buckets (Memcached)

If your project involves complex SQL processing, you can wrap these operations into “stored procedures” that expose incoming and outgoing parameters for direct invocation

This article will talk about how to execute stored procedures using Python

2. Stored procedures

Stored Procedure

Think of it as an interface that encapsulates common operations and can be invoked directly

Common operations of stored procedures are as follows:

2-1 Managing stored procedures

  • create

  • The query

  • delete

Xag delimiter $create Procedure xag() begin... xag delimiter $create procedure xag() begin Select 'name' from mysql. Proc WHERE db = 'xag' and 'type' = 'PROCEDURE'; # 2.2 Querying stored procedure status information show procedure status; DROP PROCEDURE IF EXISTS xAG;Copy the code

Among them

Use “Create Procedure Stored procedure name” to create a stored procedure, and then write the specific operation logic between begin and end

2-2 Definition and assignment of variables

Use the keyword “declare” to define a variable

For example: define a variable name with type string # Default value is null... declare name varchar(255) default null; .Copy the code

There are two ways to assign a value to a variable: set syntax and select into syntax

Among them

  • The set syntax lets you set the value of a variable through an expression

  • The SELECT into syntax is to query a database table and set the query result into a variable

Declare name varchar(255) default null; Set name = 'xag'; Select name into name from name_table limit 1 select name into name from name_table limit 1;Copy the code

2-3

For example, judging grade by age (if statement)

. declare age int default 23; declare grade varchar(255) default null; If age <=5 then set grade = 'kindergarten '; Elseif age >= 6 and age < 12 then set grade = '1 '; Elseif age >=12 and age < 15 THEN set grade = '1 '; Elseif age >=15 and age < 18 then set grade = '1 '; Elseif age >=18 then set grade = 'other '; end if; .Copy the code

While 2-4 cycles

For example, calculate the sum of values 1-10 and set it to the variable total

. Declare total int default 0; Declare end_number int default 10; Declare temp int default 0; Set total = total + temp; set temp = temp + 1; end while; .Copy the code

2-5 Input and output parameters

In order to write a stored procedure that is useful, we need to set the outgoing and incoming parameters in common stored procedures

The syntax is as follows:

Create procedure proce_name([in/out/inout])Copy the code

Among them

  • The default value passed in is the incoming parameter, which is in

  • Out represents an out parameter and is returned as a return value

  • If it is set to inout, it can be used as either an out or in parameter

3. Practice

Using Python to call stored procedures is very convenient

First, let’s write a stored procedure

For example, HERE I define a stored procedure that passes in two incoming parameters and one outgoing parameter, and returns the product of the two incoming parameters as the outgoing parameter

Delimiter $create procedure num_multi(in num1 int,in num2 int,out multiply_result int) begin Set multiply_result = num1 * num2; end $Copy the code

The call test is then performed in the database

Use the keyword “call” to invoke the stored procedure and use SELECT to see the returned value

Call num_multi(1,3,@multiply_result); select @multiply_result;Copy the code

Next, the database configuration information is used to create the connection and cursor objects

Import Pymysql PY_MYSQL_CONN_DICT = {"host": '127.0.0.1', "port": 3306, "user": 'root', "passwd": 'root', "db": Connect (**PY_MYSQL_CONN_DICT) # db_cursor = pymysql.connect(**PY_MYSQL_CONN_DICT db_conn.cursor(cursor=pymysql.cursors.DictCursor)Copy the code

Finally, the function “callProc” is used to call the stored procedure name and all parameters to get the return value

After executing the stored procedure, you need to obtain the input and output parameters from the execute function of the cursor object

Db_cursor.execute ('SELECT @_num_multi_0, @_num_multi_1, @_num_multi_1, @_num_multi_1, @_num_multi_1, @_num_multi_1 ') Output_result = db_cursor.fetchone()['@_num_multi_2'] # print(output_result)Copy the code

Note that if the stored procedure involves updating, adding, and so on, you need to explicitly call the commit() function before it is actually committed to the database

4. The last

The above lists only the common syntax for stored procedures, including case condition branching, repeats, and loops, which you can learn by extension

If you think the article is good, please like, share, leave a message, because this will be my strongest power to continue to output more high-quality articles!