Get total record count for SqlAlchemy query result, which uses pagination(limit, offset)

user_6663455 0
Viewed 1008

I am using sqlalchemy query with limit and offset. Now I need to get the total count of the query result.

For now I am using count query and limit query separately and getting the results. Is there any efficient way to get the count in a single sqlalchemy query.

Here is the sample of currently what I am using,

# getting total count
docs_count = DB_SESSION.query(Documents).count()

# using limit and offset
docs_list = DB_SESSION.query(Documents).limit(10).offset(0)

Now is it possible to combine this into a single query, or there any efficient method to do this. Thanks all.

sqlalchemy mysql python
1 Answers

I was facing the same issue. What I did is to add the count operation into the query through an 'over' clause as follow:

from sqlalchemy.sql import func

docs_query = DB_SESSION.query(Documents,func.count(Documents.id).over().label("total"))
docs_query = docs_query.limit(10).offset(0)
docs_results = docs_query.all()
if len(docs_results) > 0 :
    docs_list= docs_results[0]
    docs_count= docs_results[0][1]

SQLAlchemy info :

I don't know if it is the best way to do it but it is a bit faster than what you have done. Hope it will help you or someone else.

user_14318640
Related