SQLAlchemy关联的子查询类型错误:未定义此子句的布尔值

Question

我需要编写一个Python SQLAlchemy代码来转换这个SQL查询，但我找不到在SQL Alchemy中执行这个查询的有意义的方法。

SELECT *
FROM alpha AS X
WHERE X.index = (
SELECT
MAX(index)
FROM
alpha AS Y
WHERE
X.question = Y.question
AND X.person = Y.person
)

我试过这样做，但我一直得到错误TypeError: Boolean value of this clause is not defined(alpha是一个表(。

from sqlalchemy.orm import aliased
from sqlalchemy import func, and_
X = aliased(alpha)
Y = aliased(alpha)
conn.execute(
select(X).where(
X.c.index
== select(func.max(X.c.index))
.where(
and_(
X.c.question == Y.c.question,
X.c.person == Y.c.person,
)
)
.correlate(X)
)
)

我在这里做错了什么？

Answer 1

我认为您只需要使用scalar_subquery((。据我所知，关联将自动发生，如果SQLAlchemy不能说出你的意思，你只需要correlate。

代码

alpha = Table("alpha", metadata,
Column("id", Integer, primary_key=True),
Column("question", String),
Column("person", String),
Column("index", Integer),)

Base.metadata.create_all(engine)
with Session(engine) as session:
X = aliased(alpha, name='X')
Y = aliased(alpha, name='Y')
q = select(X).where(
X.c.index
== select(func.max(X.c.index))
.where(
and_(
X.c.question == Y.c.question,
X.c.person == Y.c.person,
)
).scalar_subquery()
)
print (q)

SQL

SELECT "X".id, "X".question, "X".person, "X".index 
FROM alpha AS "X" 
WHERE "X".index = (SELECT max("X".index) AS max_1 
FROM alpha AS "Y" 
WHERE "X".question = "Y".question AND "X".person = "Y".person)

这是Postgresql和SQLAlchemy 1.4.31的版本。