如何获取SQLAlchemy关联查询中含关系数据的Pandas DataFrame

你直接调用pd.read_sql(query.statement)读取的是SQL层的表字段，不会自动加载ORM层配置的relationship关联对象，要把关联的scenary信息作为数组字段存入DataFrame，可参考以下两种可直接复用的实现方式：

• 方式1：序列化ORM查询结果后转DataFrame
  该方式会自动读取你定义的relationship配置，不需要手动写关联查询逻辑，适配你当前的模型定义：

from sqlalchemy.orm import class_mapper
import pandas as pd

def serialize_orm(obj, processed=None):
    if processed is None:
        processed = set()
    # 避免循环引用导致死循环
    if id(obj) in processed:
        return None
    processed.add(id(obj))
    
    # 先读取当前表的普通字段
    mapper = class_mapper(obj.__class__)
    res = {col.key: getattr(obj, col.key) for col in mapper.columns}
    
    # 处理关联关系字段
    for rel_name, rel_config in mapper.relationships.items():
        rel_data = getattr(obj, rel_name)
        if rel_data is None:
            res[rel_name] = [] if rel_config.uselist else None
            continue
        if rel_config.uselist:
            # 一对多/多对多关系直接序列化为数组
            res[rel_name] = [serialize_orm(item, processed.copy()) for item in rel_data]
        else:
            # 你当前模型中Materials和Scenaries是多对一关系，按需求把单个关联对象包成数组
            res[rel_name] = [serialize_orm(rel_data, processed.copy())]
    return res

# 执行查询，你配置了lazy='subquery'，会一次性加载所有关联数据，不会产生N+1问题
material_list = session.query(Materials).all()
# 转成字典列表后生成DataFrame
content = pd.DataFrame([serialize_orm(item) for item in material_list])

• 方式2：SQL层聚合关联数据直接读取
  如果数据量较大，不想实例化ORM对象，可以直接在SQL层用数据库原生JSON函数把关联数据聚合成数组，直接读取到DataFrame中：

from sqlalchemy import func
import pandas as pd

# 注意根据你用的数据库选择聚合函数：MySQL用json_arrayagg，PostgreSQL用json_agg
agg_func = func.json_arrayagg(
    func.json_object(
        "id", Scenaries.id,
        "code", Scenaries.code,
        "name", Scenaries.name
    )
).label("scenary")

query = session.query(Materials, agg_func)\
    .join(Scenaries, Materials.scenaryId == Scenaries.id)\
    .group_by(Materials.id)

content = pd.read_sql(query.statement, query.session.bind)

补充说明：你当前的表结构是外键存在Materials表上，逻辑上一个Material只对应一个Scenary，如果不是强需求要数组格式，可以去掉序列化逻辑里包裹列表的代码，直接存储scenary字典，结构更合理。如果你的实际需求是查询Scenaries列表，将每个场景关联的多个Materials作为数组字段，只需要把查询目标换成Scenaries，上述两种方法都可以直接使用。

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