Python – 執行 MySQL Stored Procedure

測試環境為 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

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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()
True
>>> connection.close()