SQLAlchemy：scalars与intersect结合无法返回ORM对象的问题咨询

SQLAlchemy手册明确Session.scalars()方法应返回完整ORM对象，但实际测试发现，当查询语句引入intersect()集合操作后，该方法仅返回目标对象的第一列数据；未使用intersect()的查询则正常返回完整ORM对象。

目前可通过先查询主键再二次查询获取ORM对象，但操作繁琐，寻求更优雅的解决方案。

测试代码 #

from sqlalchemy import create_engine, select, intersect
from sqlalchemy.orm import Mapped, mapped_column, DeclarativeBase, Session


class Base(DeclarativeBase):
    pass


class Movie(Base):
    __tablename__ = "movie"

    title: Mapped[str]
    id: Mapped[int] = mapped_column(primary_key=True)

    def __repr__(self):
        return (
            f"{self.__class__.__qualname__}("
            f"title={self.title!r}, "
            f"id={self.id!r})"
        )


engine = create_engine("sqlite+pysqlite:///:memory:")
Base.metadata.create_all(engine)
with Session(engine) as session:
    movie_1 = Movie(title="Great Movie 1")
    movie_2 = Movie(title="Great Movie 2")
    session.add_all((movie_1, movie_2))

    statement = select(Movie).where(Movie.title == "Great Movie 1")
    print("\n", statement)
    result = session.scalars(statement).all()
    print(f"{result=}")

    stmt_isec = intersect(statement)
    # 以下语句和上面的一元intersect效果一致
    # stmt_isec = intersect(*[statement, statement])
    print("\n", stmt_isec)
    result = session.scalars(stmt_isec).all()
    print(f"{result=}")

输出结果 #

SELECT movie.title, movie.id 
FROM movie 
WHERE movie.title = :title_1
result=[Movie(title='Great Movie 1', id=1)]

SELECT movie.title, movie.id 
FROM movie 
WHERE movie.title = :title_1 INTERSECT SELECT movie.title, movie.id 
FROM movie 
WHERE movie.title = :title_1
result=['Great Movie 1']

使用场景 #

假设有一个五列的表，终端用户通过包含五个字段的搜索表单输入查询条件（例如仅输入第一列和第四列的条件），期望返回同时匹配这些条件的记录。若直接构建查询语句，五列情况下需编写120种（5的阶乘）不同语句。因此采用为用户输入条件的每一列构建单独查询语句，再通过SQLAlchemy的intersect()函数组合的方案。

优雅解决方案 #

方法1：用from_statement显式映射ORM对象 #

将intersect()生成的查询语句作为参数传入select(Movie).from_statement()，让SQLAlchemy将查询结果映射为完整ORM对象：

stmt_isec = intersect(statement)
result = session.scalars(select(Movie).from_statement(stmt_isec)).all()

方法2：子查询查主键后关联查询 #

优化二次查询的写法，先通过intersect()查询主键集合，再用主键查询完整ORM对象，性能更优：

# 子查询仅获取主键
stmt_isec = intersect(select(Movie.id).where(Movie.title == "Great Movie 1"))
# 通过主键IN子查询获取完整对象
result = session.scalars(select(Movie).where(Movie.id.in_(stmt_isec))).all()

方法3：执行查询时指定映射实体 #

通过session.execute执行集合查询后，调用scalars()并指定要映射的模型类：

stmt_isec = intersect(statement)
result = session.execute(stmt_isec).scalars(Movie).all()

原因说明 #

intersect()属于SQL集合操作，返回的是原始行数据而非ORM实体结构。SQLAlchemy无法自动识别这些行对应的模型类，因此需要显式指定映射规则，才能返回完整的ORM对象。

内容的提问来源于stack exchange，提问作者lemi57ssss