SQLAlchemy要求查询具有别名，但生成的SQL中未使用该别名

Question

我有一个简单的模型类，它代表两个角色之间的战斗：

class WaifuPickBattle(db.Model):
    """Table which represents a where one girl is chosen as a waifu."""

    __tablename__ = "waifu_battles"
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey("users.id"), nullable=False)
    date = db.Column(db.DateTime, nullable=False)
    winner_name = db.Column(db.String, nullable=False)
    loser_name = db.Column(db.String, nullable=False)

我有一个方法，它构造了一个CTE，它将战斗投射到一系列表象中（每一场战斗有两个表象——胜利者和失败者）：

^{pr2}$

然后我有一个查询，利用这个视图来确定经历过最多战斗的角色：

def query_most_battled_waifus():
    """Find the waifus with the most battles in a given date range."""
    appearence_cte = get_battle_appearences_cte()
    query = \
        select([
            appearence_cte.c.name,
            func.sum(appearence_cte.c.was_winner).label("wins"),
            func.sum(appearence_cte.c.was_loser).label("losses"),
        ])\
        .group_by(appearence_cte.c.name)\
        .order_by(func.count().desc())\
        .limit(limit)
    return db.session.query(query).all()

这将生成以下SQL：

WITH battle_appearence  AS
(
    SELECT
        waifu_battles.date AS date,
        waifu_battles.winner_name AS name,
        1 AS was_winner,
        0 AS was_loser
    FROM waifu_battles
    UNION ALL
    SELECT
        waifu_battles.date AS date,
        waifu_battles.loser_name AS name,
        0 AS was_winner,
        1 AS was_loser
    FROM waifu_battles
)
SELECT
    name AS name,
    wins AS wins,
    losses AS losses
FROM
(
    SELECT
        battle_appearence.name AS name,
        sum(battle_appearence.was_winner) AS wins,
        sum(battle_appearence.was_winner) AS losses
    FROM battle_appearence
    GROUP BY battle_appearence.name
    ORDER BY count(*) DESC
)

在对SQLite数据库执行时，这一点非常好，但是在Postgres SQL数据库上执行时，会出现以下错误：

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.SyntaxError) subquery in FROM must have an alias

