How Python calls MySql Stored Procedure

Source: Internet
Author: User

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

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

2. Create a stored procedure

 
 
  1. view sourceprint?01 CREATE PROCEDURE `proctest`
    (IN i_id BIGINT, IN i_onlinetime BIGINT, OUT o_accname 
    VARCHAR(30), OUT o_accpwd VARCHAR(50))   
  2. NOT DETERMINISTIC   
  3. CONTAINS SQL   
  4. SQL SECURITY DEFINER   
  5. COMMENT ''   
  6. BEGIN   
  7. select sm_accountName,sm_password   
  8. into o_accname,o_accpwd   
  9. from `tbl_Account` where id=i_id and sm_onlineTime=
    i_onlinetime limit 1;   
  10. END;  

3. insert part of data

 
 
  1. 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.

Step 2. Python script

 
 
  1. View sourceprint? 01 #! /Usr/bin/env python
  2. #-*-Coding: utf8 -*-
  3. Import MySQLdb
  4. Import time
  5. Import OS, sys, string
  6. Def CallProc (id, onlinetime ):
  7. ''' Call the stored procedure,
  8. Input parameter: Number, online time, output: account, password;
  9. Use the output parameter method '''
  10. Accname =''
  11. Accpwd =''
  12. Conn = MySQLdb. connect (host = 'localhost', user = 'root ',
    Passwd = '20180101', 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 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') cur = conn. cursor ()
  31. Cur. nextset ()
  32. Cur.exe cute ('call ptest (% s, % s) ', (id, onlinetime ))
  33. Data = cur. fetchall ()
  34. If data:
  35. For rec in data:
  36. Accname = rec [0]
  37. Accpwd = rec [1]
  38. Cur. close ()
  39. Conn. close ();
  40. Return accname, accpwd
  41. Name, pwd = CallProct (1, 0)
  42. Print name, pwd

Step 3. Test

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

 
 
  1. 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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.