Using sessions in a GUI application

[Philip Scott]

Hello all,

Short summary of my question: 

Is it possible to stop rollback() from expiring dirty objects?

Specifically, in the case where there are

 - local changes made outside to mapped objects outside of a transaction 

 - flush() is called

 - the flush fails

In this case SQLAlchemy will expire any objects that had local changes, and those changes are lost. I would ideally like get objects & session to be in the same state as they were before I called flush().

Longer explanation of my approach, in case I am just doing it wrong :)

I have a GUI application where users can load, view, and edit some database mapped objects. Stripped down, it's pretty simple stuff like this:

class Action(Base):

    __tablename__      = "action"

    id                 = Column(Integer, primary_key=True)

    description        = Column(Text)

class Step(Base, ActionSchemaMixin):

    __tablename__      = "step"

    id                 = Column(Integer, primary_key=True)

    id_action          = Column(Integer, ForeignKey(Action.id))

    action             = relationship(Action, backref='steps')

So the action has some attributes, and a number of steps, which have their own attributes. Users can select an action (or create a new one) - then add, remove, and update steps for that action from the GUI.

I have found it to be an extremely useful pattern to couple these widgets directly to the mapped objects they are dealing with, and let the various event handlers to update the mapped objects whenever the user alters the GUI.

The following pseudo-code should give you an idea

class ActionWidget(Widget):

    def __init__(action):

        self.action = action

        self.description_edit_widget = TextEditWidget(on_changed=self.on_description_widget_changed)

     def on_description_widget_changed(self, new_description):

        self.action.description = new_description

I also have a widget for a list-of-steps that shows a summary for each, and allows you to add/remove new ones - and a widget for editing an individual step.

Because I have no control over how long the user will faff about when editing one of these actions, and we do not want long open transactions to the database, I have set up my session with the slightly controversial (autoflush=False, autocommit=True, expire_on_commit=False) options. I load up the action and all the steps, the user can take as long as they like mucking about with it - and when they press the 'Save' button, I just flush all the changes.

It works great, except if something goes wrong with the flushing. If some DB constraint is violated for example, the transaction created in flush() is rolled back. All my modified objects are expired, and any new ones are expunged, as faithfully described in the docs.

In this case though, it is unfortunate, because now I have a mismatch between what is in my GUI and the objects they are supposed to be displaying. I want to be able to warn my user about the problem and let them fix the one field that was wrong, without forcing them to loose all their changes and start again.

It is quite difficult for me to recreate the mapped objects changed state from the GUI elements alone, as there are temporary widgets created at times to edit certain attributes.

The only way around this that I can think of is to detach all the objects before coupling them to the GUI elements - then in my save function, somehow copying all of the updated/new objects before adding them to a session and attempting to commit the changes. I could get this to work, but think it might be fiddly when there are a mixture of new and dirty persisted objects.

Interestingly when creating a set of brand new objects, things are fine too - although they are expunged, they can be re-added to the session when the user is ready to try again.

hoping there is a simple answer but suspecting there isn't :)

All the best,

Philip

[mike bayer]

so the quick and dirty approach is a flag I'm not enthused about,
nevertheless I don't plan on removing, called
_enable_transaction_accounting, that when set to False, will put the
Session in total SQLAlchemy 0.4 mode where there is no tracking or
expiry of state across transactions of any kind. This flag is in the
docs for Session, it goes alongside the autocommit etc. flags.

The more architectural answer is that the ORM considers these objects to
be proxies for state within a database transaction. That your GUI lives
outside of the span of a single transaction is correct, but also implies
that the pattern to persist the GUI to the database would involve
copying its state into the database structure, which in this case is
your ORM model. That is, the state of your GUI would in fact be
represented outside of those ORM objects.

You can do this kind of pattern by having the ORM objects represent the
GUI state as "detached" objects, then copy the state of those objects
into the session using a method like Session.merge. On an empty
Session this would incur a lot of SELECT statements, but you can try
working with session.merge(..., load=False) and feed the objects in, if
they have pending changes on them themselves you can use
sqlalchemy.orm.session.make_transient_to_detached() on each to set them
as "clean".

The best way would be that the GUI has its own dedicated data structure
that is distinct from your ORM model. A more high-end way of
representing this is to create "action" objects which represent things
the user does, like pressing a button or selecting a choice in a
dropdown. You log these "action" objects into a list as the user does
them, and when ready for persist, you "play" these objects into an ORM
model. This in formal parlance is known as the "command" pattern and
one reason it's key for GUIs is that it gives you "undo" ability for
free - each Command object can not only "play" itself into the GUI, it
can also "unplay" itself. Overall, the idea is not to look at the GUI
as a current set of state, look at it as the graphical representation of
a series of events that have occurred.

So you have "quick", "better", and "formalized" options on this.




>
> All the best,
>
> Philip
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> 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+...@googlegroups.com
> <mailto:sqlalchemy+...@googlegroups.com>.
> To post to this group, send email to sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Philip Scott]

