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-to-many relationship with an intermediate table between people.
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 a person
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}')>"
I get the following error:
sqlalchemy.exc.NoForeignKeysError: Could not determine join condition
between parent/child tables on relationship Person.children - there are
no foreign keys linking these tables via secondary table 'relationships'.
Ensure that referencing columns are associated with a ForeignKey or
ForeignKeyConstraint, or specify 'primaryjoin' and 'secondaryjoin' expressions.