How to create relationship many to many in SQLAlchemy (python, flask) for model User to itself

The pattern you're trying to implement is a special case of many-to-many relationship. SQLAlchemy calls this an Adjacency List Relationship, and I recommend trying to follow through the code there:

http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#adjacency-list-relationships

The key is the 'remote_side' kwarg there.

Here's why: the error that you're getting is because you association table ('friends') has two foreign keys pointing to table 'users': one on column 'user_id', and one on column 'friend_id'. SQLAlchemy tries to auto-detect relationships based on foreign keys, but it fails because it can't tell which direction the relationship goes. So if you have an entry in table 'friends' like so

user_id   : 1friend_id : 2

SQLAlchemy can't tell whether user_1 has user_2 as a friend, or vice-versa.

If that seems confusing, it is. Friendship in the sense of social networks can be unijective, in which case user_1 having friend user_2 does not mean that user_2 has user_1 as a friend; or it can be bijective, in which case the two are equivalent. I'm showing my age here, but the former is represented by Livejournal, whereas the latter is represented by Facebook.

I don't know off the top of my head how to implement a unijective relationship in SQLAlchemy. It's an ugly UNION ALL or something like that in MySQL.

Miguel Grinberg's tutorial explain this type of application.

http://blog.miguelgrinberg.com/post/the-flask-mega-tutorial-part-viii-followers-contacts-and-friends

You can't directly make Many-To-Many relation between 2 tables, instead you have to use 3rd table.