How can I rank entries using sqlalchemy?

Well as far as why one might do this, it's so that you can query for "rank" without needing to perform an aggregate query, which can be more performant. especially if you want to see "whats the rank for user #456?" without hitting every row.

the most efficient way to do this is a single UPDATE. Using standard SQL, we can use a correlated subquery like this:

UPDATE user SET rank=(SELECT count(*) FROM user AS u1 WHERE u1.score > user.score) + 1

Some databases have extensions like PG's UPDATE..FROM, which I have less experience with, perhaps if you could UPDATE..FROM a SELECT statement that gets the rank at once using a window function that would be more efficient, though I'm not totally sure.

Anyway our standard SQL with SQLAlchemy looks like:

from sqlalchemy.orm import aliasedfrom sqlalchemy import funcu1 = aliased(User)subq = session.query(func.count(u1.id)).filter(u1.score > User.score).as_scalar()session.query(User).update({"rank": subq + 1}, synchronize_session=False)

Just cycle on all your users:

users = User.query.order_by(User.score._desc()).all() #fetch them all in one queryfor (rank, user) in enumerate(users):    user.rank = rank + 1 #plus 1 cause enumerate starts from zerodb.session.commit()