How to construct "DELETE WHERE EXISTS IN" using SQLAlchemy?

Question

I want to delete rows from a table with composite keys.

I need to construct a query of the form:

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

How can I do this in SQLAlchemy?

Here is an example which has a table which records multiple scores per user per game. I would like to delete the lowest score for every user in every game the user participated in.

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


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

The data might look like this:

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

I want to delete (g1,u1,2) and (g2, u1,1).

Here is my attempt so far using 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

Whereas I get the expected SQL for in_clause and low_score_subquery, the delete query (below) isn't right. I've tried variations of the following, but all with bad results:

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

I have tried exists(in_clause), exists([], in_clause) and in_clause.exists() but these all result in exceptions.

linus · Accepted Answer · 2014-11-20 21:36:51Z

1

Do you really need the EXISTS? Does this not do what you want?

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

answered Nov 20, 2014 at 21:36

linus

3361 silver badge9 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to construct "DELETE WHERE EXISTS IN" using SQLAlchemy?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related