Peewee: Querying SUM of field/column

Question

I've been trying to get the sum total of a field/column in peewee. I thought it would be straightforward, but I've been going around in circles for a couple of hours now.

All I'd like to get back from the query is sum total of the price field/column.

An example of the code I've been using is:

Model

class Package(db.Model):
    id = PrimaryKeyField()
    code = CharField(max_length=11, unique=True, null=False)
    price = DecimalField(null=False, decimal_places=2)
    description = TextField()
    created = DateTimeField(default=datetime.now, null=False)
    updated = DateTimeField(default=datetime.now, null=False)

Query

    sum_total = fn.SUM(Package.price).alias('sum_total')
    query = (Package
        .select(sum_total)
        .order_by(sum_total)
    )

The outputs I'm getting are:

query.sum_total

AttributeError: 'ModelSelect' object has no attribute 'sum_total'

for q in query: logger.debug(json.dumps(model_to_dict(q)))

{"code": null, "created": null, "description": null, "id": null, "numberOfTickets": null, "price": null, "updated": null}

I've sure I'm missing something really simple. I haven't been able to find any examples outside of the peewee documentation, and I've tried those, but am still getting nowhere.

Any ideas?

Answer 1

The "model_to_dict()" method is not magic. It does not automatically infer that you want to actually just dump the "sum_total" column into a dict. Additionally, are you trying to get a single sum total for all rows in the db? If so this is just a scalar value, so you can write:

total = Package.select(fn.SUM(Package.price)).scalar()
return {'sum_total': total}

If you want to group totals by some other columns, you need to select those columns and specify the appropriate group_by() - for example, this groups sum total by code:

sum_total = fn.SUM(Package.price).alias('sum_total')
query = (Package
         .select(Package.code, sum_total)
         .group_by(Package.code)
         .order_by(sum_total))
accum = []
for obj in query:
    accum.append({'code': obj.code, 'sum_total': obj.sum_total})