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.

lenz · Accepted Answer · 2020-11-22 23:32:28Z

15

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

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.

edited Nov 22, 2020 at 23:32

lenz

5,8585 gold badges27 silver badges47 bronze badges

answered Sep 3, 2018 at 12:58

Jaume Sabater

4135 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related