SQLAlchemy-如何使用中介关联表建立一对多关系

Question

我试图使用SQLAlchemy描述一对多关系，但由于数据库设计本身的原因，我无法直接链接两个对象。

____________       _______________________           _____________
|...parent...|     |....parent_children....| ------> |....child....|
|============|     |=======================|         |=============| 
|--int: id---|---> |----int: child_id------| ------> |---int: id---|
|------------|     |----int: parent_id-----|         |-------------|
|____________|     |_______________________| ------> |_____________|

• 一个父可以有多个子
• 子只能有一个父

在纯SQL中，我必须这样做：

-- Finding the parent of a child
SELECT p.* 
FROM child c
INNER JOIN parent_children pc ON pc.child_id = c.id
INNER JOIN parent p ON pc.parent_id = p.id
WHERE c.id = 2323 -- example
-- Finding parent childrens
SELECT c.* 
FROM parent p
INNER JOIN parent_children pc ON pc.parent_id = p.id
INNER JOIN child c ON pc.child_id = c.id
WHERE p.id = 32323 -- example****

这是缩小模型：

class Parent(Base):
__tablename__ = 'parent'
id = Column(Integer, primary_key=True)
children = # TODO: HOW TO populate the list of childrens here?
class ParentChild(Base):
__tablename__ = 'parent_children'
child_id = Column(Integer, ForeignKey('child.id'))
parent_id = Column(Integer, ForeignKey('parent.id'))
class Child(Base):
__tablename__ = 'child'
id = Column(Integer, primary_key=True)
parent = # TODO: How to populate the parent of this child here?

我曾试图遵循此文档，将ParentChild视为关联表，但每当我使用它来关联child_id和parent_id时，它都会添加这两个，因为我不是直接将Parent与Child关联，而是将Parent与ParentChild关联

Answer 1

class ParentChild(Base):
__tablename__ = 'parent_children'
child_id = Column(Integer, ForeignKey('child.id'), nullable=False, primary_key=True, unique=True)
parent_id = Column(Integer, ForeignKey('parent.id'), index=True, nullable=False) 
# ForeignKeyConstraint(['parent_id'], ['parent.id'],
#                      use_alter=True, name='name_this_constraint_1')
# UniqueConstraint('child_id', 'child.id', name='name_this_unique_constraint_2')
# ForeignKeyConstraint(['child_id'], ['child.id'],
#                      use_alter=True, name='name_this_constraint__3')
class Child(Base):
__tablename__ = 'child'
id = Column(Integer, primary_key=True)}
class Parent(Base):
__tablename__ = 'parent'
id = Column(Integer, primary_key=True)
children = relationship('Child',
secondary='parent_children',
)