supreme Pooba supreme Pooba - 2 months ago 22
Python Question

SQLAlchemy: How do you delete multiple rows without querying

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?

Answer

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

Something like this:

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

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

source