SQLAlchemy关联加载父表问题：查询C时预加载A、B属性

先补全模型的反向关联关系 #

当前你的模型里缺少从子表到父表的反向关联，这是没法从C加载B和A的关键问题，先把模型补全：

class A(db.Model):
    __tablename__ = 'table_a'
    id: Mapped[int] = mapped_column(primary_key=True)
    bs: Mapped[List['B']] = relationship(
        lazy='select',
        passive_deletes='all',
        back_populates='a',  # 修正为对应B的a属性
    )
    some_attribute: Mapped[int]

class B(db.Model):
    __tablename__ = 'table_b'
    id: Mapped[int] = mapped_column(primary_key=True)
    a_id: Mapped[int] = mapped_column(
        ForeignKey('table_a.id', ondelete='RESTRICT'),
        unique=True,
    )
    a: Mapped['A'] = relationship(back_populates='bs')  # 添加反向关联到A
    cs: Mapped[List['C']] = relationship(
        passive_deletes='all',
        lazy='select',
        back_populates='b',  # 修正为对应C的b属性
    )
    some_other_attribute: Mapped[str]

class C(db.Model):
    __tablename__ = 'table_c'
    id: Mapped[int] = mapped_column(primary_key=True)
    b_id: Mapped[int] = mapped_column(
        ForeignKey('table_b.id', ondelete='RESTRICT'),
        unique=True,
    )
    b: Mapped['B'] = relationship(back_populates='cs')  # 添加反向关联到B
    uuid: Mapped[str]
    # 你的子表关联定义，比如sub_table_1等
    sub_table_1: Mapped['SubTable1'] = relationship(...)

推荐查询方式：直接从C发起查询 #

既然目标是获取C以及关联的B、A和子表，直接从C查最直观，也能避免多余的关联层级：

from sqlalchemy.orm import joinedload

c_instance = C.query
    .filter(C.uuid == c_uuid)
    .options(
        joinedload(C.b).joinedload(B.a),  # 链式加载B和对应的A
        joinedload(C.sub_table_1),
        joinedload(C.sub_table_2),
        joinedload(C.sub_table_3)
    )
    .first_or_404(f'C not found for uuid {c_uuid}.')

# 现在直接访问属性不会触发额外查询
print(c_instance.b.some_other_attribute)
print(c_instance.b.a.some_attribute)

若一定要从A发起查询的写法 #

之前报错是因为查询根是A，但joinedload直接用了C的属性，SQLAlchemy需要你指定从A到目标属性的完整关联路径：

from sqlalchemy.orm import joinedload

a_instance = A.query
    .join(B, A.bs)
    .join(C, B.cs)
    .filter(C.uuid == c_uuid)
    .options(
        joinedload(A.bs).joinedload(B.cs).joinedload(C.sub_table_1),
        joinedload(A.bs).joinedload(B.cs).joinedload(C.sub_table_2),
        joinedload(A.bs).joinedload(B.cs).joinedload(C.sub_table_3)
    )
    .first_or_404(f'C not found for uuid {c_uuid}.')

# 因为是一对多关联，过滤后只会有一个匹配的B和C，直接取第一个即可
target_c = a_instance.bs[0].cs[0]
print(target_c.b.some_other_attribute)
print(target_c.b.a.some_attribute)

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