如何将PostgreSQL联表查询结果转换为Python字典列表？

执行PostgreSQL三表联查后得到如下结构的结果集：

+-----------+--------------+---------------+-----------------+-----------------------+
| dish_name | feature_name | feature_value | ingredient_name | ingredient_proportion |
+-----------+--------------+---------------+-----------------+-----------------------+
| dish_A    | feature_1    | 1.5           | ingredient_1    | 0.3                   |
| dish_A    | feature_1    | 1.5           | ingredient_2    | 0.7                   |
| dish_A    | feature_2    | 3.2           | ingredient_1    | 0.3                   |
| dish_A    | feature_2    | 3.2           | ingredient_2    | 0.7                   |
| dish_B    | feature_1    | 2.0           | ingredient_1    | 0.2                   |
| dish_B    | feature_1    | 2.0           | ingredient_2    | 0.3                   |
| dish_B    | feature_1    | 2.0           | ingredient_3    | 0.5                   |
| dish_B    | feature_2    | 4.1           | ingredient_1    | 0.2                   |
| dish_B    | feature_2    | 4.1           | ingredient_2    | 0.3                   |
| dish_B    | feature_2    | 4.1           | ingredient_3    | 0.5                   |
+-----------+--------------+---------------+-----------------+-----------------------+

需要将其转换为如下格式的字典列表：

[
    {
        "dish_name": "dish_A", 
        "feature_names": ["feature_1", "feature_2"],
        "feature_values": [1.5, 3.2],
        "ingredient_names": ["ingredient_1", "ingredient_2"],
        "ingredient_proportions": [0.3, 0.7]
    },
    {
        "dish_name": "dish_B", 
        "feature_names": ["feature_1", "feature_2"],
        "feature_values": [2.0, 4.1],
        "ingredient_names": ["ingredient_1", "ingredient_2", "ingredient_3"],
        "ingredient_proportions": [0.2, 0.3, 0.5]
    }
]

核心逻辑是按dish_name分组，对每个分组提取唯一的特征（名称+值）和配料（名称+比例），再整理成目标格式。

步骤1：模拟查询结果 #

假设从PostgreSQL获取的结果是字典列表（实际项目中可直接用数据库驱动返回的结果）：

query_results = [
    {"dish_name": "dish_A", "feature_name": "feature_1", "feature_value": 1.5, "ingredient_name": "ingredient_1", "ingredient_proportion": 0.3},
    {"dish_name": "dish_A", "feature_name": "feature_1", "feature_value": 1.5, "ingredient_name": "ingredient_2", "ingredient_proportion": 0.7},
    {"dish_name": "dish_A", "feature_name": "feature_2", "feature_value": 3.2, "ingredient_name": "ingredient_1", "ingredient_proportion": 0.3},
    {"dish_name": "dish_A", "feature_name": "feature_2", "feature_value": 3.2, "ingredient_name": "ingredient_2", "ingredient_proportion": 0.7},
    {"dish_name": "dish_B", "feature_name": "feature_1", "feature_value": 2.0, "ingredient_name": "ingredient_1", "ingredient_proportion": 0.2},
    {"dish_name": "dish_B", "feature_name": "feature_1", "feature_value": 2.0, "ingredient_name": "ingredient_2", "ingredient_proportion": 0.3},
    {"dish_name": "dish_B", "feature_name": "feature_1", "feature_value": 2.0, "ingredient_name": "ingredient_3", "ingredient_proportion": 0.5},
    {"dish_name": "dish_B", "feature_name": "feature_2", "feature_value": 4.1, "ingredient_name": "ingredient_1", "ingredient_proportion": 0.2},
    {"dish_name": "dish_B", "feature_name": "feature_2", "feature_value": 4.1, "ingredient_name": "ingredient_2", "ingredient_proportion": 0.3},
    {"dish_name": "dish_B", "feature_name": "feature_2", "feature_value": 4.1, "ingredient_name": "ingredient_3", "ingredient_proportion": 0.5},
]

步骤2：分组并转换格式 #

# 初始化分组字典，按菜品名称聚合数据
dish_groups = {}

for row in query_results:
    dish_name = row["dish_name"]
    # 若菜品未被记录，初始化存储结构
    if dish_name not in dish_groups:
        dish_groups[dish_name] = {
            "feature_pairs": set(),  # 用集合自动去重特征(名称,值)对
            "ingredient_pairs": set()  # 用集合自动去重配料(名称,比例)对
        }
    # 添加特征和配料对到对应集合
    dish_groups[dish_name]["feature_pairs"].add( (row["feature_name"], row["feature_value"]) )
    dish_groups[dish_name]["ingredient_pairs"].add( (row["ingredient_name"], row["ingredient_proportion"]) )

# 转换为目标格式的字典列表
final_result = []
for dish_name, group_data in dish_groups.items():
    # 对特征和配料按名称排序（可选，保证结果顺序与示例一致）
    sorted_features = sorted(group_data["feature_pairs"], key=lambda x: x[0])
    sorted_ingredients = sorted(group_data["ingredient_pairs"], key=lambda x: x[0])
    
    final_result.append({
        "dish_name": dish_name,
        "feature_names": [pair[0] for pair in sorted_features],
        "feature_values": [pair[1] for pair in sorted_features],
        "ingredient_names": [pair[0] for pair in sorted_ingredients],
        "ingredient_proportions": [pair[1] for pair in sorted_ingredients]
    })

# 打印验证结果
import json
print(json.dumps(final_result, indent=2))

代码说明 #

• 使用集合存储特征和配料的键值对，自动实现去重，避免同一菜品重复的特征或配料条目。
• sorted()操作用于保证输出列表的顺序与示例一致，若无需固定顺序可省略。
• 通过列表推导式提取名称和数值，快速组装成目标字典结构。

运行结果 #

执行代码后将输出与期望格式完全一致的字典列表。

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