Hi Mike

Thank you for your quick and thoughtful response as usual!

 

so the quick and dirty approach is a flag I'm not enthused about, nevertheless I don't plan on removing, called _enable_transaction_accounting,

Yes I saw this when poking around in the source code - it does seem like a bit of a backwards step; I will try and solve my problem without that :)

 

The more architectural answer is that the ORM considers these objects to
be proxies for state within a database transaction.  

Understood. I guess we are going a bit outside the intended SQLAlchemy use-case here.

You can do this kind of pattern by having the ORM objects represent the GUI state as "detached" objects, then copy the state of those objects
into the session using a method like Session.merge.   

I think this is what I will probably end up doing. There's a quite a bit of business logic attached to the mapped classes - for example the 'Actions' are polymorphic and contain methods that are 'recipes' for generating a standard set of steps based on the attributes of the action. 

The best way would be that the GUI has its own dedicated data structure that is distinct from your ORM model.

Yes, I think you are right here - though the business logic I described above makes this a bit troublesome. In the past I have done this sort of thing, created a throwaway mapped class and populated it with data so that I could make use of its methods.

  A more high-end way of representing this is to create "action" objects which represent things the user does,

Deluxe indeed.. I'll have a think about that, though it is probably overkill for the simple app I am writing.

Every time I do a GUI with SQLAlchemy I try a slightly different approach - it is tantalising because each time I think I have cracked the perfect pattern but it always ends up being slightly more complicated than I thought! I can't come up with any suggestions on how to do it better though.

Keep up the good work - we love SQLAlchemy at my firm. It is directly responsible for keeping track of several billion dollars worth of financial products and I sleep soundly at night having selected it as a platform :)

[mike bayer]

On 01/20/2017 04:25 PM, Philip Scott wrote:
> Hi Mike
>
> Thank you for your quick and thoughtful response as usual!
>
>
> so the quick and dirty approach is a flag I'm not enthused about,
> nevertheless I don't plan on removing, called
> _enable_transaction_accounting,
>
>
> Yes I saw this when poking around in the source code - it does seem like
> a bit of a backwards step; I will try and solve my problem without that :)

It's possible that if you're using the session in "autocommit" mode and
just want to occasionally "broadcast" your object state to a quick
"flush everything", that in fact turning off the "accounting" feature
with this flag is appropriate. I'm looking at this code and it really
is all about the thing you don't want, that is, anytime the transaction
goes away, erasing all the object state because we no longer know the
state of the DB.

Trying to recall why I have expire_on_commit=False but there's no
expire_on_rollback, it's because after a commit, the only thing that can
make the database state change vs. what your objects have are other
transactions outside of what you have in front of you. but with
rollback, there could have been any number of insert/update/delete
within the last transaction that are gone. The state of the objects is
going to be a combination of data that was loaded from the DB that might
have been rolled back, data that was the result of server-side defaults
and sequences from INSERT statements that might have been rolled back,
and then values that came from the application, but those values are
organized onto an object structure that itself might not exist anymore
because the transaction was rolled back. I think the idea is that to
really try flushing again, you have to start from zero when the
transaction first started and replay everything, otherwise things will
not be the same as they were before.

[Philip Scott]

It's possible that if you're using the session in "autocommit" mode and
just want to occasionally "broadcast" your object state to a quick
"flush everything", that in fact turning off the "accounting" feature
with this flag is appropriate.   I'm looking at this code and it really
is all about the thing you don't want, that is, anytime the transaction
goes away, erasing all the object state because we no longer know the
state of the DB.

Thanks Mike, having read the source some more myself I agree :)

 

Trying to recall why I have expire_on_commit=False but there's no
expire_on_rollback, it's because after a commit, the only thing that can
make the database state change vs. what your objects have are other
transactions outside of what you have in front of you.  but with
rollback, there could have been any number of insert/update/delete
within the last transaction that are gone.  The state of the objects is
going to be a combination of data that was loaded from the DB that might
have been rolled back, data that was the result of server-side defaults
and sequences from INSERT statements that might have been rolled back,
and then values that came from the application, but those values are
organized onto an object structure that itself might not exist anymore
because the transaction was rolled back.  I think the idea is that to
really try flushing again, you have to start from zero when the
transaction first started and replay everything, otherwise things will
not be the same as they were before.

That make complete sense. I guess the only way to avoid it would be to somehow take a snapshot of the state of all of the mapped objects and their status in the session when a transaction begins, and restore that after the corresponding rollback. I bet that could actually be implemented with some event listeners, though I guess that is just a more convoluted way of achieving the same thing as _enable_transaction_accounting=False.

Would it make sense, do you think, to allow disabling of transaction accounting on a per transaction basis, instead of for the entire session? e.g. if I could say session.flush(enable_transaction_accounting=False) to disable it for the transaction it creates to do the flushing?