ORM为什么要创建重复连接？

Question

我构建了以下查询：

query = session.query(MatchesATP.ID_M, TournamentsATP.NAME_T)
query = query.join(MatchesATP).join(TournamentsATP).limit(10)

但是，我得到了以下错误：

sqlalchemy.exc.ProgrammingError: (mysql.connector.errors.ProgrammingError) 1066 (42000): Not unique table/alias:

显然，SQLAlchemy正在将ORM查询转换为以下SQL：

SELECT belgarath.matches_atp.`ID_M` AS `belgarath_matches_atp_ID_M`, oncourt.tours_atp.`NAME_T` AS `oncourt_tours_atp_NAME_T`
FROM oncourt.tours_atp INNER JOIN belgarath.matches_atp ON oncourt.tours_atp.`ID_T` = belgarath.matches_atp.`ID_T_M` INNER JOIN oncourt.tours_atp ON oncourt.tours_atp.`ID_T` = belgarath.matches_atp.`ID_T_M`

因此错误

为什么要加入两次

完整代码：

from sqlalchemy import (
    Column, Integer, String, ForeignKey, create_engine
)
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

Session = sessionmaker()
Base = declarative_base()

engine = create_engine("mysql+mysqlconnector://root:root@localhost/")

Base.metadata.reflect(engine, schema="belgarath")
Base.metadata.reflect(engine, schema="oncourt")
Base.metadata.bind = engine

session = Session()


class MatchesATP(Base):
    __tablename__ = "matches_atp"
    __table_args__ = {"schema": "belgarath",
                      "extend_existing": True}

    ID_M = Column(Integer, primary_key=True)
    ID_T_M = Column(Integer, ForeignKey("oncourt.tours_atp.ID_T"))


class TournamentsATP(Base):
    __tablename__ = "tours_atp"
    __table_args__ = {"schema": "oncourt",
                      "extend_existing": True}

    ID_T = Column(Integer, primary_key=True)
    NAME_T = Column(String(255))


query = session.query(MatchesATP.ID_M, TournamentsATP.NAME_T)
query = query.join(MatchesATP).join(TournamentsATP).limit(10)
print(query.statement)
print([result for result in query])

Answer 1

在.select_from()和.join()之间存在差异。您告诉查询执行两个联接，但不是从哪里开始。所以SQLAlchemy通过从select子句中的第一个元素进行选择隐式地实现了这一点。在本例中，您要加入两个表，因此它可能不知道该做什么。要获取以下SQL

SELECT
  belgarath.matches_atp.`ID_M` AS `belgarath_matches_atp_ID_M`,
  oncourt.tours_atp.`NAME_T` AS `oncourt_tours_atp_NAME_T`
FROM oncourt.tours_atp
INNER JOIN belgarath.matches_atp ON oncourt.tours_atp.`ID_T` = belgarath.matches_atp.`ID_T_M`
LIMIT 10

请改用以下代码：

query = session.query(MatchesATP.ID_M, TournamentsATP.NAME_T)
query = query.select_from(MatchesATP).join(TournamentsATP).limit(10)

甚至

query = session.query(MatchesATP.ID_M, TournamentsATP.NAME_T)
query = query.join(TournamentsATP).limit(10)