SqlAlchemy Group By

group_sum.py

#!/usr/bin/env python
#-*- coding: utf-8 -*-

from sqlalchemy import create_engine, Column, Integer, String, func
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker


Base = declarative_base()
class StudentInfo(Base):
    __tablename__ = 'stuinfo'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    cls = Column(String)
    cert_count = Column(Integer)

    def __init__(self, name, cls, cert_count):
        self.name = name
        self.cls = cls
        self.cert_count = cert_count


engine = create_engine('sqlite:///:memory:', echo=True)
Base.metadata.create_all(engine)
Session = sessionmaker(engine)
session = Session()


data = [
    [u'学生1', u'A2', 1],
    [u'学生1', u'A1', 4],
    [u'学生1', u'A2', 4],
    [u'学生1', u'A1', 1],
    [u'学生1', u'A1', 5],
    [u'学生1', u'A2', 1]]
reocords = [StudentInfo(*record) for record in data]
session.add_all(reocords)
session.commit()

# sqlalchemy way
rs = session.query(StudentInfo.cls, func.sum(StudentInfo.cert_count)) \
        .group_by(StudentInfo.cls).all()

for row in rs:
    print row[0], row[1]

""" Output
A1 10
A2 6
"""

# sql way
sql = 'select cls, sum(cert_count) from stuinfo group by cls'
rs = engine.execute(sql) 

for row in rs:
    print row[0], row[1]

""" Output
A1 10
A2 6
"""