查询嵌套对象/表 sqlalchemy ORM

Question

我有一个表，它和其他表之间的关系很复杂，层级很深。

@mapper_registry.mapped
@dataclass
class Project:
    __tablename__ = "project"
    __table_args__ = (#some table constraints here)

    id: float = field(init=False, metadata={"sa": Column(NUMBER(15, 0, False), primary_key=True)})
    date_created: datetime = field(metadata={"sa": Column(MyTimestamp, nullable=False)})
    user_created_id: float = field(metadata={"sa": Column(NUMBER(15, 0, False), nullable=False)})
    user_created: Optional[BiologicsUser] = field(default=None, metadata={"sa": relationship("User", foreign_keys="Project.user_created_id", backref="project")})
    
    
mapper_registry.mapped
@dataclass
class User:
    __tablename__ = "user"
    __table_args__ = ()
    __sa_dataclass_metadata_key__ = "sa"

    id: float = field(
        init=False, metadata={"sa": Column(NUMBER(15, 0, False), primary_key=True)}
    )
    name: str = field(metadata={"sa": Column(VARCHAR(100), nullable=False)})
    organization_id: Optional[float] = field(default=None, metadata={"sa": Column(NUMBER(15, 0, False))})
    department_id: Optional[float] = field(default=None, metadata={"sa": Column(NUMBER(15, 0, False))})
    site_id: Optional[float] = field(default=None, metadata={"sa": Column(NUMBER(15, 0, False))})
    department: Optional[Department] = field(default=None,metadata={"sa": relationship("Department", backref="user")})
    organization: Optional[Organization] = field(default=None,metadata={"sa": relationship("Organization", backref="user")})
    site: Optional[Site] = field(default=None,metadata={"sa": relationship("Organization", backref="user")})
    
    

@dataclass
class Department:
    __tablename__ = "department"
    __table_args__ = ( )
    __sa_dataclass_metadata_key__ = "sa"

    id: float = field(init=False, metadata={"sa": Column(NUMBER(15, 0, False), primary_key=True)})
    label: str = field(metadata={"sa": Column(VARCHAR(255), nullable=False)})

@dataclass
class Organization:
    __tablename__ = "organization"
    __table_args__ = ( )
    __sa_dataclass_metadata_key__ = "sa"

    id: float = field(init=False, metadata={"sa": Column(NUMBER(15, 0, False), primary_key=True)})
    label: str = field(metadata={"sa": Column(VARCHAR(255), nullable=False)})
    
@dataclass
class Site:
    __tablename__ = "site"
    __table_args__ = ( )
    __sa_dataclass_metadata_key__ = "sa"

    id: float = field(init=False, metadata={"sa": Column(NUMBER(15, 0, False), primary_key=True)})
    label: str = field(metadata={"sa": Column(VARCHAR(255), nullable=False)})

当我用下面的方式查询项目时：

session.execute(select(Project)).all()

我得到了一个嵌套的字典/对象，里面包含了项目的所有列和嵌套的用户创建列，这都是我预期的。但是我不想返回所有这些内容，因为它们形成了一个很大的嵌套对象树。所以我尝试只选择我需要的字段。

比如，当我尝试选择id和用户创建时，使用了select(Project.id, Project.user_created)，结果却出现了错误。

ORA-00923: FROM keyword not found where expected
E   Help: https://docs.oracle.com/error-help/db/ora-00923/
E   [SQL: SELECT project.id, user.id = project.user_created_id AS anon_1
E   FROM project, user]  - sqlalchemy.exc.DatabaseError: (oracledb.exceptions.DatabaseError) ORA-00923: FROM keyword not found where expected

我天真的想，既然我可以获取所有列，包括嵌套的列，那我应该也能只选择我需要的列，并且加载所有嵌套的列。所以我尝试了query = select(Project).options(load_only(Project.id, Project.user_created))。

结果还是出错了。

Can't apply "column loader" strategy to property "Project.user_created", which is a "relationship"; this loader strategy is intended to be used with a "column property".

于是我改成了query = select(Project).options(load_only(project.id), selectinload(Project.user_created))。

但还是失败了。我不太确定我哪里做错了。

Answer 1

这有点让人困惑，但我觉得关键问题在于列和关系之间的界限不太清楚。像 project.id 这样的就是一个列，而 project.user_created 则是一个关系，也就是说它指向另一个模型。你需要把这两者区分开来。你可以通过在你的引擎上设置 echo=True 来查看发生了什么。

这里有几个例子：

A. 使用纯ORM时，你可以限制项目对象只包含一个 id 列，然后通过另一个查询来加载 user_created 关系。

q = select(Project).options(load_only(Project.id), selectinload(Project.user_created))
for project in session.scalars(q):
    print (project.id, project.user_created.id)

B. 选择 id 列和 user_created 对象，但你需要自己指定连接，这样应该只用一个查询。

q = select(Project.id, Project.user_created).join(Project.user_created)
for project_id, project_user_created in session.execute(q):
    print (project.id, project.user_created.id)