Converting flat SQL output to a JSON array of hashes in Python

getladata.py

#!/usr/bin/env python

import json
from itertools import izip, cycle, groupby
from operator import itemgetter, attrgetter

# Imagine this is the result of some DB query like:
# SELECT region, year, value FROM some_table ORDER BY region, year
data = [
    ('Southwark',  2009, 71.89),
    ('Southwark',  2010, 72.89),
    ('Southwark',  2011, 78.1),
    ('Wandsworth', 2009, 72.89),
    ('Wandsworth', 2010, 69.89),
    ('Wandsworth', 2011, 75.1),
    ]

# List of colors to assign to regions. We'll use itertools cycle to wrap these
# around infinitely (although there's only two regions in the example here
# so you won't see it)
colors = [
    'red',
    'green',
    'blue',
    'purple',
    'yellow',
    'cyan',
    ]

# We'll use json.dumps to generate JSON and print it to stdout like a good
# little CGI app. As for re-organizing the data, we'll use itertools groupby
# (which assumes the data is sorted by the grouping key - hence the inclusion
# of an ORDER BY clause in our fake SQL above), and operator itemgetter to
# extract the first element of each row (the region). If your DB API provides
# namedtuples instead of basic tuples you could make the code a bit more
# readable by replacing itemgetter(0) with something like attrgetter('region')
print json.dumps([
    {
        'key': region,
        'color': color,
        'values': [{'x': year, 'y': value} for (_, year, value) in values]
        }
    for ((region, values), color)
    in izip(groupby(data, itemgetter(0)), cycle(colors))
    ])