How to specify columns with joined anonymously aliased tables in SqlAlchemy

I'm having trouble figuring out the correct syntax to use to properly specify columns when querying joined tables that are anonymously aliased using SqlAlchemy's generative syntax.

The schema has 2 tables, one-to-many:

user = Table('user', meta,
    Column('id', Integer, primary_key=True, autoincrement = False),
    Column('name', String),
    Column('department_id', Integer, ForeignKey('department.id'))
)

department = Table('department', meta,
    Column('id', Integer, primary_key=True, autoincrement = True),
    Column('name', String)
)

A single table query works:

# alias the tables anonymously
u = user.alias()
d = department.alias()

# single table select works
q = select开发者_开发技巧([
    u.c.id.label('UserId'),
    u.c.name.label('User'),
    ])
r = conn.execute(q).fetchall()
log.debug(r[0].keys())
# [u'UserId', u'User']

But when joining the aliased tables, they're renamed, and I don't know what the new names will be, so I can't specify the columns:

# join the 2 aliased tables
from_ = u.join(d)
log.debug(from_.c.keys())
# [u'%(172847020 user)s_id', u'%(172847020 user)s_name', u'%(172847020 user)s_department_id', u'%(172846668 department)s_id', u'%(172846668 department)s_name']

# this does not work
q = select([
    from_.c.user_id.label('UserId'),
    from_.c.user_name.label('User'),
    from_.c.department_name.label('Department'),
    ])
r = conn.execute(q).fetchall()
log.debug(r[0].keys())

How can I get this to work?

Here is the complete test code:

import logging
from sqlalchemy import create_engine, Table, Column, Integer, String, MetaData, ForeignKey
from sqlalchemy.sql import *

log = logging.getLogger('generative_test')
logging.basicConfig(level=logging.DEBUG,
        format='%(asctime)s,%(msecs)03d %(levelname)s [%(filename)s.%(funcName)s @ %(lineno)d.%(thread)d] %(message)s')

engine = create_engine('sqlite:///:memory:', echo = False, echo_pool = False)
meta = MetaData()
meta.bind = engine

user = Table('user', meta,
    Column('id', Integer, primary_key=True, autoincrement = False),
    Column('name', String),
    Column('department_id', Integer, ForeignKey('department.id'))
)

department = Table('department', meta,
    Column('id', Integer, primary_key=True, autoincrement = True),
    Column('name', String)
)

meta.create_all(engine)
conn = engine.connect()

conn.execute(department.insert(),[
    {'name':'bosses'},
    {'name':'peons'},
])
conn.execute(user.insert(),[
    {'name':'Mr. Slate','department_id':1},
    {'name': 'Fred','department_id':2},
    {'name': 'Barney','department_id':2},
])

# alias the tables anonymously
u = user.alias()
d = department.alias()

# single table select works
q = select([
    u.c.id.label('UserId'),
    u.c.name.label('User'),
    ])
r = conn.execute(q).fetchall()
log.debug(r[0].keys())
# [u'UserId', u'User']

# join the 2 aliased tables
from_ = u.join(d)
log.debug(from_.c.keys())
# [u'%(172847020 user)s_id', u'%(172847020 user)s_name', u'%(172847020 user)s_department_id', u'%(172846668 department)s_id', u'%(172846668 department)s_name']

# this does not work
q = select([
    from_.c.user_id.label('UserId'),
    from_.c.user_name.label('User'),
    from_.c.department_name.label('Department'),
    ])
r = conn.execute(q).fetchall()
log.debug(r[0].keys())

---

q = select([
    u.c.id.label('UserId'),
    u.c.name.label('User'),
    d.c.name.label('Department'),
], from_obj=u.join(d))