python - 我可以使用 SQLAlchemy 在查询中运行查询吗？

Question

这个问题在这里已经有了答案:

Generate sql with subquery as a column in select statement using SQLAlchemy (2 个回答)

关闭 5 年前。

可以使用单个 SQLAlchemy session.query 完成以下 MySQL 查询，还是我必须运行第二个 session.query ？如果是，怎么会这样？

Select *, (select c from table2 where id = table1.id) as d from table1 where foo = x

Answer 1

你想要的是 SQLAlchemy 的 subquery 对象。本质上，您像往常一样编写查询，但不是以 .all() 或 .first() 结束查询(就像您通常返回某种直接结果)，您以 .subquery() 结束查询以返回子查询对象。子查询对象基本上生成嵌入在别名中的子查询 SQL，但不运行它。然后您可以在主查询中使用它，SQLAlchemy 将发出必要的 SQL 以在单个操作中执行查询和子查询。

假设我们有以下 student_scores 表:

+------------+-------+-----+
|    name    | score | age |
+------------+-------+-----+
| Xu Feng    |   95  |  25 |
| John Smith |   88  |  26 |
| Sarah Taft |   89  |  25 |
| Ahmed Zaki |   86  |  26 |
+------------+-------+-----|

(忽略可怕的数据库设计)

在这个例子中，我们想要得到一个包含所有学生及其分数的结果集，并按年龄与平均分数相结合。在原始 SQL 中，我们会做这样的事情:

  SELECT ss.name, ss.age, ss.score, sub.average
    FROM student_scores AS "ss"
    JOIN ( SELECT age, AVG(score) AS "average"
             FROM student_scores
         GROUP BY age) AS "sub"
      ON ss.age = sub.age
ORDER BY ss.score DESC

结果应该是这样的:

+------------+-------+-----+---------+
|    name    | score | age | average |
+------------+-------+-----+---------+
| Xu Feng    |   95  |  25 |    92   |
| John Smith |   88  |  26 |    87   |
| Sarah Taft |   89  |  25 |    92   |
| Ahmed Zaki |   86  |  26 |    87   |
+------------+-------+-----|---------+

在SQLAlchemy中，我们可以先自己定义子查询:

from sqlalchemy.sql import func

avg_scores = (
    session.query(
        func.avg(StudentScores.score).label('average'),
        StudentScores.age
    )
    .group_by(StudentScores.age)
    .subquery()
)

现在我们的子查询已经定义好了，但是实际上还没有语句被发送到数据库。尽管如此，我们几乎可以将我们的子查询对象视为另一个表，并编写我们的主查询:

results = (
    session.query(StudentScores, avg_scores)
    .join(avg_scores, StudentScores.age == avg_scores.c.age)
    .order_by('score DESC').all()
)

直到现在才向数据库发出任何 SQL，我们得到与原始子查询示例相同的结果。

话虽如此，您提供的示例实际上非常简单，根本不需要子查询。根据您的关系是如何定义的，SQLAlchemy 可以预先加载相关对象，以便返回的对象:

results = session.query(Table1).filter(Table1.foo == 'x').all()

将有权访问表 2 中的子(或父)记录，即使我们没有在此处请求它——因为直接在模型中定义的关系正在为我们处理。查看"Relationship Loading Techniques"在 SQLAlchemy 文档中了解有关其工作原理的更多信息。