SqlAlchemy: How to create connections between users, i.e. make "friends"

Question

I'm trying to use SqlAlchemy to make Sqlite database tables inside of Pylons. I'm using declarative base to create the table, class, and mapper all at once with the following code:

class Friends(Base):
    __tablename__ = 'friends'
    left_id = Column(Integer, ForeignKey('facebooks.id'), primary_key=True)
    right_id = Column(Integer, ForeignKey('facebooks.id'), primary_key=True) 

    def __repr__(self):
        return "<Friend(id:'%s' id: '%s')>" % (self.left_id, self.right_id)

class Facebook(Base):
    __tablename__ = 'facebooks'

    id = Column(Integer, primary_key=True)
    friends = relationship("Facebook",
                           secondary=Friends.__tablename__,
                           primaryjoin= id == Friends.right_id,
                           secondaryjoin= Friends.left_id == id)    

    def __init__(self, id):
        self.id = id

    def __repr__(self):
        return "<User(id:'%s')>" % (self.id)

I'm just learning about all the different relationships like many to one, one to many, one to one, and many to many and how to implement each with tables and/or declatively. I'm wondering, how do I associate an object with itself? For example, I want to associate facebooks with other facebooks. In other words, to build connections between them, and establish them as "friends". How would I structure the database to make this possible?

Edit: I changed the code, which I've updated above, and I've added an association object called "Friends," but when I add a friend to a facebook object, it only works in one direction. If I add Bob as a friend to John, I can see Bob in John.Friends, but I cannot see John in Bob.Friends. What am I doing wrong? I tried adding the following relationship in the Friends class:

friend = relationship("Facebook", backref="friends")

but I get an error:

sqlalchemy.exc.ArgumentError: Could not determine join condition between parent/child tables on relationship Friends.friend. Specify a 'primaryjoin' expression. If 'secondary' is present, 'secondaryjoin' is needed as well.

Answer 1

Where is this much different from 1:N or N:M relationship? Storing the friend relationships in a table isFriend(user1_id, user2_id) is straight forward. If you think of a friendship relationship as graph, check this: http://www.sqlalchemy.org/docs/orm/examples.html#directed-graphs