1

I'm looking for a way to count with SQLAlchemy the number of rows that is returned from a given query (that potentially includes filters) but everything I find on the net makes explicit use of a model (example here). My problem is that I don't have a model, I only have a Table object (because I'm dealing with temporary tables that vary in format from time to time). For the moment I can do the following:

tbl = Table(mytablename,metadata,autoload=True, autoload_with=myengine, schema=myschemaname)
query = select([tbl]) 
filters = build_filters(...) #my function that build filters
query = query.where(and_(*filters))
conn = myengine.connect()
ResultProxy = conn.execute(query)
totalCount = len(ResultProxy.fetchall())

but it's very inefficient. Is there a way to do the count efficiently and without referring to any model?

Improve this question
2
  • Are you looking to do a select count(*) on mytablename without actually querying all the records? Commented Jan 24, 2020 at 17:27
  • I want to count the number of rows returned by a query on a table yes but that query can include filters. I would like to do for example: select count(*) from mytable where temperature>80 and city=Dallas Commented Jan 24, 2020 at 17:32

1 Answer 1

Reset to default
2

Try the SQLAlchemy Core 'count' function documented here. I believe you can attach your filters on to that like you're doing now. So, (not guaranteeing my syntax here, but here's something to start you with)...

query = select([func.count()]).select_from(my_table).where(and_(*filters))
conn = myengine.connect()
ResultProxy = conn.execute(query)
totalCount = ResultProxy.fetchone()[0]

According to the documentation, I believe this will actually generate a SELECT COUNT from the database, not actually bring all the rows back from the DB and then count them.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Sorry but I'm not sure to understand. Did you mean 'count' instead of 'sum'? And could you give an example? I see how to do a count on my table (query = select([func.count()]).select_from(tbl)) but not on a my filtered query.
Sorry about that Patrick, yes, I meant count. I'll modify my answer to show that.
You're welcome, Patrick. Thanks for giving the chance to help!
Actually, your last line should be: totalCount = ResultProxy.fetchone()[0]

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.