Python calls the MySql Stored Procedure

Environment:

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

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

Steps:

I. Database preparation

1. Create a table

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

CREATE  PROCEDURE `proctest`(IN i_id BIGINT, IN i_onlinetime BIGINT, OUT o_accname VARCHAR(30), OUT o_accpwd VARCHAR(50))NOT DETERMINISTICCONTAINS SQLSQL SECURITY DEFINERCOMMENT ''BEGINselect sm_accountName,sm_passwordinto o_accname,o_accpwdfrom `tbl_Account` where id=i_id and sm_onlineTime=i_onlinetime limit 1;END;

3. insert part of data

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.

Ii. python script

#! /Usr/bin/env python #-*-coding: utf8-*-import MySQLdbimport timeimport OS, sys, stringdef CallProc (id, onlinetime): ''' calls the stored procedure, input parameter: Number, online time, output: account, password; use output parameter method '''accname = ''accpwd = '''conn = MySQLdb. connect (host = 'localhost', user = 'root', passwd = '000000', db = 'ceshi') cur into 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, accpwddef 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 implements 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, accpwdname, pwd = CallProct (1, 0) print name, pwd

Iii. Test

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

[root@redhat-dev python]# python pycallproc.pyluoshulin asdfsdf

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