> On Feb 3, 2016, at 7:07 PM, Mike Bayer <mike...@zzzcomputing.com> wrote: > > On 02/03/2016 09:39 PM, jemer...@spiresystems.com wrote: >> We're using SQLAlchemy in a GUI application and have been experiencing >> some challenges integrating the ORM Session with our usage model. (This >> is difficult to change as we have ported from a legacy system to >> SQLAlchemy.) >> >> We're currently using autocommit=True. When the user edits a record we >> create a new session, load the record from the database, and allow them >> to interact with the data. When they save the record we validate and >> flush the changes. Since we don't want to hold database transactions >> open for a long period of time, we start a transaction once the user has >> clicked save. Unfortunately, as soon as we call Session.begin() the >> session will call flush() outside the context of the transaction. > > ah, that's some old behavior but yes, that will happen *if you have dirty > state* in the session. The Session can't keep it's contract of being able > to roll back state to a point that matches what's in the database if it > doesn't flush out dirty state beforehand; because when a rollback happens, it > wants to just expire all the state, which means your pre-transaction changes > will be lost. > > You can disable this by passing the flag > _enable_transaction_accounting=False, but you then the state of the session > will be out of sync after a rollback.
Since the record objects are directly edited by the user, we will be accumulating dirty state outside the context of the transaction. (We also experimented with holding a transaction open while the record was being edited, but this wasn’t workable for several reasons.) >> We have tried a couple approaches to work around this, to very limited >> success: >> >> First, we set _enable_transaction_accounting=False, but this causes >> Session._expunge_states() to fail with an AttributeError accessing >> SessionTransaction._deleted. > > well that's just a bug. Feel free to file a test case for that and it's > likely a simple fix. Will do. > >> >> Subsequently we tried using Connection.begin() to "hide" the transaction >> from the Session, but this causes problems when there is an error >> causing the transaction to rollback as the Session and the database are >> now inconsistent with each other. > > that would be my next idea, so if you do need to have rollbacks cause the > object state to reset itself and remain consistent with the beginning of the > transaction, this indicates _enable_transaction_accounting won't work for you. > > If you need the Session to expire its state such that it matches what was > present at the beginning of the transaction, then I'm not sure how you intend > to have dirty state from before the transaction lying around unflushed. > Syncing up with a rollback necessarily means that we expire all in-memory > state and we have to load it again. Your pre-transaction changes however > would be lost. > > Only the approach of doing a full in-memory rollback to the previous state of > the objects would get around this, but there is no such ORM in the Python > world I'm familiar with which performs such a task, while it's "simple" for > things like scalar attributes, it quickly becomes massively complicated for > collections and object graphs. > > > >> >> Most recently, I put on my mad science gear and have attempted to pickle >> the __dict__ and InstanceState to restore these on rollback, but this >> doesn't seem to work. (My understanding of session internals is still >> fairly weak, so perhaps I'm simply missing something or perhaps this is >> an unworkable solution.) >> >> It's my sincere hope that we can find a solution that does not involve >> replacing SQLAlchemy with another ORM. > > im not sure why you need to use begin() at all? in autocommit mode, the user > presses save, you just call flush(). flush() always uses a transaction > internally. That's originally how the Session was meant to be used, having > an explicitly open transaction was an optional feature. Unfortunately, some processes in our application execute SQL directly, often operating on records which were very recently inserted into the database. Our models have save methods that validate the record and then issue a flush. This means that flush will be called several times during the course of a transaction. (Yay legacy code!) > Since you're looking for an ORM that can rollback a transaction and > simultaneously reset the state of the objects to exactly what they were at > the beginning of that transaction, there is no ORM I'm aware of that can do > this any differently than SQLAlchemy does. More simplistic ORMs like Django > / Peewee etc. have no concept of expiration-on-rollback, and after a > transaction rollback your objects have exactly the same in-memory state as > before. SQLAlchemy offers this mode of operation both through the > _enable_transaction_accounting=False (with small bugfixes apparently) as well > as the use of an external transaction. The only requirement is that attributes and instances that were dirty before the transaction began must be dirty after a rollback. I’m not concerned about instance attributes being rolled back to their previous value (we didn’t have this before.) If we use _enable_transaction_accounting=False and issue multiple calls to flush during the course of a transaction is there a way to restore the dirty state to the point before the transaction began? Thanks, James -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
