用Pandas/SQLAlchemy/SQL将code_name表转为分组嵌套键值字典

我在MySQL中存储了一张code_name枚举字典表，共覆盖A到W 23种类型、合计999条记录，表结构及示例数据如下：

id   type       code    name
-----------------------------
1     A          0      Male
2     A          1     Female
3     B          2      Adult
4     B          3      Child
5     C          C4      Asia
6     C          C5     Europe
7     C          C6     Africa
8     D          D7     Python
9     D          D8      Java
10    D          D9      Golang
11    D          D10     PHP
12    D          D11     Javascript
...
997   W          886    China
998   W          65     Singapore
999   W          81     Japan

最终需要输出的目标结构：

{
    "data": {
        "A": {
            "0": "Male",
            "1": "Female"
        },
        "B": {
            "2": "Adult",
            "3": "Child"
        },
        "...": "其余类型条目按同规则拼接",
        "W": {
            "886": "China",
            "65": "Singapore",
            "81": "Japan"
        }
    }
}

之前的实现是硬编码重复查询：为每个type单独写SELECT语句、单独遍历结果拼字典，不管是原生SQL拼接还是SQLAlchemy查询，都要手写23个独立分支，代码冗余度极高。以下是不同技术栈下的简洁实现：

方案1：SQLAlchemy 轻量原生实现（无额外依赖） #

完全不需要为每个type单独写查询，一次拉取全表需要的字段，在Python层聚合即可，总代码量不到10行：

def get_code_name(self):
    # 按id排序拉取全表需要的三个字段，保证顺序和原表一致
    records = db.session.query(
        CodeName.type, CodeName.code, CodeName.name
    ).order_by(CodeName.id.asc()).all()

    # 循环聚合生成嵌套字典
    result = {}
    for type_val, code_val, name_val in records:
        result.setdefault(type_val, {})[code_val] = name_val

    return {"data": result}

• 优点：单条查询拉全量数据，999条记录的规模下性能完全没有损耗；后续如果type范围从A到W扩展到更多字母，不需要修改任何查询逻辑，代码自动适配。

方案2：Pandas 实现（适配现有数据分析流程的场景） #

如果项目已经引入pandas，可以用内置的分组聚合能力简化代码：

import pandas as pd

def get_code_name(self):
    # 直接把SQLAlchemy查询结果读成DataFrame
    df = pd.read_sql(
        sql=db.session.query(CodeName.type, CodeName.code, CodeName.name).order_by(CodeName.id).statement,
        con=db.session.bind
    )
    # 按type分组，每个组内把code、name转成映射字典，再拼接成整体结构
    data = df.groupby("type").apply(
        lambda g: dict(zip(g["code"], g["name"])),
        include_groups=False
    ).to_dict()

    return {"data": data}

• 注意：pandas 2.0及以上版本需要加include_groups=False参数，避免分组键被重复带入apply逻辑触发警告。

方案3：纯MySQL 端聚合实现（逻辑下推到数据库层） #

如果不想在Python层做聚合处理，可以直接用MySQL内置的JSON函数拼出最终结构，查询返回的结果就是目标格式：

SELECT JSON_OBJECT(
    'data',
    JSON_OBJECTAGG(
        t.type,
        JSON_OBJECT(t.code, t.name)
    )
) AS final_result
FROM code_name t;

• 适配要求：该语法需要MySQL 5.7.22及以上版本支持；如果存在同type下code重复的异常数据，需要提前加去重逻辑避免聚合报错。

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