How to use count() in Flask-sqlalchemy

Question

I am assisting a project which uses flask-sqlalchemy.
I would like a db query to count the number of records in the table.

Can I use table.query.filter_by(condition).count() directly?
Or is there anything that I need to add?

Please assist me. I am a begineer. Thanks in advance!

Answer 1

None of the given answers address flask-sqlalchemy specifically, where you would use exactly the example you gave:

Table.query.filter_by(condition).count()

You can perform .count() without filters:

Table.query.count()

You can also count using M2M relationships:

ParentTable.children.count()

And you can use any of these directly in your jinja templates like:

{{ Table.query.filter_by(condition).count() }}

Bonus points (for the performance minded):

.count() is a bit slow (especially with MySQL, thanks to a poor handling of subqueries), so instead, you can use a custom count that looks like this:

db.session.execute(Table.query.filter_by(condition).statement.with_only_columns([func.count()]).order_by(None)).scalar()

That's assuming db is your SQLAlchemy instance (ie, db = SQLAlchemy(app)). It's a mouthful, but it will save you a little bit of overhead on big queries.

Answer 2

Per this source, the following should work for you:

session.query(Class_name).filter_by(condition).count()

Class_name is the name of the mapped class for your table.

Answer 3

Yes, you can do that. But remember, that count() uses one more query, like this:

Select count(q.*) from (select * from table) q

However, you can make it more efficient by using only one query. You can use this:

from sqlalchemy.sql.functions import func
number = session.query(func.count(table.id).label('number').first().number

The SQL query will be like this:

Select count(table.id) as number from table