Sqlalchemy Error, Multiple Foreign Keys References To The Same Table And Column

I already tried a solution from this question and this but failed (on of these solutions are present here), I don't know what to say additionally, logically both FK (sender and rec

Solution 1:

This works for me in version 1.4:

class Message(Base):
    __tablename__ = "messages"
    id = sa.Column(sa.Integer, primary_key=True)
    sender_id = sa.Column(
        sa.String(10), sa.ForeignKey("users.id"), nullable=False
    )
    recipient_id = sa.Column(
        sa.String(10), sa.ForeignKey("users.id"), nullable=False
    )
    data = sa.Column(sa.String, nullable=False)
    sender = relationship(
        "User", foreign_keys=[sender_id], back_populates="sent_messages"
    )
    recipient = relationship(
        "User", foreign_keys=[recipient_id], back_populates="received_messages"
    )

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


class User(Base):
    __tablename__ = "users"
    id = sa.Column(sa.String(10), primary_key=True)
    sent_messages = relationship(
        "Message",
        foreign_keys=[Message.sender_id],
        back_populates="sender",
        cascade="all, delete",
    )
    received_messages = relationship(
        "Message",
        foreign_keys=[Message.recipient_id],
        back_populates="recipient",
        cascade="all, delete",
    )

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


Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)

with sa.orm.Session(engine, future=True) as session:
    gord = User(id="gord")
    david = User(id="david")
    msg = Message(sender=gord, recipient=david, data="Hello, David!")
    session.add(msg)  # dependent objects are added automatically
    session.commit()

    results = session.execute(sa.text("SELECT * FROM messages")).fetchall()
    print(results)
    # [(1, 'gord', 'david', 'Hello, David!')]

    print(david.received_messages)
    # [<Message(id=1, data='Hello, David!')>]