Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

sqlalchemy: get rows affected by bulk delete

I need to get all rows that were affected by a bulk delete like that:

Session.query(SomeClass).filter_by(foo='bar').delete()

I found the possibility to subscribe to bulk delete events but can't figure out how I can use this event to actually get the information I need.

like image 359
epegzz Avatar asked Jan 21 '12 15:01

epegzz


People also ask

How do I delete multiple records in SQLAlchemy?

SQLAlchemy Delete Multiple RowsThe delete() SQL Expression will construct the multiple tables, and the function will generate the new instance of delete which already represents the delete statement in the SQL.

What is _sa_instance_state in SQLAlchemy?

_sa_instance_state is a non-database-persisted value used by SQLAlchemy internally (it refers to the InstanceState for the instance. While not directly relevant to this section, if we want to get at it, we should use the inspect() function to access it).

What does SQLAlchemy all () return?

As the documentation says, all() returns the result of the query as a list.

Is SQLAlchemy efficient?

SQLAlchemy leverages powerful common statements and types to ensure its SQL statements are crafted efficiently and properly for each database type and vendor without you having to think about it. This makes it easy to migrate logic from Oracle to PostgreSQL or from an application database to a data warehouse.


1 Answers

Okay, found the answer \☺/ You can re-execute the select statement that was used to select the rows for bulk deletion. Unfortunately you can't query the ORM objects, but you can get a list of all the deleted rows with the old column values as tuples:

def after_bulk_delete(session, query, query_context, result):
    affected_table = query_context.statement.froms[0]
    affected_rows = query_context.statement.execute().fetchall() 

sqlalchemy.event.listen(Session, "after_bulk_delete", after_bulk_delete)
like image 193
epegzz Avatar answered Oct 15 '22 12:10

epegzz