如何从单个表生成嵌套JSON响应

import json
from flask import Flask

app = Flask(__name__)

@app.route('/nested_json')
def generate_nested_json():
    # 查询数据库获取数据
    # 这里假设从名为"users"的表中获取用户信息，从名为"orders"的表中获取订单信息
    # 使用JOIN语句将两个表连接在一起
    query = "SELECT users.id, users.name, orders.order_id, orders.product_name FROM users JOIN orders ON users.id = orders.user_id"
    # 执行查询并获取结果
    # ...

    # 将查询结果转换为嵌套的JSON结构
    nested_json = {}
    for row in query_result:
        user_id = row['id']
        user_name = row['name']
        order_id = row['order_id']
        product_name = row['product_name']

        # 构建嵌套的JSON结构
        if user_id not in nested_json:
            nested_json[user_id] = {
                'name': user_name,
                'orders': []
            }
        nested_json[user_id]['orders'].append({
            'order_id': order_id,
            'product_name': product_name
        })

    # 将嵌套的JSON结构转换为字符串
    json_response = json.dumps(nested_json)

    return json_response

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