如何用SQLAlchemy将多行多列结果转为单字段字典列表？JSON方案

一、数据库层面直接聚合（推荐） #

不同数据库内置了JSON聚合函数，可直接在查询阶段完成转换，无需应用层额外处理：

PostgreSQL #

使用json_agg()搭配json_build_object()，将单条属性记录转为JSON对象后聚合：

SELECT 
    item,
    json_agg(
        json_build_object(
            'attribute_id', attribute_id,
            'attribute_value', attribute_value,
            'attribute_name', attribute_name
        )
    ) AS attributes
FROM your_table
GROUP BY item;

MySQL 5.7+/MariaDB #

用JSON_ARRAYAGG()和JSON_OBJECT()组合实现聚合：

SELECT 
    item,
    JSON_ARRAYAGG(
        JSON_OBJECT(
            'attribute_id', attribute_id,
            'attribute_value', attribute_value,
            'attribute_name', attribute_name
        )
    ) AS attributes
FROM your_table
GROUP BY item;

二、Python应用层处理（兼容所有数据库） #

如果数据库不支持JSON聚合，或通过SQLAlchemy查询后需手动处理，可在Python中完成分组聚合：

假设SQLAlchemy返回的查询结果为字典列表（或可转为字典的模型实例）：

from collections import defaultdict
import json

# 模拟SQLAlchemy返回的查询结果
query_results = [
    {"item": "A", "attribute_id": "zone", "attribute_value": "A", "attribute_name": "zone_position"},
    {"item": "A", "attribute_id": "type", "attribute_value": "simple", "attribute_name": "type_item"},
    {"item": "A", "attribute_id": "status", "attribute_value": "active", "attribute_name": "state"},
]

# 按item分组，聚合属性字段
aggregated_data = defaultdict(list)
for row in query_results:
    item_key = row.pop("item")
    aggregated_data[item_key].append(row)

# 转换为目标结构，attributes字段转为JSON字符串
final_result = [
    {"item": item, "attributes": json.dumps(attrs)}
    for item, attrs in aggregated_data.items()
]

运行后即可得到符合需求的结构，attributes字段为JSON格式的字典列表字符串。

若使用SQLAlchemy ORM查询模型实例，可通过row._asdict()（针对命名元组结果）或自定义序列化方法，将实例转为字典后再执行上述聚合逻辑。

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