在web api中以json格式显示sql查询结果

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

app = Flask(__name__)

@app.route('/api/query', methods=['GET'])
def query_data():
    # 连接数据库
    cnx = mysql.connector.connect(user='username', password='password',
                                  host='localhost', database='database_name')
    cursor = cnx.cursor()

    # 执行SQL查询
    query = "SELECT * FROM table_name"
    cursor.execute(query)

    # 获取查询结果
    result = cursor.fetchall()

    # 将结果转换为JSON格式
    columns = [column[0] for column in cursor.description]
    data = [dict(zip(columns, row)) for row in result]
    json_data = json.dumps(data)

    # 返回JSON响应
    response = app.response_class(
        response=json_data,
        status=200,
        mimetype='application/json'
    )
    return response

if __name__ == '__main__':
    app.run()