SQLAlchemy - how to get raw SQL of `.count()` queries?

Question

The simplest possible way to get "raw" SQL for any query is just print it (actually, convert it to str).

But, this is not working for count() queries because count() is "firing" method - a method which is stated as "This results in an execution of the underlying query". Other "firing" methods include all(), first() and so on.

How to get SQL for such methods?

I'm especially interested in count() because it transforms underlying query in some way (actually, this way is described explicitly in docs, but things may vary). Other methods can alter resulting SQL as well, for example, first().

So, sometimes it is useful to get raw SQL of such queries in order to investigate how thing goes under the hood.

I read answers about "getting raw SQL" but this case is special because such methods don't return Query objects.

Note that I mean that I need a SQL of existing Query objects which are already constructed in some way.

Maybe using func.count() might work instead of query.count()? — André C. Andersen
– André C. Andersen, Commented Feb 11, 2019 at 14:52
Did you mean func.count(query)? If so, then no, it doesn't work. It even produces malformed SQL which can't be executed. — Anthony
– Anthony, Commented Feb 11, 2019 at 15:05

André C. Andersen · Accepted Answer · 2019-02-11 16:51:35Z

2

The following example will return a count of any query object, which you should then be able to convert to a string representation:

from sqlalchemy import func

...

existing_query = session.query(Something)\
    .join(OtherThing)\
    .filter(OtherThing.foo = 'FOO')\
    .subquery()

query = session.query(func.count(existing_query.c.bar).label('bar_count'))

print(query)

actual_count = query.as_scalar()  # Executes query

Notice that you have to specify a field from the query output to count. In the example defined by existing_query.c.bar.

edited Feb 11, 2019 at 16:51

answered Feb 11, 2019 at 14:56

André C. Andersen

9,5053 gold badges59 silver badges84 bronze badges

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

2 Comments

Anthony Over a year ago

I've already some query object - it has filters, joins, etc. I want to get SQL of it. Such Query object is not equivalent to sess.query(func.count(Model.id).label('some_count')).

André C. Andersen Over a year ago

@tosh you can at any time change your existing query to a subquery by calling query.subquery(). Then it is just a matter of counting one of the fields in it. See edit. That said. Next time you should follow Stack Overflows MCVE guidelines: It is hard to guess every contingency in your particular case without knowing exactly what you are trying to do.

Collectives™ on Stack Overflow

SQLAlchemy - how to get raw SQL of `.count()` queries?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related