如何在 SQLAlchemy 中使用关系

Question

我是 SQLAlchemy 的新手，在将关系概念实现到我的逻辑中时遇到了困难。

我有两个表：Offices 和 Departments，其中部门包括其所在的办公室。

我尝试应用文档 https://www.tutorialspoint.com/sqlalchemy/sqlalchemy_orm_building_relationship.htm 但我很难理解应该在哪里插入 Relationship 以及是否 back_populates 属性在这种情况下实际上很有用。

class Offices(Base):
    __tablename__: 'offices'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    code = Column(String)
    reader = Column(Integer) 


class Departments(Base):
    __tablename__ = 'departments'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    office_id = Column(Integer, ForeignKey('offices.id'))
    office = relationship("Offices", back_populates="departments")
    
    def __init__(self, name):
        self.name = name

另外，我是否必须在 department 的 __init__ 中定义与 office 相关的任何内容，以便在尝试插入新行时保存它到表中？

Answer 1

您可以在两个模型中指定它们relationship

class Offices(Base):
    __tablename__: 'offices'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    code = Column(String)
    reader = Column(Integer) 

    departments = relationship("Departments", back_populates='office')


class Departments(Base):
    __tablename__ = 'departments'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    office_id = Column(Integer, ForeignKey('offices.id'))

    office = relationship("Offices", back_populates="departments")

之后您可以实现此目的（假设department 和office 是对象）

department.offices  # this will refer to office
office.departments  # this will refer to departments set (department is one of them)

替代方案是backref。它会自己创建反向关系，所以

class Offices(Base):
        __tablename__: 'offices'
        id = Column(Integer, primary_key=True)
        name = Column(String)
        code = Column(String)
        reader = Column(Integer) 

    
    
    
    class Departments(Base):
        __tablename__ = 'departments'
        id = Column(Integer, primary_key=True)
        name = Column(String)
        office_id = Column(Integer, ForeignKey('offices.id'))

        office = relationship("Offices", backref="departments")

会做与第一个相同的事情