How do I query an association table in SQLAlchemy?

Ok, so the key to querying association object in Flask-Sql alchemy is to make an external join to roles_users. Try to join all tables first and then filter afterwards. I'm posting the answer below.

query_user_role = User.query.join(roles_users).join(Role).
filter((roles_users.c.user_id == User.id) & (roles_users.c.role_id == Role.id)).all()

Do not forget to put 'c' when querying association table object. Without it, it won't work.

One more thing, do not forget to set backref lazy = 'joined'


Very similar to the selected answer, but maybe helpful for some people with some more examples:

result = session.query(User.first_name, User.last_name, Role.name, roles_users).filter(
        roles_users.c.user_id == User.id).filter(roles_users.c.role_id == Role.id).all()