python - SQLAlchemy ORM - 3-一些对象关系

Question

我将 SQLAlchemy 与 Postgres 一起使用。来自https://auth0.com/blog/sqlalchemy-orm-tutorial-for-python-developers/
的代码示例

我有 3 个对象：

• 演员
• 特技演员
• 联系方式

在原始代码示例中，关系如下：

• 演员：ContactDetails = 1:0..*
• 演员：特技演员 = 1:1

见代码：

class Actor(Base):
    __tablename__ = 'actors'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    birthday = Column(Date)


class Stuntman(Base):
    __tablename__ = 'stuntmen'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    active = Column(Boolean)
    actor_id = Column(Integer, ForeignKey('actors.id'))
    actor = relationship("Actor", backref=backref("stuntman", uselist=False))


class ContactDetails(Base):
    __tablename__ = 'contact_details'

    id = Column(Integer, primary_key=True)
    phone_number = Column(String)
    address = Column(String)
    actor_id = Column(Integer, ForeignKey('actors.id'))
    actor = relationship("Actor")

我正在努力将此模型扩展到以下关系：

• 演员：ContactDetails = 1:0..*
• 演员：特技演员 = 1:1
• 特技演员：联系方式 = 1:0..*

特技演员也很努力，应该得到联系方式。请问谁能帮忙？！

标签： pythonpostgresqlormsqlalchemy

Answer 1

在我看来，实现所需的最佳方法是使用关联表。这个应该工作：

actors_to_contact_details = Table('actors_to_contact_details', Base.metadata,
    Column('actor_id', Integer, ForeignKey('actors.id')),
    Column('contact_detail_id', Integer, ForeignKey('contact_details.id'))
)


stuntmen_to_contact_details = Table('stuntmen_to_contact_details', Base.metadata,
    Column('stuntman_id', Integer, ForeignKey('stuntmen.id')),
    Column('contact_detail_id', Integer, ForeignKey('contact_details.id'))
)


class Actor(Base):
    __tablename__ = 'actors'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    birthday = Column(Date)
    contact_details = relationship("ContactDetails", secondary=actors_to_contact_details)


class Stuntman(Base):
    __tablename__ = 'stuntmen'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    active = Column(Boolean)
    actor_id = Column(Integer, ForeignKey('actors.id'))
    actor = relationship("Actor", backref=backref("stuntman", uselist=False))
    contact_details = relationship("ContactDetails", backref="stuntman", secondary=stuntmen_to_contact_details)


class ContactDetails(Base):
    __tablename__ = 'contact_details'

    id = Column(Integer, primary_key=True)
    phone_number = Column(String)
    address = Column(String)

如果你愿意，你可以unique=True像这样放入关联表

actors_to_contact_details = Table('actors_to_contact_details', Base.metadata,
    Column('actor_id', Integer, ForeignKey('actors.id')),
    Column('contact_detail_id', Integer, ForeignKey('contact_details.id'), unique=True)
)


stuntmen_to_contact_details = Table('stuntmen_to_contact_details', Base.metadata,
    Column('stuntman_id', Integer, ForeignKey('stuntmen.id')),
    Column('contact_detail_id', Integer, ForeignKey('contact_details.id'), unique=True)
)

其他选择是将另一个可为空的外键放在Stuntman表上，但我会采用上面介绍的解决方案。