machine-envy

If your database doesn’t support pivots, here is a quick technique to get pivot columns with sqlalchemy

import operator
from sqlalchemy.sql import case, func, select

def pivot_report(report, pivot_on=None, pivot_columns=None, pivot_func=func.sum,
                    non_pivot_columns=None, group_by=None):
    """ produce a pivot on a select

    if we have a report: 

        id, type, count
        1, white, 10
        1, black, 20
        2, white, 12
        2, black, 20

    and we want 

        id, black count, white count
        1, 20, 10
        2, 20, 12

    pass in type as the pivot_on, and [count] as pivot columns  

    """

    # find all possible values of the pivot
    pivot_values = map(
        operator.itemgetter(0),
        select([pivot_on], from_obj=[report]).distinct().execute()
    )

    # build the new pivot columns
    new_columns = [
        pivot_func(case([(pivot_on == value, column)])).label("%s %s" % (value, column))
        for value in pivot_values
        for column in pivot_columns
    ]

    return select(
        non_pivot_columns + new_columns,
        from_obj=[report],
        group_by=group_by
    )

# example code
from sqlalchemy import Table, Column, Integer, String, MetaData
from sqlalchemy import create_engine

metadata = MetaData()
example = Table('example', metadata,
    Column('id', Integer),
    Column('type', String),
    Column('count', Integer),
)

engine = create_engine('sqlite:///:memory:')
metadata.bind = engine
example.create()
example.insert().execute(
    dict(id=1, type='white', count=10),
    dict(id=1, type='black', count=20),
    dict(id=2, type='white', count=30),
    dict(id=2, type='black', count=40),
)

report = example.select()

# now build the pivot
report = pivot_report(
    report,
    pivot_on=report.c.type,
    pivot_columns=[report.c.count],
    non_pivot_columns=[report.c.id],
    group_by=[report.c.id])

for r in report.execute():
    print r.items()