Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Aggregating with SQLAlchemy's SQLExpression. Getting total of price, number of sales, piechart, linechart and ranking.
# -*- 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()
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment