Unable to insert extracted objects in SQLAlchemy ORM

user_7328605 0
Viewed 54

I'm trying to insert/update the list of objects extracted using SQLAlchemy ORM.

def truncate_mytable(self):
    with self.session.begin():
        current_records = self.session.query(MyTable).all()
        self.session.query(MyTable).delete()
        self.session.expunge_all()
    return current_records

def compensate_truncate_mytable(self, objects):
    with self.session.begin():
        self.session.bulk_save_objects(objects)

But while the objects have been extracted correctly, they are not getting written to the DB.

Could it be because there are also some protected attributes inside the objects, such as <sqlalchemy.orm.state.InstanceState object at 0x11471bf70> and <ClassManager of <class 'lib.kaizen_models.models.MyTable'> at 1146673b0>? The objects' type in the list is <lib.kaizen_models.models.MyTable object at 0x11471bd00>.

(I'm writing compensation methods, following Saga pattern.)

sqlalchemy python
1 Answers

The problem is that the objects are in a detached state, and this means bulk_save_objects will try to update rather than insert them*.

The state can be reset to transient by calling orm.make_transient on each object, after which they can be saved by bulk_save_objects.

def truncate_mytable(self):
    with self.session.begin():
        current_records = self.session.query(MyTable).all()
        self.session.query(MyTable).delete()
        self.session.expunge_all()

    for record in current records:
        orm.make_transient(record)
    return current_records

Alternatively, you could merge the objects back into the session before calling bulk_save_objects, but this might reduce the performance benefits that you want to obtain from the bulk operation.

* By default bulk_create_objects' update_changed_only argument is True, and since there a no changes in the objects' attribute histories no updates are attempted. Setting it to False will emit UPDATE statements, but result in a StaleDataError because the UPDATE matches no rows in the empty table.

user_53209060
Related