Is there a convenient way to alias only conflicting columns when joining tables in SQLAlchemy?

Question

Sometimes it is useful to map a class against a join instead of a single table when using SQLAlchemy's declarative extension. When column names collide, usually in a one-to-many because all primary keys are named id by default, you can use .alias() to prefix every column with its table name. That is inconvenient if you've already written code that assumes your mapped class has non-prefixed names.

For example:

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Table, Column, Integer, ForeignKeyConstraint

Base = declarative_base()

t1 = Table('t1', 
    Base.metadata, 
    Column('id', Integer, primary_key=True))

t2 = Table('t2', 
    Base.metadata, 
    Column('id', Integer, primary_key=True),
    Column('fkey', Integer),
    ForeignKeyConstraint(['fkey'], [t1.c.id]))

class ST(Base):
    __table__ = t1.join(t2)

class ST2(Base):
    __table__ = t1.join(t2).alias()

ST has id, fkey properties with each name mapping to the first table in the join that uses the overridden name, so the mapped class does not expose t2's primary key. ST2 has t1_id, t2_id and t2_fkey properties.

Is there a convenient way to alias only some of the columns from each table in the join so the mapped class exposes the more convenient non-prefixed property names for most mapped columns?

Answer 1

You can create alias for each column separately with its label() method. So it's possible something similar to the following (not tested):

from sqlalchemy import select

def alias_dups(join):
    dups = set(col.key for col in join.left.columns) & \
                set(col.key for col in join.right.columns)
    columns = []
    for col in join.columns:
        if col.key in dups:
            col = col.label('%s_%s' % (col.table.name, col.key))
        columns.append(col)
    return select(columns, from_obj=[join]).alias()

class ST2(Base):
    __table__ = alias_dups(t1.join(t2))