How Python calls MySql Stored Procedure

The emergence of the Python programming language has brought great benefits to developers. For beginners, the application of mastering this language is actually relatively simple. Here we will first introduce you to a common application technique, which is the implementation method for calling the MySql stored procedure in Python.

• Classic introduction to Python instance applications
• Features of the Python ZipFile Module
• Interpretation of basic Python set applications
• Introduction to the basic application of the Python packaging method
• Basic concepts of third-party Python libraries

Python calls the MySql Stored Procedure configuration environment:

1. Versions of mysql5.0 or above that support stored procedures

2. Install MySQL-python, which currently supports 2.x

Step 1. Prepare a database

1. Create a table

 
 

  
  
view sourceprint?1 CREATE TABLE `Account` (   
  
  
`id` BIGINT(20) NOT NULL AUTO_INCREMENT,   
  
  
`sm_accountName` VARCHAR(100) COLLATE gbk_chinese_ci NOT NULL DEFAULT '',   
  
  
`sm_password` TEXT COLLATE gbk_chinese_ci NOT NULL,   
  
  
`sm_onlineTime` BIGINT(20) UNSIGNED NOT NULL DEFAULT '0',   
  
  
PRIMARY KEY (`id`),   
  
  
UNIQUE KEY `accountNameIndex` (`sm_accountName`)   
  
  
)ENGINE=InnoDB  
 
 

2. Create a stored procedure

 
 

  
  
view sourceprint?01 CREATE PROCEDURE `proctest`
(IN i_id BIGINT, IN i_onlinetime BIGINT, OUT o_accname 
VARCHAR(30), OUT o_accpwd VARCHAR(50))   
  
  
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 part of data

 
 

  
  
view sourceprint?1 INSERT INTO `Account` (`id`, 
`sm_accountName`, `sm_password`, `sm_onlineTime`) VALUES   
  
  
(1, 'luoshulin', 'asdfsdf', 0),   
  
  
(2, 'test', '1', 0),   
  
  
(3, 'adsfasd', 'asdf', 1);  
 
 

The database-related content is ready. Next, write the python script.

Step 2. Python script

 
 

  
  
View sourceprint? 01 #! /Usr/bin/env python
  
  
#-*-Coding: utf8 -*-
  
  
Import MySQLdb
  
  
Import time
  
  
Import OS, sys, string
  
  
Def CallProc (id, onlinetime ):
  
  
''' Call the stored procedure,
  
  
Input parameter: Number, online time, output: account, password;
  
  
Use the output parameter method '''
  
  
Accname =''
  
  
Accpwd =''
  
  
Conn = MySQLdb. connect (host = 'localhost', user = 'root ',
Passwd = '20180101', db = 'ceshi ')
  
  
Cur = conn. cursor ()
  
  
Cur. callproc ('proctest', (id, onlinetime, accname, accpwd ))
  
  
Cur.exe cute ('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 the stored procedure,
  
  
Input parameter: Number, online time, output: account, password;
  
  
Use the select return record method '''
  
  
Accname =''
  
  
Accpwd =''
  
  
Conn = MySQLdb. connect (host = 'localhost', user = 'root ',
Passwd = '000000', db = 'ceshi') cur = conn. cursor ()
  
  
Cur. nextset ()
  
  
Cur.exe cute ('call ptest (% s, % s) ', (id, onlinetime ))
  
  
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
 
 

Step 3. Test

Save the python script and execute it to view the result.

 
 

  
  
view sourceprint?1 [root@redhat-dev python]# python pycallproc.py   
  
  
luoshulin asdfsdf  
 
 

The test uses the select return record method. The results returned by the output parameters are the same.