Skip to main content
 首页 » 编程设计

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

2025年05月04日11mq0036

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

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

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

请您参考如下方法:

你真的需要 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")