Confusion over session.dirty, query, and flush

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

Hello,

I've been exploring some of the session functionality that handles object states, and I'm quite confused. Here is what I see:

>>> engine = engine_from_config({'sqlalchemy.url': 'mysql+pymysql://…'})
>>> session_factory = sessionmaker(bind=engine) # No autoflush
>>> session = session_factory()
>>> # Now query a table to get an object.
>>> p = session.query(Table).filter(Table.id == '0f4…ed6').one_or_none()
>>> p.name
"Some Name"
>>> p.name = "Other Name"
>>> session.dirty
IdentitySet([<Table object at 0x10bb50d68>])
>>> session.is_modified(p)
True
>>> session._is_clean()
False

This all makes sense. If I now create a new query, then the above change seems to be gone, but isn't?

>>> p2 = session.query(Table).filter(Table.id == '384…a05c').one_or_none()
>>> session.dirty
IdentitySet([])
>>> session.is_modified(p)
False # p is not modified according to this.
>>> session._is_clean()
True
>>> p.name
"Other Name" # p still has the modified name.

The new query seems to set the session to "clean", but the object p still contains its change. I can't quite find documentation for the behavior. What am I missing?

What I would like to do is: in one session, select a few objects (multiple queries), inspect, perhaps modify them. Then I'd like to query if there were any modifications/deletes and if so choose to commit or rollback. Initially I thought to use the Session.dirty/deleted/new properties, but then the above showed.

If I was to set autoflush, how could I inspect the flushed code buffer? 

Thanks!

Jens

[Simon King]

The results you are seeing are due to autoflush, which is on by
default. When you run your second query, the session flushes any
pending changes to the database before running the query. After the
flush, the session is considered clean. The methods you were using
(dirty, is_modified etc.) indicate whether the session contains
changes that haven't been flushed to the database. They don't tell you
if the database has received changes that haven't yet been committed.

There are various ways to do what you want, depending on how your code
is structured. One possibility is to use a Session event such as
before_flush to set a flag saying that there are uncomitted changes in
the database.

http://docs.sqlalchemy.org/en/latest/orm/session_events.html

Hope that helps,

Simon

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

That makes sense, thank you, Simon!

Regarding the events: you suggest to use a before_flush() to examine session.dirty whenever a session.query() executes?

Also, is there a way to get the list of objects that have been flushed, or should I track them myself whenever a before_flush() event occurs?

Jens

[Simon King]

Can you explain why you actually want to do this? There might be
better options than before_flush, but we'd need to know exactly what
you're trying to do first.

Simon

> --
> 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.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

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

Sure.

I'm working with two Pyramid/SQLAlchemy web servers, and in order to have a more convenient way of looking at the db data I wrote a small tool which essentially creates a db session, loads the server orm helper functions and sets up an environment much like a view handler functions has. Then the tools calls code.interact() and I have a terminal.

From that terminal I can look at tables, use the server's helper functions to read data, but also to write objects.

When I exit interactive mode, I just rolled back the transaction and ended the session. However, I'd now like to check: if during that terminal session some objects were modified, give the user the choice to either commit() or rollback().

To do that, I checked with session.dirty/deleted/new and that's when the initial questions arose.

If there are better ways of checking, curious to learn :-)

Thank you!

[Simon King]

OK, I think tracking session events seems reasonable. You could do
something like this (completely untested):

from sqalchemy.event import event

@event.listens_for(YourSessionOrSessionMaker, 'before_flush')
def on_before_flush(session, flush_context, instances):
session.info['flushed'] = True


# You'd probably also want to reset the 'flushed' flag
# after a commit or rollback
@event.listens_for(YourSessionOrSessionMaker, 'after_commit')
@event.listens_for(YourSessionOrSessionMaker, 'after_rollback')
def on_session_reset(session):
session.info['flushed'] = False


# when user exits interactive session:
modified = (
session.info.get('flushed', False)
or session.deleted
or session.new
or session.dirty
)
if modified:
raw_input('do you want to commit?')


...but note that if you ever execute raw SQL (ie.
session.execute('UPDATE x WHERE y')), that will not be noticed by
those events.

Hope that helps,

Simon

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

Thank you, the event worked like a charm :-) Though I think that I don't need the commit events, because the application terminates anyway.

I modified your approach to gather which objects were flushed so that in the end I can give the user more precise information:

        dbsession.info["new"] = set()                                                               

        dbsession.info["dirty"] = set()                                                             

        dbsession.info["deleted"] = set()                                                           

                                                                                                    

        def update_session_info(session):                                                           

            new = session.info["new"]                                                               

            new |= set(session.new)                                                                 

            dirty = session.info["dirty"]                                                           

            dirty |= set(session.dirty)                                                             

            deleted = session.info["deleted"]                                                       

            deleted |= set(session.deleted)                                                         

            return new, dirty, deleted                                                              

                                                                                                    

        @event.listens_for(dbsession, "before_flush")                                               

        def on_before_flush(session, _, _):                                     

            update_session_info(session)

        ...

        code.interact(local=locals())                                                               

        ...

           

        new, dirty, deleted = update_session_info(dbsession)                                        

        if new or dirty or deleted:                                                                 

            if new:                                                                                 

                print("The following objects were created: ", new)                                   

            if dirty:                                                                               

                print("The following objects were modified: ", dirty)                                  

            if deleted:                                                                             

                print("The following objects were deleted: ", deleted)                                   

            yesno = input("Would you like to commit this transaction? [y/N] ")                     

            if yesno == "y":                                                                        

                print("Committing transaction...")                                             

            else:                                                                                   

                print("Rolling back transaction...")                                           

                raise _SessionRollbackException()                                                   

        # ...this is where the context closes and the transaction commits and the dbsession ends.

Cheers,

Jens

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

I’d like to pick up this topic once more briefly.

Suppose I get the “new”, “dirty”, and “deleted” sets as per discussion below, and I’m especially interested in the “dirty” set: is there a way to find out which properties of an object were modified, or only that the object was modified?

Thanks!

Jens

[Jonathan Vanasco]

On Wednesday, September 26, 2018 at 10:08:43 PM UTC-4, jens.t...@gmail.com wrote:

Suppose I get the “new”, “dirty”, and “deleted” sets as per discussion below, and I’m especially interested in the “dirty” set: is there a way to find out which properties of an object were modified, or only that the object was modified?

You want the `inspect`  API

https://docs.sqlalchemy.org/en/latest/core/inspection.html

use `inspect` to get at the InstanceState for the object (https://docs.sqlalchemy.org/en/latest/orm/internals.html#sqlalchemy.orm.state.InstanceState)

then use `attrs` on the InstanceState to view the `attrs` which has an `AttributeState` with a `history` (https://docs.sqlalchemy.org/en/latest/orm/internals.html#sqlalchemy.orm.state.AttributeState)

if you search for 'inspect' in this forum, Michael has provided many examples on this topic.