sqlalchemy func.sum() returns None when no rows exist

Question

I've managed to get a query working that sums up all the child rows of the parent class:

subq = db.session.query(Transaction.budget_id, func.sum(Transaction.amount).label("total"), func.count('*').label("count"))
  .group_by(Transaction.budget_id).subquery()

return db.session.query(Budget.ID, Budget.name, Budget.start_date, Budget.end_date, Budget.amount, subq.c.total, subq.c.count)
  .outerjoin(subq, Budget.ID==subq.c.budget_id)
  .order_by(Budget.start_date.desc()).limit(count)

The problem is that it doesn't work when Budget doesn't have any Transaction subclasses. It returns None, which throws a spanner in the works. I want the sum() and count() functions to return 0 instead.

Answer 1

from sqlalchemy.sql.functions import coalesce

...

return db.session.query(Budget.ID, 
                        Budget.name, 
                        Budget.start_date,
                        Budget.end_date,
                        Budget.amount,
                        coalesce(subq.c.total, 0),
                        coalesce(subq.c.count, 0))
      .outerjoin(subq, Budget.ID==subq.c.budget_id)
      .order_by(Budget.start_date.desc()).limit(count)

Answer 2

2022 Answer

I want the sum() and count() functions to return 0 instead.

Based on the answer of @antonio_antuan but using sqlalchemy 1.4 you now can:

from sqlalchemy import func, select

stmt = select(func.coalesce(func.sum(Transaction.amount), 0))

s = session.execute(stmt).scalars().one()

and then same for func.count.