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

alias, join, python, sqlalchemy

Solution

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

Problem

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?

Original source