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!
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.
Table.query.filter_by(condition).count() produces: SELECT COUNT(*) AS count_1 FROM ( SELECT table.field1, table.field2, ... table.fieldN WHERE condition) AS anon_1 count in postgres is bad to start with (because of MVC postgres does not keep row counts of tables), but doing it in a subquery triggers the worst-case scenario regularly. Also notice how SQL Alchemy fetches every row in the table to pass to count... Better to use: db.session.query(db.func.count()).filter(condition).scalar()
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.
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
UPDATE: SQLAlchemy 2.0
In SQLAlchemy 2.0, the use of the query object has been deprecated SQLAlchemy 2.0 Documentation. The following code has worked for me (adapted to the OP's question):
with app.app_context():
my_query = db.func.count(Table.id)
number = db.session.execute(my_query).scalar()
In the above, Table.id could also be substituted by a condition; e.g., in my specific case, using class Cafe(), I used the condition: Cafe.location == "Peckham". A suitable condition to be used in the OP's case would depend on the Table class.
Simple is best:
accounts = db.session.execute('select count(id) as c from user').scalar()
