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

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)

def get_battle_appearences_cte():
    """Create a sqlalchemy subquery of the battle appearences."""
    wins = select([
        WaifuPickBattle.date,
        WaifuPickBattle.winner_name.label("name"),
        expression.literal_column("1").label("was_winner"),
        expression.literal_column("0").label("was_loser")
    ])
    losses = select([
        WaifuPickBattle.date,
        WaifuPickBattle.loser_name.label("name"),
        expression.literal_column("0").label("was_winner"),
        expression.literal_column("1").label("was_loser")
    ])
    return wins.union_all(losses).cte("battle_appearence")

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()

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
)

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)

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

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

最佳答案

答案

SQLalchemy decides to introduce it despite not being explicitly instructed to

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.

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

dialect = postgresql.dialect()
str(query.compile(dialect=dialect))

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

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

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

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

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

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._froms
[<sqlalchemy.sql.selectable.Alias at 0x7f7e2f4f7160; foo>]

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.

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

db.session.execute(n_query)

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

关于python - SQLAlchemy 要求为查询设置别名，但在生成的 SQL 中未使用该别名，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/56040284/