python callproc调用 Mysql 储存过程教程实例

一、存储过程（stored procedure）的优缺点

优点：1.多次提取，减少编译时间，2.因为每次提取都需要传入sql语句，如果用存储过程名来调用的话，就减少了访问流量3.增加了重用（可以相较之与（函数对编程的影响））
缺点：1.存储过程将会占用内存空间，并且复杂的过程操作需要一定的cpu 2.存储过程难以调试，如果存储过程太过复杂，不有利于业务逻辑3.存储过程高级，难以维护

DELIMITER $$   // 设置注释
CREATE PROCEDURE CountOrderByStatus(
 IN orderStatus VARCHAR(25),     //需要给定存入使用的参数
 OUT total INT)                 //需要给定输出的参数
BEGIN
 SELECT count(orderNumber)
 INTO total
 FROM orders
 WHERE status = orderStatus;
END$$
DELIMITER ;

这个存储过程接受两个过程参数 一个是输入参数orderStatus，另一个是输出参数 total 最后可以使用Call CountOrderByStatus('23',@title)来进行调用，变量前面要加@.

参数类型

IN 参数名 参数类型 :表示该参数需要在创建存储类型时给出，以便在下面的语句中使用

官方样例:

　　

DELIMITER //
CREATE PROCEDURE GetOfficeByCountry(IN countryName VARCHAR(255))
 BEGIN
 SELECT * 
 FROM offices
 WHERE country = countryName;
 END //
DELIMITER ;

CALL GetOfficeByCountry('USA')//调用

OUT 参数名 参数类型:该参数在执行完所有的参数语句后可以返回.

INOUT 参数名 参数类型: 该参数的不同之处在于它传入的参数需要赋值，并且在callprogram之后他的参数值将会被修改返回给变量值.

DELIMITER $$
CREATE PROCEDURE set_counter(INOUT count INT(4),IN inc INT(4))//传入一个Inout参数和一个in参数
BEGIN
 SET count = count + inc;
END$$
DELIMITER ;

现在尝试调用此储存过程

SET @counter = 1;
CALL set_counter(@counter,1); -- 2  
CALL set_counter(@counter,1); -- 3  //@count的值已经被修改
CALL set_counter(@counter,5); -- 8
SELECT @counter; -- 8

谓词逻辑：

IF（基本）

IF if_expression THEN commands
   [ELSEIF elseif_expression THEN commands]
   [ELSE commands]
   END IF;

While loop

WHILE expression DO
   Statements
END WHILE
　REPEAT
　　　Statements;
　UNTIL expression
　　　END REPEAT

Case

CASE  case_expression
   WHEN when_expression_1 THEN commands
   WHEN when_expression_2 THEN commands
   ...
   ELSE commands
END CASE;

注意事项：能用Case用Case，能简化就简化

重点

如何python调用 callproc 进行调用储存过程

1.创建完整的Mysql数据库连接
2.使用cursor()初始化数据库游标
3.使用游标来调用callproc函数 里面添加 需要传入的变量 例如 callproc(name,args) name="proc_user",args=['21',syh];
3.cursor可以传递出一系列的结果集，使用storeresult来获取一系列的iterator指向结果集
4.用fetchall方法获取结果

callproc 无法直接获得out和INOUT变量 ，但是变量存在server中，可以通过@_procname_n 来获取变量值，可以按照传入参数的位置获取，如第1个 SELECT @_procname_0

from mysql.connector import MySQLConnection, Error
from python_mysql_dbconfig import read_db_config
 
def call_find_all_sp():
    try:
        db_config = read_db_config()
        conn = MySQLConnection(**db_config)
        cursor = conn.cursor()
 
        cursor.callproc('find_all')
 
        # print out the result
        for result in cursor.stored_results():
            print(result.fetchall())
 
    except Error as e:
        print(e)
 
    finally:
        cursor.close()
        conn.close()
 
if __name__ == '__main__':
    call_find_all_sp()

python调用MySql存储过程实例

环境：

1.mysql5.0 或者以上支持存储过程的版本
2.安装MySQL-python，目前支持到2.x

步骤：

一.数据库准备

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  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 INTO `Account` (`id`, `sm_accountName`, `sm_password`, `sm_onlineTime`) VALUES
(1, 'luoshulin', 'asdfsdf', 0),
(2, 'test', '1', 0),
(3, 'adsfasd', 'asdf', 1);

到这里数据库相关内容就准备好了接下去开始写python脚本

二.python脚本

#!/usr/bin/env python
# -*- coding: utf8 -*-
import MySQLdb
import time
import os, sys, string
def CallProc(id,onlinetime):
'''调用存储过程，
输入参数：编号，在线时间，输出：帐号，密码;
使用输出参数方式'''
accname=''
accpwd=''
conn = MySQLdb.connect(host='localhost',user='root',passwd='111111',db='ceshi')
cur =conn.cursor()
cur.callproc('proctest',(id,onlinetime,accname,accpwd))
cur.execute('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):
'''调用存储过程，
输入参数：编号，在线时间，输出：帐号，密码;
使用select返回记录方式'''
accname=''
accpwd=''
conn = MySQLdb.connect(host='localhost',user='root',passwd='111111',db='ceshi')
cur =conn.cursor()
cur.nextset()
cur.execute('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

三.测试

将python脚本保存为 并执行可以看到结果

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

测试使用的是select返回记录的方式，对于使用输出参数返回结果情况也是一样的。