sqlalchemy group_by and count

Question

I'm using flask as a python framework with sqlalchemy. The models use the query_property which helps me build queries:

class Person(object):
        query = db_session.query_property()
        ....

persons = Person.query.all()

Each person has a city, state and country where he lives in and I want to gather all results and output something like:

    Country     State       City        Persons(count)
================================================================================
    US      NY      New York    10
    DE      Berlin      Berlin      100

This obviously needs a select count and group by country, state, city. The group by thing works but I dont know how to implement the counting since query property has no select method in which case I would write:

Person.query.select([Person.country, Person.state, Person.city, func.count(Person.id)]).all()

Anyone with an idea how to use query_property with group_by and count?

Answer 1

You can use the add_columns method on Person.query to append computed values:

Person.query.add_columns(func.count(...)).group_by(...).all()

Keep in mind that you get back a list of tuples (not Person objects). However, each tuple contains the Person object and the computed value:

[(<Person object>, 3), (<Person object>, 7), ...]