python - 如何使用 SQLAlchemy 构造 "DELETE WHERE EXISTS IN"？

Question

我想从具有复合键的表中删除行。

我需要构建以下形式的查询:

DELETE FROM t WHERE EXISTS (c1, c2, c3) IN (subquery)

我如何在 SQLAlchemy 中执行此操作？

这是一个示例，其中有一个表格，其中记录了每个用户每场比赛的多个分数。我想删除用户参与的每个游戏中每个用户的最低分数。

from sqlalchemy import Table, Column, MetaData, String, Integer


metadata = MetaData()
t = Table('scores', metadata, 
           Column('game',String),
           Column('user',String),
           Column('score',Integer))

数据可能是这样的:

game     user    score  
g1       u1      44
g1       u1      33
g1       u1      2     (delete this)
g2       u1      55
g2       u1      1     (and this)

我想删除(g1,u1,2) 和(g2, u1,1)。

到目前为止，这是我使用 SQLAlchemy 的尝试:

from sqlalchemy import delete, select, func, exists, tuple_

selector_tuple = tuple_(t.c.game, t.c.user, t.c.score)
low_score_subquery = select([t.c.game, t.c.user, func.min(t.c.score)])\
                        .group_by(t.c.game, t.c.user)
in_clause = selector_tuple.in_(low_score_subquery)
print "lowscores = ", low_score_subquery # prints expected SQL
print "****"
print "in_clause = ", in_clause # prints expected SQL

虽然我得到了 in_clause 和 low_score_subquery 的预期 SQL，但删除查询(如下)不正确。我尝试了以下变体，但结果都不好:

>>> delete_query = delete(t, exists([t.c.game, t.c.user, t.c.score], 
...                                 low_score_subquery))
>>> print delete_query # PRODUCES INVALID SQL
DELETE FROM scores WHERE EXISTS (SELECT scores."game", scores."user", scores.score 
FROM (SELECT scores."game" AS "game", scores."user" AS "user", min(scores.score) AS min_1 
FROM scores GROUP BY scores."game", scores."user") 
WHERE (SELECT scores."game", scores."user", min(scores.score) AS min_1 
FROM scores GROUP BY scores."game", scores."user"))

我已经尝试过 exists(in_clause)、exists([], in_clause) 和 in_clause.exists() 但这些都会导致异常(exception)。

Answer 1

你真的需要 EXISTS 吗？这不是您想要的吗？

>>> delete_query = delete(t, in_clause)
>>> print(delete_query)
DELETE FROM scores WHERE (scores.game, scores."user", scores.score) IN (SELECT scores.game, scores."user", min(scores.score) AS min_1
FROM scores GROUP BY scores.game, scores."user")