What is the equivalent of Query.count in the SQLAlchemy 1.4 ORM?

Question

Given a SQLAlchemy ORM model like this

class Foo(Base):
    __tablename__ = 'foo'

    id = sa.Column(sa.Integer, primary_key=True)
    name = sa.Column(sa.String())

In SQLAlchemy 1.4 / 2.0, the ORM's session.query idiom is being unified with the SQLAlchemy core select function*, so to fetch all Foos we would do

foos = session.execute(Foo).scalars().all()

instead of

foos = session.query(Foo).all()

In the current (sqlalchemy<=1.3) ORM we can obtain the number of Foos in the database with this query:

nfoos = session.query(Foo).count()

But how can we get the count in SQLALchemy 1.4?

session.execute(sa.select(Foo).count())

raises

AttributeError: 'Select' object has no attribute 'count'

session.execute(sa.select(Foo)).count()

raises

AttributeError: 'ChunkedIteratorResult' object has no attribute 'count'

session.execute(sa.select(sa.func.count(Foo)))

raises

sqlalchemy.exc.ArgumentError: SQL expression element expected, got <class '__main__.Foo'>.

This works,

session.execute(sa.select(sa.func.count(Foo.id))).scalars()

but specifying an attribute seems less OO / elegant than the Query.count version. Moreover, it precludes building a query but deferring the decision of whether to retrieve the count or the model instances.

What is the idiomatic way to get the count() of an ORM query in the new SQLAlchemy 1.4 world?

---

* the session.query() API will still work in 1.4 and later

Answer 1

Based on Ilja Everilä's comments, it seems that there is no direct equivalent to Query.count in the new ORM-querying API released in SQLAlchemy 1.4 (in beta at the time of writing).

The functional equivalent is to call count(), selecting from a subquery*

from sqlalchemy import func, select

count = (
    session.execute(select(func.count()).select_from(select(Foo).subquery()))
    .scalar_one()
)

Generating this SQL

SELECT count(*) AS count_1 
 FROM (SELECT foo.id AS id, foo.name AS name 
         FROM foo) 
AS anon_1

Counting an attribute, such as the primary key column, generates a simple SELECT COUNT

count = session.execute(select(func.count(Foo.id))).scalar_one()

SELECT count(foo.id) AS count_1 
FROM foo

Query.count is still supported in 1.4, so it can still be used, as can all the ORM features available in 1.3.

---

* Query.count also selects from a subquery rather than executing SELECT COUNT directly.