如何让调用MySQL存储过程的Python API返回键值对格式数据

开发连接MySQL的Python API时，调用存储过程后仅返回纯列表结果，需要将返回数据改为key:value的键值对格式。

原实现代码 #

from flask import Flask, jsonify
import mysql.connector
import dbConfig

app = Flask(__name__)

# Ruta para el método GET que llama al Stored Procedure
@app.route('/api/ejemplo', methods=['GET'])
def get_ejemplo():
    try:
        # Conexión a la base de datos MySQL
        connection = mysql.connector.connect(**dbConfig.db_config)
        cursor = connection.cursor()

        # Llamada al Stored Procedure
        results =cursor.callproc('sps_reservas')

        # Obtención de los nombres de las columnas
        column_names = [desc[0] for desc in cursor.column_names]

        # Obtención de los resultados del Stored Procedure
        results = []
        for result in cursor.stored_results():
            results = result.fetchall()
            
        # Cierre del cursor y la conexión a la base de datos
        cursor.close()
        connection.close()

        # Conversión de los resultados a formato JSON y respuesta HTTP
        return jsonify({'data':results})

    except Exception as e:
        return jsonify({'error': str(e)})

if __name__ == '__main__':
    app.run(debug=True, port=5000)

当前返回结果 #

{
    "data": [
        [
            1,
            1,
            1,
            "Thu, 06 Jul 2023 17:31:39 GMT",
            "Mon, 10 Jul 2023 00:00:00 GMT",
            "Fri, 14 Jul 2023 00:00:00 GMT"
        ],
        [
            2,
            1,
            2,
            "Fri, 07 Jul 2023 11:49:33 GMT",
            "Sat, 15 Jul 2023 00:00:00 GMT",
            "Mon, 17 Jul 2023 00:00:00 GMT"
        ]
    ]
}

期望返回格式 #

{
    "data": [
        {
            "id": 1,
            "idCliente": 1,
            "idHabitacion": 1,
            "fechaReserva": "Thu, 06 Jul 2023 17:31:39 GMT",
            "fechaEntrada": "Mon, 10 Jul 2023 00:00:00 GMT",
            "fechaSalida": "Fri, 14 Jul 2023 00:00:00 GMT"
        },
        {
            "id": 2,
            "idCliente": 1,
            "idHabitacion": 2,
            "fechaReserva": "Fri, 07 Jul 2023 11:49:33 GMT",
            "fechaEntrada": "Sat, 15 Jul 2023 00:00:00 GMT",
            "fechaSalida": "Mon, 17 Jul 2023 00:00:00 GMT"
        }
    ]
}

解决方案 #

核心问题是原代码未将查询结果与列名映射，且获取列名的时机错误（cursor.column_names无法正确获取存储过程返回的列名，需从每个stored_result对象中获取）。修改后的代码如下：

from flask import Flask, jsonify
import mysql.connector
import dbConfig

app = Flask(__name__)

@app.route('/api/ejemplo', methods=['GET'])
def get_ejemplo():
    try:
        connection = mysql.connector.connect(**dbConfig.db_config)
        cursor = connection.cursor()

        cursor.callproc('sps_reservas')
        
        formatted_results = []
        # 遍历存储过程返回的结果集
        for result in cursor.stored_results():
            # 获取当前结果集的列名
            column_names = result.column_names
            # 获取所有行数据
            rows = result.fetchall()
            # 将每行数据与列名配对，生成字典
            formatted_results = [dict(zip(column_names, row)) for row in rows]

        cursor.close()
        connection.close()

        return jsonify({'data': formatted_results})

    except Exception as e:
        return jsonify({'error': str(e)})

if __name__ == '__main__':
    app.run(debug=True, port=5000)

修改说明 #

1. 移除无效列名获取代码：删除原代码中column_names = [desc[0] for desc in cursor.column_names]，改为从stored_result对象中获取列名。
2. 结果映射为字典：使用dict(zip(column_names, row))将每行元组数据与列名配对，生成键值对格式的字典。
3. 整理结果集：将转换后的字典列表作为最终返回数据。

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