在SQLAlchemy中与关联对象的自引用多对多关系

问题描述：

我已经找到了与关联表进行自引用多对多关系的示例.如何使用关联对象实现相同目的?

I've found examples for a self-referencing many-to-many relationship with an association table. How can I achieve the same using an association object?

以下代码基于:

答

以下方法使用关联对象而不是关联表来获得自引用的多对多关系:

The following approach uses an association object instead of an association table to get a self-referencing many-to-many relationship:

from sqlalchemy import Column, Integer, ForeignKey, create_engine, String
from sqlalchemy.orm import relationship, sessionmaker
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class EntityAssociation(Base):
    __tablename__ = 'entity_association'

    entity_parent_id = Column(Integer, ForeignKey('entity.id'), primary_key=True)
    entity_child_id = Column(Integer, ForeignKey('entity.id'), primary_key=True)

class Entity(Base):
    __tablename__ = 'entity'

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

    entity_childs = relationship('Entity',
                                 secondary='entity_association',
                                 primaryjoin=id==EntityAssociation.entity_parent_id,
                                 secondaryjoin=id==EntityAssociation.entity_child_id,
                                 backref='childs')

    entity_parents = relationship('Entity',
                                  secondary='entity_association',
                                  primaryjoin=id==EntityAssociation.entity_child_id,
                                  secondaryjoin=id==EntityAssociation.entity_parent_id,
                                  backref='parents')

    def __repr__(self):
        return f'<Entity(name={self.name})>'

if __name__ == '__main__':
    engine = create_engine('sqlite://')
    Base.metadata.create_all(engine)
    Session = sessionmaker(engine)

    db = Session()

    parent1 = Entity(name='parent1')
    parent2 = Entity(name='parent2')
    child1 = Entity(name='child1')
    child2 = Entity(name='child2')

    parent1.entity_childs = [child1, child2]
    parent2.entity_childs = [child2]

    db.add(parent1)
    db.add(parent2)
    db.add(child1)
    db.add(child2)
    db.commit()

    entities = db.query(Entity).all()
    for entity in entities:
        print(entity)
        print('   Parent: ', entity.entity_parents)
        print('   Childs: ', entity.entity_childs)
        print()

这将产生以下结果:

<Entity(name=parent1)>
   Parent:  []
   Childs:  [<Entity(name=child1)>, <Entity(name=child2)>]

<Entity(name=child1)>
   Parent:  [<Entity(name=parent1)>]
   Childs:  []

<Entity(name=child2)>
   Parent:  [<Entity(name=parent1)>, <Entity(name=parent2)>]
   Childs:  []

<Entity(name=parent2)>
   Parent:  []
   Childs:  [<Entity(name=child2)>]

在SQLAlchemy中与关联对象的自引用多对多关系

相关推荐