from sqlalchemy import *

import logging
logging.basicConfig()
#logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)

engine = create_engine('sqlite:///:memory:')
metadata = BoundMetaData(engine)

departments = Table('departments', metadata,
                    Column('department_id', Integer, primary_key=True),
                    Column('name', String(50)))

employees = Table('employees', metadata, 
                  Column('person_id', Integer, primary_key=True),
                  Column('name', String(50)),
                  Column('department_id', Integer,
                         ForeignKey('departments.department_id')))

def dump(text, query):
    print text + '\n' + ('-' * len(text))
    print query
    print

def test():
    inner = select([departments.c.department_id],
                   employees.c.department_id == departments.c.department_id)
    inner = inner.alias('filtered_departments')
    dump("Initial inner query", inner)

    join = inner.join(departments,
                      onclause=inner.c.department_id==departments.c.department_id)
    dump("Inner query after join", inner)
    dump("Join", join)

    outer = select([departments.c.name],
                   from_obj=[join],
                   use_labels=True)
    dump("Outer query", outer)

    dump("Inner query after creating outer query", inner)


if __name__ == '__main__':
    test()