SQLAlchemy delete a list of objects (opposite of bulk_save_objects)

Question

If I want to save many objects within SQLAlchemy's ORM, I can use session.bulk_save_objects(). I want do the opposite of that --> I want to delete a list of objects all at once:

# Efficiently adding many objects:
obj_1 = Model(name="foo")
obj_2 = Model(name="bar")
session.bulk_save_objects([obj_1, obj_2])
session.commit() # execute


# How do I efficiently delete many objects?
record_obj = session.query(Model).filter(Model.name.in_(['foo', 'bar']).all()
# Assume `record_obj` is a list with a length of 2...


# CURRENT SOLUTION:
# I know I can loop through the list and delete them one by one, then commit
for obj in record_obj:
    session.delete(obj)
session.commit()


# DESIRED SOLUTION:
# Is it possible to delete the list itself?
session.delete(record_obj)

# Or maybe I need to unpack the list?
session.delete(*record_obj)

Jelmer Wind · Accepted Answer · 2021-01-14 12:41:16Z

1

As far as I can tell, SQLAlchemy does not provide such functionality out of the box. However, using you can get a fast delete in only a few lines:

obj_ids = [obj.id for obj in record_obj]
query = session.query(Model).filter(Model.id.in_(obj_ids))
query.delete(synchronize_session=False)
db.session.commit()

Where id should be replaced by the primary key in your model.

edited Jan 14, 2021 at 12:41

answered Jan 14, 2021 at 12:27

Jelmer Wind

4355 silver badges12 bronze badges

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

4 Comments

Yaakov Bressler Over a year ago

Interesting! You can perform a query and execute query.delete()! That's clever.

Jelmer Wind Over a year ago

Yes you can! Note that the in_() part of the query may get very slow if you have many (>1000) items. In that case, instead of providing an explicit list, it may be better to write a query to find the items.

Ripper346 Over a year ago

@JelmerWind could you explain better what you mean by "it may be better to write a query to find the items"? In plain SQL if we do a select or a delete the IN statement should be equal in terms of performance, they should use the same index or table access, shouldn't they?

Jelmer Wind Over a year ago

Suppose you want to remove all Models for which Model.model_class_id = 1, then it may be faster to write: query = session.query(Model).filter(Model.model_class_id == 1).delete() In practice, this filter could be a lot more complex, including joins with other tables etc. In my experience, this is faster weather there is an index on model_class_id or not.

Collectives™ on Stack Overflow

SQLAlchemy delete a list of objects (opposite of bulk_save_objects)

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related