from sqlalchemy import schema, types, create_engine


# Table definitions
metadata = schema.MetaData()

foo = schema.Table(
    'foo', metadata,
    schema.Column('id', types.String, primary_key=True),
    schema.Column('x', types.Integer)
    )

bar = schema.Table(
    'bar', metadata,
    schema.Column('a', types.Integer, primary_key=True),
    schema.Column(
        'id', types.String, schema.ForeignKey(foo.c.id),
        nullable=False)
    )


def setup(conn):
    query = foo.insert()
    conn.execute(query, id='ID', x=777)

    query = bar.insert()
    conn.execute(query, a=0, id='ID')

engine = create_engine('sqlite://')
metadata.create_all(bind=engine)

try:
    setup(engine)
    engine.echo = True

    query = foo.join(bar).select()
    r = engine.execute(query).fetchall()

    print r[0]
    print r[0].keys()
    print r[0]['ID']
finally:
    engine.echo = False
    metadata.drop_all(bind=engine)