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.