Dis/Reassociate objects with a db session.

[jens.t...@gmail.com]

Hello,

My question is regarding long-running tasks and db sessions. Currently I have the very rare situation where a task takes longer than a db session is valid and thus fails when it wants to write back results. Extending the TTL of a db session is probably not a good idea.

I think the proper approach would be to open a db session, fetch data, close the db session, do work, open a new db session, write data, close the db session. So, I must make sure that I fetch all data ahead of time while the first session is active.

Is there a way to re-associate objects that belonged to the first session with a newly opened one? What’s the recommended approach here, does SQLA have any magic in store to help me with very long-lived ORM objects across db sessions? Or should I manage that data independently of their respective ORM objects?

Thanks!

Jens

[Jonathan Vanasco]

Read the docs on State Management and pay attention to `merge`:

   https://docs.sqlalchemy.org/en/14/orm/session_state_management.html

Also, to simplify this stuff a popular related pattern is to use  a RevisionID or RevisionTimestamp on the objects.  In the first session, you note the version information. On the second session you fetch a new object and ensure it is the same - if so, your data is safe to update.  If not, the objects became out of-sync and may require more logic.