How to properly join same table multiple times using sqlalchemy core api?

Question

I'm trying to join same table several times, using sqlalchemy core api.

Here is the code:

import sqlparse
import sqlalchemy as sa

meta = sa.MetaData('sqlite:///:memory:')

a = sa.Table(
    'a', meta,
    sa.Column('id', sa.Integer, primary_key=True),
)

b = sa.Table(
    'b', meta,
    sa.Column('id', sa.Integer, primary_key=True),
    sa.Column('x', sa.Integer, sa.ForeignKey(a.c.id)),
    sa.Column('y', sa.Integer, sa.ForeignKey(a.c.id)),
)

meta.create_all()

x = b.alias('x')
y = b.alias('y')

query = (
    sa.select(['*']).
    select_from(a.join(x, a.c.id == x.c.x)).
    select_from(a.join(y, a.c.id == y.c.y))
)

print(sqlparse.format(str(query), reindent=True))

Last statement produces following output:

SELECT *
FROM a
JOIN b AS x ON a.id = x.x,
            a
JOIN b AS y ON a.id = y.y

If I try to execute this query query.execute() I get error:

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) ambiguous column name: main.a.id [SQL: 'SELECT * \nFROM a JOIN b AS x ON a.id = x.x, a JOIN b AS y ON a.id = y.y']

The question is, how can I get rid of , a? If I try to execute:

engine.execute('''
    SELECT *
    FROM a
    JOIN b AS x ON a.id = x.x
    JOIN b AS y ON a.id = y.y
''')

It works fine.

Answer 1

query = (
    sa.select(['*']).
    select_from(a
                .join(x, a.c.id == x.c.x)
                .join(y, a.c.id == y.c.y)
                )
)