0

I'm learning SQLAlchemy ORM and I think I have a handle on the basics. One thing I'm curious about though: When a change is made that implies a database write (for example session.add(my_object)), that change is treated as pending in the session and isn't written until right before the next query.

What I'm wondering is, are all such writes always written before the next query, or does the next query have to be somehow related to the pending write? For example if I issue the command session.add(my_object) and then my next command is session.query(ClassOfMyObject).filter(...some condition which would match my_object).all(), I know that the first write will definitely be flushed before this latter query is executed. But what if the objects are totally unrelated, e.g. session.add(apple) and session.query(Orange).filter(...) where there isn't even a foreign key between apples and oranges? Would apple still be written before we query for an Orange?

Also, what is the rationale for delaying the flush like this? Why not always flush everything immediately?

Stephen
  • 8,508
  • 12
  • 56
  • 96
  • Have you read ["SQLAlchemy: What's the difference between flush() and commit()?"](https://stackoverflow.com/questions/4201455/sqlalchemy-whats-the-difference-between-flush-and-commit) It's a rather good Q/A about SQLA flush behaviour. – Ilja Everilä Apr 15 '19 at 20:00

1 Answers1

1

Imagine the database from your worst nightmares. Spaghetti code triggers left and right, doing who knows what. There might be a business rule that for every apple a tax of 2 oranges is payed. And so in order to keep the world in order, SQLAlchemy must flush all changes before every query you make, unless you tell it that it does not need to do so.

As to delaying flushes, it may allow for example batching multiple writes to the same table into a single multi-values INSERT statement, which means fewer round trips to the database.

Ilja Everilä
  • 50,538
  • 7
  • 126
  • 127