LINE 6: FROM (SELECT battle_appearence.name AS name, count(battle_ap... ^ HINT: For example, FROM (SELECT ...) [AS] foo.

[SQL: WITH battle_appearence AS (SELECT waifu_battles.date AS date, waifu_battles.winner_name AS name, 1 AS was_winner, 0 AS was_loser FROM waifu_battles UNION ALL SELECT waifu_battles.date AS date, waifu_battles.loser_name AS name, 0 AS was_winner, 1 AS was_loser FROM waifu_battles) SELECT name AS name, wins AS wins, losses AS losses FROM (SELECT battle_appearence.name AS name, count(battle_appearence.was_winner) AS wins, count(battle_appearence.was_winner) AS losses FROM battle_appearence GROUP BY battle_appearence.name ORDER BY count(*) DESC)] (Background on this error at: http://sqlalche.me/e/f405)

在这一点上有几点需要注意：

1. sub select是多余的，我们应该简单地使用sub select作为主select语句。在
2. 您可以通过给sub select加上别名并在主select语句中使用<alias>.<column>来解决这个问题—Postgres需要子选择的别名在其他地方有很好的文档记录。在

我的第一个问题是如果SQLalchemy决定引入它，尽管没有明确指示（据我所知），我将如何对这个子选择进行别名？在

我发现一个解决问题的方法是在查询中添加.alias("foo")：

query = query\
        ...\
        .alias("foo")

这将导致生成以下SQL（一个奇怪地解决了整个冗余子选择问题的SQL！）公司名称：

WITH battle_appearence  AS
(
    SELECT
        waifu_battles.date AS date,
        waifu_battles.winner_name AS name,
        1 AS was_winner,
        0 AS was_loser
    FROM waifu_battles
    UNION ALL
    SELECT
        waifu_battles.date AS date,
        waifu_battles.loser_name AS name,
        0 AS was_winner,
        1 AS was_loser
    FROM waifu_battles
)
SELECT
    battle_appearence.name,
    sum(battle_appearence.was_winner) AS wins,
    sum(battle_appearence.was_winner) AS losses
FROM battle_appearence
GROUP BY battle_appearence.name
ORDER BY count(*) DESC

我的第二个问题是为什么添加别名会阻止创建子选择，为什么不使用别名！似乎忽略了"foo"别名，但对生成的查询有实质性的影响。在

Answer 1

答案

SQLalchemy decides to introduce it despite not being explicitly instructed to

它没有。{1}你可能没有意识到这个时刻。请改用db.session.execute(query)。在

why did adding the alias prevent the sub-select from being created and why is the alias not used! The "foo" alias was seemingly disregarded yet had a substantial effect on the generated query.

它曾经没有，而且它是使用的。在

说明-简介

炼金术刚刚骗了你。我猜你一直在用print(query)来窥视引擎盖下的东西，明白什么是错的——这次运气不好，它没有告诉你全部真相。在

要查看生成的真实SQL，turn the echo functionality on在引擎中。完成后，您将发现，实际上，sqlalchemy生成了以下查询：

WITH battle_appearence AS 
(
    SELECT
        waifu_battles.date AS date,
        waifu_battles.winner_name AS name,
        1 AS was_winner,
        0 AS was_loser 
    FROM waifu_battles
    UNION ALL
    SELECT
        waifu_battles.date AS date,
        waifu_battles.loser_name AS name,
        0 AS was_winner,
        1 AS was_loser 
    FROM waifu_battles
)
SELECT foo.name AS foo_name, foo.wins AS foo_wins, foo.losses AS foo_losses 
FROM (
    SELECT
        battle_appearence.name AS name,
        sum(battle_appearence.was_winner) AS wins,
        sum(battle_appearence.was_loser) AS losses 
    FROM battle_appearence
    GROUP BY battle_appearence.name
    ORDER BY count(*) DESC
    LIMIT ?
)
AS foo

这两个查询都能正常工作（我声称是在上面真正使用过的查询和您在答案末尾给出的查询）。让我们先来探讨一下-为什么这些不同？在

如何调试查询以及所看到的不同之处

您看到的查询（我们称之为select over alias）是查询的字符串表示或str(query.compile())的结果。您可以将其调整为使用postgres方言：

^{pr2}$

得到一个稍微不同的结果，但仍然没有子查询。很有趣，不是吗？仅供将来参考，query.compile与调用dialect.statement_compiler(dialect, query, bind=None)相同（简化）

当调用db.session.query(query).all()时，生成第二个查询（我们将其称为A作为别名）。如果您只需输入str(db.session.query(query))，您将看到我们得到了一个不同的查询（与N的query.compile()）不同——有一个子查询和一个别名。在

这和会议有什么关系吗？否-可以通过将查询转换为Query对象来检查是否忽略会话信息：

from sqlalchemy.orm.query import Query
str(Query(query))

窥视一下实现细节（Query.__str__），我们可以看到A的情况是：

context = Query(query)._compile_context()
str(context.statement.compile(bind=None))

context.statement.compile将尝试选择一种方言（在我们的例子中正确识别postgre），然后以与S变体相同的方式执行语句：

dialect.statement_compiler(dialect, context.statement, bind=None)

为了提醒我们自己，S来源于：

dialect = postgresql.dialect()
str(dialect.statement_compiler(dialect, query, bind=None))

这暗示我们，在上下文中有一些东西改变了语句编译器的行为。dialect.statement_compiler是做什么的，？它是SQLCompiler子类的构造函数，专门处理继承过程以匹配您的方言需求；对于Postgres，它应该是PGCompiler。在

注意：我们可以走捷径到a：

dialect.statement_compiler(dialect, Query(query).statement, bind=None)

让我们比较一下编译对象的状态。这可以通过访问编译器的__dict__属性轻松完成：

with_subquery = dialect.statement_compiler(dialect, context.statement, bind=None)
no_subquery = dialect.statement_compiler(dialect, query, bind=None)
from deepdiff import DeepDiff 
DeepDiff(sub.__dict__, nosub.__dict__, ignore_order=True)

重要的是，声明的类型已经改变。这并不意外，因为在第一个实例中，context.statement是一个sqlalchemy.sql.selectable.Select对象，而在后一个实例中query是{}对象。在

这突出了这样一个事实，即使用db.session.query()将查询转换为Query对象，会导致编译器根据语句的更改类型采用不同的路径。我们可以看到，S实际上是一个用以下方法包装在select中的别名：

>>> context.statement._froms
[<sqlalchemy.sql.selectable.Alias at 0x7f7e2f4f7160; foo>]

别名在select语句（S）中包装时呈现，这一事实在某种程度上与the documentation一致，后者将别名描述为在select语句中使用（但不是作为查询的根）：

When an Alias is created from a Table object, this has the effect of the table being rendered as tablename AS aliasname in a SELECT statement.

为什么会有一个次级选择？

让我们将不带.alias('foo')的查询命名为N（无别名），并在下面的伪代码中将其表示为n_query。因为它是sqlalchemy.sql.selectable.Select类型，当您调用db.session.query(n_query)时，它创建了一个子查询，其方式与使用别名的情况基本相同。您可以验证我们是否在另一个select中有一个select：

>>> Query(nquery).statement._froms
[<sqlalchemy.sql.selectable.Select at 0x7f7e1e26e668; Select object>]

你现在应该很容易看到select中的ect意味着在使用db.session.query(n_query)查询数据库时始终创建了子选择。在

我不确定为什么您显示的第一个查询有一个子查询可见-您是否可能在当时使用了echo（或str(db.session(n_query))）？在

我能改变这种行为吗？

当然！只需使用以下命令执行查询：

db.session.execute(n_query)

然后（如果您按照上面的说明启用了echo），您将看到相同的查询（正如您在最后发布的一样）。在

这与执行别名查询完全相同：

db.session.execute(n_query.alias('foo'))

因为如果没有连续选择，别名就没有用了！在