adapt_on_names demo

test.py

from sqlalchemy import Column
from sqlalchemy import create_engine
from sqlalchemy import Integer
from sqlalchemy import MetaData
from sqlalchemy import select
from sqlalchemy import String
from sqlalchemy import Table
from sqlalchemy.orm import aliased
from sqlalchemy.orm import declarative_base
from sqlalchemy.orm import Session
Base = declarative_base()


class A(Base):
    __tablename__ = 'a'

    id = Column(Integer, primary_key=True)
    data = Column(String)


not_a_at_all = Table("a", MetaData(), Column("id"), Column("data"))

e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)

s = Session(e)

s.add(A(data="foo"))
s.add(A(data="bar"))
s.commit()

# "not_a_at_all" is a totally unrelated Table object with matching col names
na1 =not_a_at_all.alias("a")
na2 =not_a_at_all.alias("b")

aa = aliased(A, na1, adapt_on_names=True)
bb = aliased(A, na2, adapt_on_names=True)

stmt = select(
    aa, bb
).outerjoin_from(aa, bb, aa.id < bb.id)

print(s.execute(stmt).all())