Is there any order for add versus delete when committing in sqlalchemy

Question

I'm adding a bunch of entries to a table in sqlalchemy. If an entry already exists, based on some key, I delete the row in the table and then add the "updated" entry. After finishing deleting and adding all entries I commit the session. However during testing the commit fails due to a unique constraint failure. My understanding from this error is that I'm trying to add the updated entry before deleting the old entry. If I delete the old entry, then commit, then add, everything works ok.

So my question is, does sqlalchemy have a defined order of operations for deleting and adding? Is it possible to change this order? Looking through my code, I noticed the object I'm adding is instantiated twice but only added once (see below) - maybe that's a problem (but not sure why it would be).

I also don't want the commit() because I only want to commit if I get through adding/updating all of the entries.

#Inside a loop
#-----------------------
temp_doc = Document(doc)
dirty_doc = session.query(Document).filter(Document.local_id == temp.local_id).first()

#other non-relevant code here ...

session.delete(dirty_doc)

#This seems to be needed but I wouldn't expect it to be 
session.commit() 

#Later on in the code ...
if add_new_doc:
    temp_doc = Document(doc)
    session.add(temp_doc)

#Outside the loop
#-----------------------------
session.commit()
session.close()

Jimbo Jimbo · Accepted Answer · 2020-03-23T12:33:30

A similar yet different question was asked here regarding whether order was maintained within objects that were added: Does SQLAlchemy save order when adding objects to session?

This prompted looking at the session code, since I haven't seen any documentation on flush behavior.

Session code link: https://github.com/sqlalchemy/sqlalchemy/blob/master/lib/sqlalchemy/orm/session.py

Search for def _flush(self

In the code it looks like adds and updates are done before deletes, which would explain why I'm running into my problem.

As a fix, I'm now flushing instead of committing (inside the loop), which seems to fix the problem. I'm assuming flush order is maintained, i.e. commands that are flushed first get committed/saved first.

Is there any order for add versus delete when committing in sqlalchemy

1 Answers