How to count results with SQLAlchemy Select API?

Question

All answers to this question (i.e. counting results from queries in SQLAlchemy) that I could find, where given for Query API. But in my application I am using AsyncSession which does not support query and I have to use select. I have no idea how to get count of all results from query using select API as count() simply does not work.

My code so far:

    q = (
        select(User)
        .order_by(User.id)
    )
    if user_type:
        q = q.where(User.user_type == user_type)
    if name:
        q = q.where(User.name.contains(name))
    if surname:
        q = q.where(User.surname.contains(surname))
    if email:
        q = q.where(User.email.contains(email))

    result = await self.db_session.execute(q)
    results = result.scalars()

    return results

What I'd like to achieve is something like:

count_results = results.count()

and return them alongside with list of retrieved objects. Thank all of you in advance!

Answer 1

You should be able to use func.count from the core API either with OR without an argument: sqlalchemy.sql.functions.count

from sqlalchemy.sql import func

q = (
        select(func.count(User.id))
        .order_by(User.id)
    )
#...