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

Question

It is sometimes useful to map a class to joininstead of a single table when using the SQLAlchemy declarative extension. When column names collide, usually one-to-many, because by default all primary keys have a name id, you can use .alias()to prefix each column with its table name. This is inconvenient if you have already written code assuming that your mapped class has non-prefix 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 id, fkey , , t2. ST2 t1_id, t2_id t2_fkey.

join, ?

Answer 1

label(). , - ( ):

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))