Using COUNT(*) OVER() in current query with SQLAlchemy over PostgreSQL

[ferstar]

via https://stackoverflow.com/a/5215028

So I could not find any examples in the SQLAlchemy documentation, but I found these functions:

• count()
• over()
• label()

And I managed to combine them to produce exactly the result I was looking for:

from sqlalchemy import func
query = session.query(Guest, func.count(Guest.id).over().label('total'))
query = query.filter(Guest.deleted == None)
query = query.order_by(Guest.id.asc())
query = query.offset(0)
query = query.limit(50)
result = query.all()

Cheers!

P.S. I also found this question on Stack Overflow, which was unanswered.