Python调用MySQL存储过程输出格式化问题求助

我尝试通过Python脚本调用MySQL存储过程sp_stats来获取美观的输出，但未能成功。

现有Python脚本 #

#!/usr/bin/python3

import getpass
import MySQLdb
DATUM           = sys.argv[1]
def sp_res():
    db = MySQLdb.connect(user='root',passwd=pw,host="127.0.0.1",db="pdi_db")
    cursor = db.cursor()
    res = cursor.callproc('sp_stats', [DATUM])
    results = cursor.fetchall()
    print(results)
    cursor.close()
    db.commit()
    db.close()
pw = getpass.getpass()  
sp_res()

当前输出 #

Some stats
[(datetime.date(2023, 3, 13), Decimal('2215'), Decimal('5'), Decimal('32773'), Decimal('73'), Decimal('2634'), Decimal('1756'), Decimal('1260'), Decimal('1739'), Decimal('393'), Decimal('1896'), Decimal('0'), 44666)]
MySQL connection is closed

期望输出 #

希望去除输出中的数据类型标识和括号，达到类似bash命令调用时的格式（分隔符用逗号或制表符均可）：

mysql -u root -p -sN pdi_db -e " call sp_conn('2023-03-13');"
Enter password: 
2023-03-13  2215    5   32773   73  2634    1756    1260    1739    393 1896    0   44666

对应的MySQL存储过程 #

CREATE DEFINER=`root`@`localhost` PROCEDURE `pdi_db`.`sp_stats`(IN DATUM date)
BEGIN
-- Count number of days when using an older file than curdate   
  declare NUM_DAYS INT DEFAULT 0;
  SELECT DATEDIFF(curdate(),DATUM)
  into NUM_DAYS;
-- Here we start counting number of VIN per day
WITH  
v_data AS 
  ( select report_date ,datediff((curdate() - interval NUM_DAYS day),tem_update) as delta
    from conn_stats_1 cs
    where report_date = DATUM
          AND pdi_date IS NOT NULL
          -- AND tem_update IS NULL
  ) 
SELECT distinct
       report_date,
       sum(CASE WHEN delta IS NULL THEN 1 ELSE 0 END) OVER () AS never,
       round(sum(CASE WHEN delta IS NULL THEN 1 ELSE 0 END) OVER () * 100 / count(*) OVER ()) AS '%',
       sum(CASE WHEN delta >= 0 AND delta <= 7 THEN 1 ELSE 0 END) OVER () AS 0to7days,
       round(sum(CASE WHEN delta >= 0 AND delta <= 7 THEN 1 ELSE 0 END) OVER () * 100 / count(*) OVER ()) AS '%',
       sum(CASE WHEN delta >7  AND delta <=14 THEN 1 ELSE 0 END) OVER () AS 8to14_days, 
       sum(CASE WHEN delta >14 AND delta <=21 THEN 1 ELSE 0 END) OVER () AS 15to21_day, 
       sum(CASE WHEN delta >21 AND delta <=28 THEN 1 ELSE 0 END) OVER () AS 22to28_day,
       sum(CASE WHEN delta >28 AND delta <=50 THEN 1 ELSE 0 END) OVER () AS 29to50_day,
       sum(CASE WHEN delta >50 AND delta <=60 THEN 1 ELSE 0 END) OVER () AS 51to60_day,
       sum(CASE WHEN delta >60 THEN 1 ELSE 0 END) OVER () AS 60_or_more,
       sum(CASE WHEN delta <0  THEN 1 ELSE 0 END) OVER () AS other,
       count(*) over() as Total
FROM v_data;
-- select NUM_DAYS;
END

解决方案 #

修改后的Python脚本 #

#!/usr/bin/python3

import getpass
import sys
import MySQLdb

DATUM = sys.argv[1]

def sp_res():
    db = MySQLdb.connect(user='root', passwd=pw, host="127.0.0.1", db="pdi_db")
    cursor = db.cursor()
    cursor.callproc('sp_stats', [DATUM])
    
    # 处理存储过程可能返回的多个结果集，获取有效查询结果
    results = None
    while True:
        try:
            results = cursor.fetchall()
            if results:
                break
        except MySQLdb.ProgrammingError:
            break
    
    # 格式化输出：将每行元素转为字符串，用制表符分隔
    if results:
        for row in results:
            print('\t'.join(str(item) for item in row))
    
    cursor.close()
    db.close()

pw = getpass.getpass()  
sp_res()

关键修改说明 #

1. 导入sys模块：原脚本使用了sys.argv[1]但未导入sys，会导致运行报错，添加import sys解决。
2. 处理结果集：存储过程可能返回多个结果集（比如中间变量的查询），通过循环获取直到拿到有效数据。
3. 格式化输出：用str(item)自动转换datetime.date和Decimal类型为友好字符串，再用'\t'.join()生成制表符分隔的格式；如果需要逗号分隔，替换为','.join(...)即可。
4. 移除不必要的commit：该存储过程是查询操作，无需提交事务，删除db.commit()。

内容的提问来源于stack exchange，提问作者Claes