Can I use aggregate expressions using SQLAlchemy?

Question

PostgreSQL have aggregate expressions, e.g. count(*) FILTER (WHERE state = 'success'). How can I generate such expressions using SQLAlchemy?

@IljaEverilä Completely different topic. That's about aggregate functions, and this is about aggregate expressions. — minhee
– minhee, Commented Nov 16, 2017 at 8:16
I'd not go so far as to say completely different, but here's a better dupe candidate: stackoverflow.com/questions/46167101/…, also mentioned here. The relevant documentation: funcfilter() and FunctionElement.filter(). — Ilja Everilä
– Ilja Everilä, Commented Nov 16, 2017 at 8:18

bakkal · Accepted Answer · 2017-11-16 11:08:28Z

15

Suppose I have a model Machine with a boolean field active, and would like to filter the count by active = true

Using `func.count(...).filter(...)`

from models import db, Machine
from sqlalchemy.sql import func

query = db.session.query(
    func.count(Machine.id).filter(Machine.active == True)
    .label('active_machines')
)

We can look at the generated SQL query:

>>> print(query)
SELECT count(machine.id) FILTER (WHERE machine.active = true) AS active_machines 
FROM machine

This should work the same for the other aggregate functions like func.avg, func.sum, etc

Longer syntax using `funcfilter(count(...), filter)`

func.count(Machine.id).filter(Machine.active == True) is short hand for:

from sqlalchemy import funcfilter

funcfilter(func.count(Machine.id), Machine.active == True)

edited Nov 16, 2017 at 11:08

answered Nov 16, 2017 at 8:34

bakkal

55.6k12 gold badges136 silver badges113 bronze badges

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

Collectives™ on Stack Overflow

Can I use aggregate expressions using SQLAlchemy?

1 Answer 1

Using `func.count(...).filter(...)`

Longer syntax using `funcfilter(count(...), filter)`

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Using func.count(...).filter(...)

Longer syntax using funcfilter(count(...), filter)

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related

Using `func.count(...).filter(...)`

Longer syntax using `funcfilter(count(...), filter)`