SQLAlchemy declarative many-to-many self-join via Association object

Question

I have a table Users and a table Friends which maps users to other users as each user can have many friends. This relation is obviously symmetric: if user A is a friend of user B then user B is also a friend of user A, I only store this relation once. The Friends table has additional fields besides the two User ID's so I have to use an association object.

I am trying to define this relationship in declarative style in the Users class (which extends the declarative base), but I can't seem to figure out how to do this. I want to be able to access all friends of a given user via a property friends, so say friends = bob.friends.

What's the best approach for this problem? I tried to many different setups to post here, and none of them worked for various reasons.

EDIT: My latest attempt looks like this:

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)

    # Relationships
    friends1 = relationship('Friends', primaryjoin=lambda: id==Friends.friend1ID)
    friends2 = relationship('Friends', primaryjoin=lambda: id==Friends.friend2ID)


class Friends(Base):
    __tablename__ = 'friends'
    id = Column(Integer, primary_key=True)
    friend1ID = Column(Integer, ForeignKey('users.id') )
    friend2ID = Column(Integer, ForeignKey('users.id') )
    status = Column(Integer)

    # Relationships
    vriend1 = relationship('Student', primaryjoin=student2ID==Student.id)
    vriend2 = relationship('Student', primaryjoin=student1ID==Student.id)

This however results in the following error:

InvalidRequestError: Table 'users' is already defined for this MetaData instance.  Specify 'extend_existing=True' to redefine options and columns on an existing Table object.

I must admit that at this point I am thoroughly confused because of many failed attempts and might have made more than one stupid mistake in the above.

Answer 1

That particular exception is caused by describing the table more than once, either by repeatedly defining the class mapping (say, in the interactive interpreter, or in a function that can be called more than once), or by mixing declarative style class mappings with table reflection. In the former case, eliminate the repeated call; start a new interpreter if you are doing it interactively, or eliminate the extra function calls (possibly a good use for a singleton/borg object).

In the latter case, just do what the exception says, add __table_args__ = {'extend_existing': True} as an extra class variable in your class definitions. Only do this if you are actually sure that the table is being correctly described twice, as with table reflection.