SQLAlchemy: How do you delete multiple rows without querying

Question

I have a table that has millions of rows. I want to delete multiple rows via an in clause. However, using the code:

session.query(Users).filter(Users.id.in_(subquery....)).delete()

The above code will query the results, and then execute the delete. I don't want to do that. I want speed.

I want to be able to execute (yes I know about the session.execute):Delete from users where id in ()

So the Question: How can I get the best of two worlds, using the ORM? Can I do the delete without hard coding the query?

score 74 · Accepted Answer · edited Sep 01 '19 at 04:29

74

Yep! You can call delete() on the table object with an associated where clause.

Something like this:

stmt = Users.__table__.delete().where(Users.id.in_(subquery...))

(and then don't forget to execute the statement: engine.execute(stmt))

edited Sep 01 '19 at 04:29

MarredCheese

answered Sep 29 '16 at 15:26

dizzyf

5

Didnt the question ask about using the ORM? This uses SQAlchemy Core syntax. Just an FYI I guess. – c8999c 3f964f64 Sep 08 '20 at 14:27

score 48 · Answer 2 · answered Nov 27 '18 at 14:02

48

To complete dizzy's answer:

delete_q = Report.__table__.delete().where(Report.data == 'test')
db.session.execute(delete_q)
db.session.commit()

answered Nov 27 '18 at 14:02

Mickael

score 30 · Answer 3 · edited Sep 09 '20 at 13:15

30

The below solution also works, if developers do not want to execute a plain vanilla query.

session.query(Users).filter(Users.id.in_(subquery....)).delete(synchronize_session=False)

edited Sep 09 '20 at 13:15

bertdida

answered Sep 07 '20 at 07:00

Ojus sangoi

2

What is that `synchronize_session=False` for? – Nam G VU Aug 03 '21 at 06:05
2

@NamGVU Here is the explanation from the documentation: False - don’t synchronize the session. This option is the most efficient and is reliable once the session is expired, which typically occurs after a commit(), or explicitly using expire_all(). Before the expiration, objects that were updated or deleted in the database may still remain in the session with stale values, which can lead to confusing results. https://docs.sqlalchemy.org/en/14/orm/session_basics.html#selecting-a-synchronization-strategy – Ojus sangoi Aug 03 '21 at 08:37

score 15 · Answer 4 · edited Jun 10 '23 at 02:53

15

New syntax (1.4 version and above)

from sqlalchemy import delete

statement = delete(User).where(User.id.in_(...))
session.execute(statement)

edited Jun 10 '23 at 02:53

wim

answered Feb 08 '22 at 17:42

Angel