How to establish many:many relationship for two different entities in the same table, sqlalchamy

  many-to-many, python, sql, sqlalchemy

I’m looking for some guidance if anyone is willing. I’ve got a table called people, and I would like to set up a way to track relationships between various people. I think the best way to do that would be a many:many relationship with an intermediate table between people. However, it just doesn’t seem to want to work.

relationships = Table('relationships', Base.metadata,
    Column('person_one', ForeignKey('person.id')),
    Column('person_two', ForeignKey('person.id'))

class Person(Base):
"""table to store information about people
most information is going to be saved as a fact tied to this table"""
__tablename__ = 'people'

id                  = Column(Integer, primary_key=True)
source_id           = Column(Integer, ForeignKey('sources.id'))

# one to many relationships
facts               = relationship("Fact", back_populates="person")
source              = relationship("Source", back_populates="people")

# many:many
children = relationship("Person", secondary=relationships, back_populates="parents")
parents = relationship("Person", secondary=relationships, back_populates="children")

def __repr__(self):
    return f"<People(id='{self.id}', source_id='{self.source_id}')>"

Source: Python Questions

LEAVE A COMMENT