Python调用pymysql组件MySQL存储过程无法得到返回参数

Python调用MySQL存储过程的返回参数

1. 问题描述

MySQL的存储过程如下，入口参数两个，出口参数1个：

DELIMITER $$
create procedure pro_test_args(in a int ,in b int ,out t_num int)
begin
	
	set t_num = a * b;
		
END $$

通过Python调用mysql的存储过程，得到返回参数。

import pymysql

global_config = {
    "host": "127.0.0.1",
    "user": "root",
    "password": "mysqlpwd",
    "database": "mysqldb",
    "use_unicode": "True",
    "charset": "utf8"
}
global_db = pymysql.connect(**global_config)
cursor = global_db.cursor()
para =[5,6,0]
result = cursor.callproc('pro_test_args',para)
print(result[2])
print (result)
print (para)
sql_string = "select * from tb_student limit 5;"
record = cursor.execute(sql_string)

print(record)
res = cursor.fetchall()

for r in res :
    print(r)
    
cursor.close()    

执行结果：
result返回参数是list，入口参数para也是list ,结果不正确，应该是30 。

0
[5, 6, 0]
[5, 6, 0]
5
('HTMXJHRSZS', 63, 3, datetime.date(2023, 10, 14))
('GTMWQCYWDP', 98, 3, datetime.date(2023, 11, 2))
('ABCVACMCRT', 74, 3, datetime.date(2023, 8, 3))
('UQUUSYZIXJ', 87, 2, datetime.date(2023, 11, 3))
('ZYPAUIHBZI', 83, 3, datetime.date(2023, 7, 15))

pymysql的版本：

print(pymysql.__version__)
1.0.3

经过反复测试，pymysql无法调用存储过程的返回值，一直以为程序上，或者mysql上有问题，就用了最简单的存储过程，还是不能正确的取得返回值。

注意：
pymysql无法调用存储过程的返回值！！！

2.解决方法

更换Python访问mysql的组件，mysql-connector-python
用pip 安装如下：

# pip install mysql-connector-python
Looking in indexes: https://pypi.douban.com/simple/
Collecting mysql-connector-python
  Downloading https://mirrors.cloud.tencent.com/pypi/packages/8c/ad/4f906a7460821faa7a5ed4daa93f07362e45c9c9966c7100faf5354881f0/mysql_connector_python-8.2.0-cp38-cp38-manylinux_2_17_x86_64.whl (31.6 MB)
     ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 31.6/31.6 MB 814.7 kB/s eta 0:00:00
Collecting protobuf<=4.21.12,>=4.21.1
  Downloading https://mirrors.cloud.tencent.com/pypi/packages/e7/a2/3273c05fc5d959fa90de6453ebd6d45c6d4fab3ec212d631625ea5780921/protobuf-4.21.12-cp37-abi3-manylinux2014_x86_64.whl (409 kB)
     ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 409.8/409.8 kB 850.9 kB/s eta 0:00:00
Installing collected packages: protobuf, mysql-connector-python
  Attempting uninstall: protobuf
    Found existing installation: protobuf 4.22.3
    Uninstalling protobuf-4.22.3:
      Successfully uninstalled protobuf-4.22.3
Successfully installed mysql-connector-python-8.2.0 protobuf-4.21.12
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
#                       

Python调用程序一样：

from mysql.connector import MySQLConnection, Error

conn = MySQLConnection(**global_config)
cursor = conn.cursor()

para =[5,3,0]
result = cursor.callproc('pro_test_args',para)


print(result[2])
print(result)
print(para)

执行结果如下：
result 调用返回参数是tuple，入口参数是list ，结果正确

15
(5, 3, 15)
[5, 3, 0]

pymysql调用存储过程的返回值，存在bug！！
一下午的时间验证，比较坑。