无主键/外键关联表数据查询与格式化 - SQLModel+FastAPI

我正在开发一个基于FastAPI的应用，用于从Oracle数据库表中获取数据。当前核心问题：Oracle表无主键和外键，表间依赖唯一键建立关联，关联逻辑如下：

• Department与Employee通过employee_id（唯一键）关联，每个Department对应一名Employee
• Employee与Resource通过resource_id（唯一键）关联，员工姓名存储在Resource表，Employee仅保存非敏感数据

最终需求：查询Department数据时，将对应员工姓名纳入返回结果。

定义的SQLModel类 #

from typing import Optional
from sqlmodel import SQLModel, Field, Relationship, PrimaryKeyConstraint, RelationshipProperty


class Resource(SQLModel, table=True):
    __tablename__ = "resource"
    __table_args__ = (
        PrimaryKeyConstraint('resource_id'),
    )

    resource_id: int = Field(default=None, primary_key=True)
    name: Optional[str]
    employee_rep: Optional["Employee"] = Relationship(
        back_populates="resource",
        sa_relationship=RelationshipProperty(
            "Employee",
            primaryjoin="foreign(Resource.resource_id) == Employee.resource_id",
            uselist=False
        )
    )


class Employee(SQLModel, table=True):
    __tablename__ = "employee"
    __table_args__ = (
        PrimaryKeyConstraint('employee_id'),
    )

    employee_id: int = Field(default=None, primary_key=True)
    resource_id: Optional[int] = Field(default=None, foreign_key="resource.resource_id")
    resource: Resource = Relationship(
        back_populates="employee_rep",
        sa_relationship=RelationshipProperty(
            "Resource",
            primaryjoin="foreign(Employee.resource_id) == Resource.resource_id",
            uselist=False
        )
    )


class Department(SQLModel, table=True):
    __tablename__ = "department"
    __table_args__ = (
        PrimaryKeyConstraint('department_id'),
    )

    department_id: int = Field(default=None, primary_key=True)
    employee_id: Optional[str] = Field(default=None, foreign_key="employee.employee_id")
    employee_name: Employee = Relationship(
        back_populates="resource",
        sa_relationship=RelationshipProperty(
            "Employee",
            primaryjoin="foreign(Department.employee_id) == Employee.employee_id",
            uselist=False
        )
    )

尝试的查询方式 #

方式1：直接关联查询（无法返回带员工姓名的结果） #

statement = select(Department).join(Employee, Department.employee_id == Employee.employee_id).join(Resource, Employee.resource_id == Resource.resource_id)
results = session.exec(statement).first()

方式2：多表查询（返回拆分的数据结构） #

statement = select(Department, Resource).join(Employee, Department.employee_id == Employee.employee_id).join(Resource, Employee.resource_id == Resource.resource_id)
results = session.exec(statement).first()

result_dicts = [
    {
        "Department": [department, resource]
    }
    for department, resource in results
]

当前返回结果 #

[
{
"Department": [
{
"department_id": 1,
"employee_id": "007"
},
{
"resource_id": 123456,
"name": "Jon Doe"
}
]
}
]

期望返回格式 #

[
{
"Department": {
"department_id": 1,
"employee_id": "007",
"employee_name": "Jon Doe"
}
}
]

核心疑问 #

1. 是否可以通过SQLModel的关联配置直接实现这种嵌套格式的返回，还是必须手动拼接数据？
2. 如何确保获取数据后支持员工姓名的更新操作？

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