測試環境為 CentOS 8 x86_64 (虛擬機) , MariaDB 10.3.28
在 Python 中要怎麼去執行 MySQL(MariaDB) 的 Stored Procedure , 參考文章 https://pynative.com/python-mysql-execute-stored-procedure/
MySQL(MariaDB)
[root@localhost ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 156
Server version: 10.3.28-MariaDB-log MariaDB Server
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> CREATE DATABASE testdb;
Query OK, 1 row affected (0.000 sec)
MariaDB [(none)]> USE testdb;
Database changed
建立一個簡單 Procedure ,有 2 個輸入參數與 1 個輸出參數.
DROP PROCEDURE IF EXISTS add2param;
DELIMITER //
CREATE PROCEDURE add2param (IN param1 INT , IN param2 INT , OUT AddResult INT)
BEGIN
SET AddResult=param1+param2;
END//
DELIMITER ;
測試一下這個 Procedure ( 參數1+參數2 並回傳到第 3參數 ).
MariaDB [testdb]> CALL add2param(1 , 2 , @ReturnResult);
Query OK, 0 rows affected (0.001 sec)
MariaDB [testdb]> SELECT @ReturnResult;
+---------------+
| @ReturnResult |
+---------------+
| 3 |
+---------------+
1 row in set (0.000 sec)
Python
安裝 mysql.connector 套件.
[root@localhost ~]# pip install mysql.connector
Collecting mysql.connector
Downloading mysql-connector-2.2.9.tar.gz (11.9 MB)
|████████████████████████████████| 11.9 MB 2.7 MB/s
Preparing metadata (setup.py) ... done
Using legacy 'setup.py install' for mysql.connector, since package 'wheel' is not installed.
Installing collected packages: mysql.connector
Running setup.py install for mysql.connector ... done
Successfully installed mysql.connector-2.2.9
WARNING: Running pip as the 'root' user can result in broken permissions and conflicting behaviour with the system package manager. It is recommended to use a virtual environment instead: https://pip.pypa.io/warnings/venv
[root@localhost ~]# python3
Python 3.6.8 (default, Sep 21 2021, 20:17:36)
[GCC 8.4.1 20200928 (Red Hat 8.4.1-1)] on linux
Type "help", "copyright", "credits" or "license" for more information.
導入 mysql.connector 模組.
>>> import mysql.connector
>>> from mysql.connector import Error
建立與資料庫的連結.
>>> connection = mysql.connector.connect(host='localhost',
database='testdb',
user='root',
password='111111')
>>> cursor = connection.cursor()
使用 callproc() method 去呼叫資料庫的 Procedure , Procedure 有 2 個輸入參數與 1 個輸出參數 ( 參數1+參數2 並回傳到第 3參數 ).
>>> args = (5, 6, 0)
>>> cursor.callproc('add2param', args)
(5, 6, 11)
或是直接輸入也可以.
>>> cursor.callproc('add2param', [1,2,0])
(1, 2, 3)
使用完關閉連結.
>>> cursor.close()
>>> connection.close()
