CODEWITHSUNDEEP
pythonsqlalchemy
snakecharmerb
snakecharmerb

Reputation: 55884

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

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

Upvotes: 23

Views: 25091

Answers (5)

ndndinger
ndndinger

Reputation: 103

For me - using Flask-Sqlalchemy, with the SQLAlchemy Version 2.X - this worked:

subselect = db.select(Tablename).filter_by(user_id=current_user.id, ...)

select = db.select(db.func.count()).select_from(subselect)

number_of_rows = db.session.execute(select).scalar_one()

Shoutout to @snakecharmerb - on strike

for giving the answer to his/her own question and really helping me out there!

Upvotes: 2

yomajo
yomajo

Reputation: 376

I'm working with Flask, SQLAlchemy's session is equivalent to db.session in Flask-SQLAlchemy (correct me if I'm wrong), here are examples for simple counts:

Legacy:

>>> db.session.query(Order).count()
2111

2.0 syntax (simple row count in table):

>>> from sqlalchemy import func
>>> db.session.scalar(db.select(func.count(Order.order_id)))
2111

I would assume counting on any column is safe, and count does not eliminate duplicates (I assure, I don't have 2000+ currencies):

>>> db.session.scalar(db.select(func.count(Order.currency)))
2111

P.S. generated SQL by select statement print(db.select(func.count(Order.currency))) :

SELECT count("order".currency) AS count_1 
FROM "order"

2.0 syntax (using subqueries):

Counting from subquery is more elaborate as others have suggested.

>>> to_filter = db.select(Order).where(Order.currency=='GBP')
>>> db.session.scalar(db.select(func.count()).select_from(to_filter))
17

Upvotes: 4

Salad.Guyo
Salad.Guyo

Reputation: 3415

You can use rowcount

# using sqlalchemy

  from sqlalchemy import create_engine

    def connect_to_db():
        """Create database connection."""
        url = f"mysql+pymysql://{user}:{db_pwd}@{host}:{port}/{db_name}"
        try:
            engine = create_engine(url)
            return engine.connect()
        except Exception as e:
            raise e
    
    
    def execute_query(query):
        connection = connect_to_db()
        with connection as con:
            result = con.execute(query)
            return result

    results = execute_query("show tables;")
    print("count = ",results.rowcount)

    output: count =  1298

Upvotes: 1

snakecharmerb
snakecharmerb

Reputation: 55884

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.

Upvotes: 29

aimfeld
aimfeld

Reputation: 3151

If you have constructed a complex query with joins and several conditions, you can count the query results like this:

count_query = query.with_only_columns(func.count(Foo.id))
count = session.execute(count_query).scalar_one()

The idea is to use a copy of the query and replace the selected columns with a single count column.

Upvotes: 5

Related Questions