'SQLAlchemy appends aliased Table in FROM clause

I'm creating an application with a flask backend and I'm using SQLAlchemy Core to create the SQL-Queries. Now I'm creating a base select like this:

SYSTEM              = self.__TABLES.SYSTEM
RESPONSIBLE         = self.__TABLES.responsible
INFRA_RESPONSIBLE   = RESPONSIBLE.alias('INFRA_RES')
SYSTEM_RESPONSIBLE  = RESPONSIBLE.alias('SYS_RES')

STMT = SYSTEM \
        .outerjoin(INFRA_RESPONSIBLE, INFRA_RESPONSIBLE.c.id == SYSTEM.c.infrares) \
        .outerjoin(SYSTEM_RESPONSIBLE, SYSTEM_RESPONSIBLE.c.id == SYSTEM.c.sysres)

Now, if I'm creating a SQL-Query like this:

 STMT = select([SYSTEM]) \
        .select_from(STMT) \
        .where(INFRA_RESPONSIBLE.c.id == 1)

This is how the generated looks like:

SELECT "it".systems.*
    FROM "it".responsible AS "INFRA_RES", "it".systems
    LEFT OUTER JOIN "it".responsible as "INFRA_RES" ON "INFRA_RES".id = "it".systems.infrares
    LEFT OUTER JOIN "it".responsible as "SYS_RES" ON "SYS_RES".id = "it".systems.sysres
    WHERE "INFRA_RES".id = 1

But i want to look it like this:

SELECT "it".systems.*
    "it".systems
    LEFT OUTER JOIN "it".responsible as "INFRA_RES" ON "INFRA_RES".id = "it".systems.infrares
    LEFT OUTER JOIN "it".responsible as "SYS_RES" ON "SYS_RES".id = "it".systems.sysres
    WHERE "INFRA_RES".id = 1

The table INFRA_RES is already joined with the base statement, how can I achieve to build a proper SQL-Statement?



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source