SQLAlchemy aggregate functions example

results

In [29]: my_sub = db.session.query(TestModel.name, func.count(TestModel.id).label('count')).group_by(TestModel.name).subquery()

In [30]: db.session.query(TestModel, my_sub.c.count).outerjoin(my_sub, TestModel.name==my_sub.c.name).all()
Out[30]: [(<1: Bob>, 1), (<2: Alice>, 2), (<3: Alice>, 2)]

test.py

from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy
from sqlalchemy import func

app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = 'sqlite:///test.db'

db = SQLAlchemy(app)

class TestModel(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String())

    def __repr__(self):
        return "<{}: {}>".format(self.id, self.name)


@app.route('/')
def hello_world():
    return 'Hello World!'


if __name__ == '__main__':
    with app.app_context():
        db.create_all()
        if len(db.session.query(TestModel).all())==0:
            # insert some data, one Bob and two Alices
            bob = TestModel(name="Bob")
            alice = TestModel(name="Alice")
            alice_two = TestModel(name="Alice")

            db.session.add_all([bob, alice, alice_two])
            db.session.commit()

        u = db.session.query(TestModel, func.count(TestModel.id)).group_by(TestModel.name).all()
        for x in u:
            print x