SQLAlchemy Delete And Insert in Same Transaction

Question

I have am using SQLAlchemy and inside one transaction, I want to do the following:

1. Delete all records that meet a certain criteria (say this is Cars.color == red).
2. Now, I want to insert all cars that meet a certain criteria (say Cars.type == Honda).

Now lets say that my database is just a table with 3 columns (name, color, andtype) with name as the primary key.

If my database already has cars that are red and of type Honda with name as Bob. I can't just say

Cars.query.filter(Cars.name == red).delete()
// add all Hondas
db.session.commit()

as the // add all Hondas will fail because I could potentially be adding a car with name as Bob and color red. How can I do a deletion and have deletions follow as part of one action?

Reference: I am using MySQL.

Answer 1

Caveat lector - I do not think your current code does set up the transaction properly. Apart from that I don't believe that the problem you describe exists - session.commit() flushes the changes in sequences, so no matter whether session.flush() is called, you should be able to insert the Hondas at the point you marked - the red car will be deleted before the insert hits the DB.

Answer 2

You could try this:

db.session.query(Cars).filter(Cars.name == red).delete()
 // add all Hondas
for h in Hondas:
    db.session.add(h)
db.session.commit()