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); -- 8SELECT @counter; -- 8

謂詞邏輯：

IF（基本）

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

While loop

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

Case

CASE  case_expression   WHEN when_expression_1 THEN commands   WHEN when_expression_2 THEN commands   ...   ELSE commandsEND 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, Errorfrom 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 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 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 MySQLdbimport timeimport os, sys, stringdef 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,accpwddef 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,accpwdname,pwd=CallProct(1,0)print name,pwd

三.測試

將python指令碼儲存為 並執行可以看到結果

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

測試使用的是select返回記錄的方式，對於使用輸出參數返回結果情況也是一樣的。