Aggregating with SQLAlchemy's SQLExpression. Getting total of price, number of sales, piechart, linechart and ranking.

aggregation_with_sqlexpressions.py

# -*- coding: utf-8 -*-

from sqlalchemy import (
    create_engine,
    Table,
    Column,
    Integer,
    String,
    MetaData,
    ForeignKey,
    DateTime,
    func,
    select,
)

engine = create_engine('sqlite:///:memory:', echo=True)
conn = engine.connect()
metadata = MetaData()

categories = Table('categories', metadata,
    Column('id', Integer, primary_key=True),
    Column('name', String),
)
items = Table('items', metadata,
    Column('id', Integer, primary_key=True),
    Column('name', String),
    Column('price', Integer),
    Column('category_id', None, ForeignKey('categories.id')),
)
histories = Table('histories', metadata,
    Column('id', Integer, primary_key=True),
    Column('item_id', None, ForeignKey('items.id')),
    Column('sold_datetime', DateTime)
)

metadata.create_all(engine)

conn.execute(categories.insert(), [
    {'id': '1', 'name': u'ボディ'},
    {'id': '2', 'name': u'ウィッグ'},
    {'id': '3', 'name': u'アイ'},
])
conn.execute(items.insert(), [
    {'id': 1, 'name': u'DD', 'price': 20000, 'category_id': 1},
    {'id': 2, 'name': u'DD-2', 'price': 23000, 'category_id': 1},
    {'id': 3, 'name': u'ショートヘア', 'price': 1000, 'category_id': 2},
    {'id': 4, 'name': u'ロングヘア', 'price': 2000, 'category_id': 2},
    {'id': 5, 'name': u'グラスアイ', 'price': 5000, 'category_id': 3},
    {'id': 6, 'name': u'アクリルアイ', 'price': 1000, 'category_id': 3},
])
import datetime
conn.execute(histories.insert(), [
    {'item_id': 1, 'sold_datetime': datetime.datetime(2012, 1, 1, 0, 0, 0)},
    {'item_id': 1, 'sold_datetime': datetime.datetime(2012, 1, 10, 12, 0, 0)},
    {'item_id': 1, 'sold_datetime': datetime.datetime(2012, 2, 21, 21, 0, 0)},
    {'item_id': 2, 'sold_datetime': datetime.datetime(2012, 1, 4, 8, 0, 0)},
    {'item_id': 2, 'sold_datetime': datetime.datetime(2012, 2, 1, 1, 0, 0)},
    {'item_id': 3, 'sold_datetime': datetime.datetime(2012, 1, 23, 1, 0, 0)},
    {'item_id': 3, 'sold_datetime': datetime.datetime(2012, 2, 14, 22, 0, 0)},
    {'item_id': 4, 'sold_datetime': datetime.datetime(2012, 2, 11, 4, 0, 0)},
    {'item_id': 5, 'sold_datetime': datetime.datetime(2012, 1, 11, 9, 0, 0)},
    {'item_id': 5, 'sold_datetime': datetime.datetime(2012, 1, 21, 2, 0, 0)},
    {'item_id': 6, 'sold_datetime': datetime.datetime(2012, 1, 18, 11, 0, 0)},
    {'item_id': 6, 'sold_datetime': datetime.datetime(2012, 2, 13, 23, 0, 0)},
])

jan = (datetime.datetime(2012, 1, 1), datetime.datetime(2012, 2, 1))

for s in (
    select([func.count()],
           histories.c.sold_datetime.between(*jan)),
    select([func.sum(items.c.price)],
           histories.c.sold_datetime.between(*jan) & \
           (items.c.id == histories.c.item_id)),
    select([categories.c.name, func.sum(items.c.price)],
           histories.c.sold_datetime.between(*jan) & \
           (items.c.category_id == categories.c.id) & \
           (items.c.id == histories.c.item_id)).\
           group_by(categories.c.id),
    select([items.c.name, func.sum(items.c.price).label('total_price')],
           histories.c.sold_datetime.between(*jan) & \
           (items.c.id == histories.c.item_id)).\
           group_by(items.c.id).\
           order_by('total_price'),
    select([func.date(histories.c.sold_datetime).label('sold_date'),
           func.sum(items.c.price)]).\
           group_by('sold_date'),
    select([func.strftime('%Y-%m', histories.c.sold_datetime).label('sold_date'),
           func.sum(items.c.price)]).\
           group_by('sold_date')
    ):
    print conn.execute(s).fetchall()