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

Question

In a prototype application that uses Python and SQLAlchemy with a PostgreSQL database I have the following schema (excerpt):

class Guest(Base):
    __tablename__ = 'guest'
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    surname = Column(String(50))
    email = Column(String(255))
    [..]
    deleted = Column(Date, default=None)

I want to build a query, using SQLAlchemy, that retrieves the list of guests, to be displayed in the back-office.

To implement pagination I will be using LIMIT and OFFSET, and also COUNT(*) OVER() to get the total amount of records while executing the query (not with a different query).

An example of the SQL query could be:

  SELECT id, name, surname, email,
       COUNT(*) OVER() AS total
    FROM guest
   WHERE (deleted IS NULL)
ORDER BY id ASC
   LIMIT 50
  OFFSET 0

If I were to build the query using SQLAlchemy, I could do something like:

query = session.query(Guest)
query = query.filter(Login.deleted == None)
query = query.order_by(Guest.id.asc())
query = query.offset(0)
query = query.limit(50)
result = query.all()

And if I wanted to count all the rows in the guests table, I could do something like this:

from sqlalchemy import func
query = session.query(func.count(Guest.id))
query = query.filter(Login.deleted == None)
result = query.scalar()

Now the question I am asking is how to execute one single query, using SQLAlchemy, similar to the one above, that kills two birds with one stone (returns the first 50 rows and the count of the total rows to build the pagination links, all in one query).

The interesting bit is the use of window functions in PostgreSQL which allows the abovementioned behaviour, thus saving you from having to query twice but just once.

Is it possible?

Thanks in advance.

Answer 1

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.