新手教程系列 -- SQLAlchemy对同一张表联表两次

在开发过程中，我们经常会遇到对同一张表进行多次联表查询的需求。比如在查询航线时，我们希望将起飞和降落的机场名称代入结果中。为了实现这一目标，机场名称统一存放在 AirPort 表中。下面，我们将介绍如何通过 SQLAlchemy 实现这一需求。

问题描述

一般情况我们第一时间会想到这么写：

from sqlalchemy import select
async with get_db_session_async() as session:
    stmt = (
        select(
            AirRoute.dep_airport_code,
            AirPort.airport_en_name.label('dep_airport_name'),
            AirRoute.arr_airport_code,
            AirPort.airport_en_name.label('arr_airport_name'),
            ResourceSite.site_code,
            ResourceSite.site_name,
            ResourceSite.flight_code,
        )
        .join(ResourceSite, AirRoute.resource_site_id == ResourceSite.id, isouter=True)
        .join(AirPort, AirRoute.dep_airport_code == AirPort.airport_code, isouter=True)
        .join(AirPort, AirRoute.arr_airport_code == AirPort.airport_code, isouter=True)
        .limit(1)
    )
    row_set = await session.execute(stmt)
    air_route_rows = [row._asdict() for row in row_set.all()]

然而，运行上面的代码时，会得到一个错误：

OperationalError: (pymysql.err.OperationalError) (1066, "Not unique table/alias: 'air_ports'")

这个错误是由于在同一个查询中多次引用同一张表 AirPort，导致表别名冲突。

解决方案：使用别名（aliased）

为了解决上述问题，我们可以使用 SQLAlchemy 提供的 aliased 方法。通过为同一张表创建不同的别名，可以避免表别名冲突。

• 注意：不同版本的 SQLAlchemy 可能 aliased 存放的包路径不一样，具体请以您使用的 SQLAlchemy 版本为准

from sqlalchemy import select
from sqlalchemy.orm import aliased

async with get_db_session_async() as session:
    dep_airports_table = aliased(AirPort, name='dep_airports_table')
    arr_airports_table = aliased(AirPort, name='arr_airports_table')
    stmt = (
        select(
            AirRoute.dep_airport_code,
            dep_airports_table.airport_en_name.label('dep_airport_name'),
            AirRoute.arr_airport_code,
            arr_airports_table.airport_en_name.label('arr_airport_name'),
            ResourceSite.site_code,
            ResourceSite.site_name,
            ResourceSite.flight_code,
        )
        .join(ResourceSite, AirRoute.resource_site_id == ResourceSite.id, isouter=True)
        .join(dep_airports_table, AirRoute.dep_airport_code == dep_airports_table.airport_code, isouter=True)
        .join(arr_airports_table, AirRoute.arr_airport_code == arr_airports_table.airport_code, isouter=True)
        .limit(1)
    )
    row_set = await session.execute(stmt)
    air_route_rows = [row._asdict() for row in row_set.all()]

通过为 AirPort 表生成两个别名对象 depairportstable 和 arrairportstable，我们可以分别进行联表查询，避免了别名冲突问题。

最终结果

经过修改，我们成功对 AirPort 表生成了两个别名对象，并通过这两个对象进行联表查询，得到了正确的结果：

[{'dep_airport_code': 'ADB', 'dep_airport_name': 'Adnan Menderes Airport', 'arr_airport_code': 'SIN', 'arr_airport_name': 'Singapore Changi Airport', 'site_code': 'xxx', 'site_name': 'Example Site', 'flight_code': 'XX123'}]

通过以上步骤，我们解决了 SQLAlchemy 对同一张表进行多次联表查询时的别名冲突问题，使查询结果更加准确和直观。

结论

在开发中，对同一张表进行多次联表查询是一个常见需求。通过使用 SQLAlchemy 的 aliased 方法，可以有效避免表别名冲突问题，从而实现预期的查询结果。希望这篇教程能帮助你更好地理解和应用 SQLAlchemy 进行复杂查询。

关注【程序员的开发手册】，让您少走十年弯路！