How does Python call MySQL stored procedures?

This article mainly describes the correct operation steps for calling the MySQL stored procedure using Python, as well as the matters worth your attention in actual operations, if you are interested, you can click the following articles to view them.

Environment:

1. MySQL or later versions that support MySQL stored procedures

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

Steps:

I. Database preparation

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 MySQL 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))   
  
  
02 NOT DETERMINISTIC   
  
  
03 CONTAINS SQL   
  
  
04 SQL SECURITY DEFINER   
  
  
05 COMMENT ''   
  
  
06 BEGIN   
  
  
07 select sm_accountName,sm_password   
  
  
08 into o_accname,o_accpwd   
  
  
09 from `tbl_Account` where id=i_id and sm_onlineTime=i_onlinetime limit 1;   
  
  
10 END;   
 
 

3. insert part of data

 
 

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

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

Ii. python script

 
 

  
  
view sourceprint?01 #!/usr/bin/env python   
  
  
02 # -*- coding: utf8 -*-   
  
  
03 import MySQLdb   
  
  
04 import time   
  
  
05 import os, sys, string   
  
  
06 def CallProc(id,onlinetime):   
 
 

07 ''' call the MySQL stored procedure,

08 input parameter: Number, online time, output: account, password;

09 use the output parameter method '''

10 accname =''

11 accpwd =''

12 conn = MySQLdb. connect (host = 'localhost', user = 'root', passwd = '000000', db = 'ceshi ')

13 cur = conn. cursor ()

14 cur. callproc ('proctest', (id, onlinetime, accname, accpwd ))

15 cur.exe cute ('select @ _ proctest_2, @ _ proctest_3 ')

16 data = cur. fetchall ()

17 if data:

18 for rec in data:

19 accname = rec [0]

20 accpwd = rec [1]

21 cur. close ()

22 conn. close ();

23 return accname, accpwd

24 def CallProct (id, onlinetime ):

25 ''' call the MySQL stored procedure,

26 input parameter: Number, online time, output: account, password;

27 use the select return record method '''

28 accname =''

29 accpwd =''

30 conn = MySQLdb. connect (host = 'localhost', user = 'root', passwd = '000000', db = 'ceshi ')

31 cur = conn. cursor ()

32 cur. nextset ()

33 cur.exe cute ('call ptest (% s, % s) ', (id, onlinetime ))

34 data = cur. fetchall ()

35 if data:

36 for rec in data:

37 accname = rec [0]

38 accpwd = rec [1]

39 cur. close ()

40 conn. close ();

41 return accname, accpwd

42 name, pwd = CallProct (1, 0)

43 print name, pwd

Iii. Test

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

View sourceprint? 1 [root @ redhat-dev python] # python pycallproc. py

2 luoshulin asdfsdf

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

The above content is an introduction to calling the MySQL stored procedure in python. I hope you will get some benefits.