freeze-unemployment.py

import dataset


def main():
    db = dataset.connect('sqlite:////Users/gka/Incubator/eurostat/eurostat.db')

    # common WHERE parameters
    common = dict(sex='T', age='Y_LT25', s_adj='SA')
    # paramters to be added for each join
    split = dict(
        Italy=dict(geo='IT'),
        Germany=dict(geo='DE'),
        Spain=dict(geo='ES'),
        Portugal=dict(geo='PT'),
        Turkey=dict(geo='TR'),
        France=dict(geo='FR'),
        Greece=dict(geo='EL'),
        Cyprus=dict(geo='CY')
    )
    # generate sql query
    sql = join_sql('une_rt_m', 'time', 'value', common, split, order_by='time')
    # freeze to csv
    dataset.freeze(db.query(sql), filename='youth-unemployment.csv')


def join_sql(table, key, value, where, split, order_by=False):
    sql = 'SELECT t0.%s, ' % key
    select_ = []
    t = 0
    for split_key in split:
        select_.append('t%d.%s %s' % (t, value, split_key))
        t += 1
    sql += ', '.join(select_)
    sql += ' FROM '
    t = 0
    sub_queries = []
    for split_key in split:
        query = '(SELECT %s, %s FROM %s WHERE ' % (key, value, table)
        w = dict()
        w.update(where)
        w.update(split[split_key])
        query += ' AND '.join(['%s = "%s"' % (k, v) for k, v in w.iteritems()]) + ') t%d' % t
        if t > 0:
            query += ' ON t%d.%s = t0.%s' % (t, key, key)
        sub_queries.append(query)
        t += 1
    sql += ' LEFT JOIN '.join(sub_queries)
    if order_by:
        dir_ = 'ASC'
        if order_by[0] == '-':
            dir_ = 'DESC'
            order_by = order_by[1:]
        sql += ' ORDER BY t0.%s %s' % (order_by, dir_)
    return sql + ';'


if __name__ == '__main__':
    main()

youth-unemployment.csv