Hi Mike,

Thanks for the detailed explanation and example code. I suspect this is
exactly what is happening (that the deserialization library is using add
instead of merge) - I will look over the code and confirm.

Thanks!

On Fri, Sep 16, 2016 at 7:05 AM, Mike Bayer <mike...@zzzcomputing.com>
wrote:

>
>
> On 09/15/2016 11:22 PM, 'Nicholas A Fries' via sqlalchemy wrote:
>
>> Hi Mike. Thanks for the reply and clarification on how the history
>> system is implemented. I will investigate further and review the code
>> you mentioned.
>>
>> Right now, I can see that get_history() is showing changes for one of
>> the relationships (an add and a delete of an object with the same type
>> as the relationship) and I can also verify that the data itself is no
>> different from what was committed to the database.
>>
>
> OK but in isolation, "get_history() shows an add and a delete of an object
> with the same type of relationship" and that it has "the data that is no
> different from what was committed to the database" does not mean it is
> working improperly.  For relationships, two objects that represent the same
> row *must be the same object*.   This is how the identity map works.  If
> you have two separate objects in one session, doesn't matter if they are
> identical, they are not both keyed to the identical identity in the
> identity map, so this is a net change.
>
> The cause for the condition you describe, where you have *two* objects
> that are identical in data but aren't the same, is when a program uses
> session.add() to put a new object in the session when it should be using
> merge() or a manual equivalent of loading existing identities and
> updating.   Since you're doing deserialization, merge is recommended for
> exactly this use case since if you are deserializing a structure that
> includes an identity already in your database, you want to *load* that
> identity and *update* its contents, rather than making a brand new
> identity.  The ORM cannot figure out at flush time if a particular primary
> key is already present in the database or not as this would be very
> complicated and expensive (in SQL this is called a MERGE, oddly enough).
>
> Merging is discussed in detail at http://docs.sqlalchemy.org/en/
> latest/orm/session_state_management.html#unitofwork-merging  and the
> example below illustrates something resembling what I think you're trying
> to do:
>
>
> from sqlalchemy import *
> from sqlalchemy.orm import *
> from sqlalchemy.ext.declarative import declarative_base
>
> Base = declarative_base()
>
>
> class A(Base):
>     __tablename__ = 'a'
>     key = Column(String, primary_key=True)
>
>     b_id = Column(ForeignKey('b.key'))
>     b = relationship("B")
>
> class B(Base):
>     __tablename__ = 'b'
>     key = Column(String, primary_key=True)
>     data = Column(String)
>
> struct_one = {
>     "a": {
>         "key": "a_key",
>         "b": {
>             "key": "b_key",
>             "data": "the data"
>         }
>     }
> }
>
> e = create_engine("sqlite://", echo=True)
> Base.metadata.create_all(e)
>
>
> def persist_struct(s, struct, use_merge):
>
>     # manually "merge" data into A.  That is, look up the identity first
>     # and UPDATE if already present.
>     a = s.query(A).get(struct["a"]["key"])
>     if a is None:
>         a = A(key=struct["a"]["key"])
>         s.add(a)
>
>     # for b, do the same thing using merge() if use_merge is True.
>     b = B(key=struct["a"]["b"]["key"], data=struct["a"]["b"]["data"])
>
>     if use_merge:
>         b = s.merge(b)
>     else:
>         s.add(b)
>
>     a.b = b
>
>     return a
>
>
> s = Session(e)
> persist_struct(s, struct_one, True)
> s.commit()
>
> # make sure existing identities are loaded, no net change to a.b
> a = persist_struct(s, struct_one, True)
>
> assert inspect(a).attrs.b.history == ((), [a.b], ())
>
>
> # when we *don't* use merge(), and instead stick a brand new B()
> # into the Session, this shows as a change.  would fail on
> # INSERT
>
> a = persist_struct(s, struct_one, False)
> old_b = inspect(a).committed_state['b']
>
> # two bs, same "data", different "identity"
> assert a.b is not old_b
> assert inspect(a).attrs.b.history == ([a.b], (), [old_b])
>
>
>
>
>
>
>> Right now, the only thing I can think of is that perhaps the
>> deserializer is removing and then adding a new object in that
>> relationship's list. The new item has the same data though and the
>> strange thing is, the relationship is very much like the other ones, and
>> they are unaffected. Hm. I will need to investigate further.
>>
>> Thanks!
>>
>> Nick
>>
>> On Thursday, September 15, 2016 at 7:18:52 PM UTC-7, Mike Bayer wrote:
>>
>>
>>
>>     On 09/15/2016 08:04 PM, 'Nicholas A Fries' via sqlalchemy wrote:
>>     > I have a typo for item #7 in my post - this is unexpected.
>>     >
>>     > Cases 7 - 10 are the problem. We don't expect there to be changes
>>     > showing for data that is the same as in the database.
>>
>>     having a hard time following this.   In your paste, I see numbered
>>     tests, "1.1", "1.2", "1.3", "2.1", "2.2", "3.1".  That is...6 cases?
>>     not 10 ?
>>
>>     Also, the workings of GetUncommittedChangesDict are extremely
>>     complicated and appear to be omitting in the print output the contents
>>     of "history.deleted", placing "oldValue=None" inside of changedDict
>>     which is what's printed out.    The example here overall has *ten*
>>     different models, is not in a format I can run, and also I don't
>>     support
>>     sqlalchemy-marshmallow so I don't know anything about it.   So I can't
>>     offer too much insight into what's going on here without a vastly more
>>     succinct and clear demonstration of a single, simple failure with as
>>     little extraneous conditional logic and other code as possible.
>>
>>     The history system is very simple.   If the attribute we have locally
>>     does not compare as True to the database loaded attribute using ==
>>     for a
>>     scalar value, or using "is" for an object value, you have a history
>>     event.    Read the source code to sqlalchemy.orm.attributes.History
>>     within the methods History.from_scalar_attribute,
>>     History.from_object_attribute, History.from_collection to get some
>> idea
>>     here.
>>
>>
>>     >
>>     > On Thursday, September 15, 2016 at 5:01:40 PM UTC-7, Nicholas A
>>     Fries wrote:
>>     >
>>     >     Hi guys,
>>     >
>>     >     The history system in SQLAlchemy seems to think that an object
>>     with
>>     >     the same column values is different than what's in the database.
>>     >     Only it's not.
>>     >
>>     >     Here's a test case I wrote really quick to demonstrate the
>>     issue -
>>     >     https://paste.pound-python.org/show/5FtE85ve08hVoByT6WNj/
>>     <https://paste.pound-python.org/show/5FtE85ve08hVoByT6WNj/>
>>     >     <https://paste.pound-python.org/show/5FtE85ve08hVoByT6WNj/
>>     <https://paste.pound-python.org/show/5FtE85ve08hVoByT6WNj/>>
>>     >
>>     >      1. Given a single JSON string full of data
>>     >      2. Deserialize to a new instance of a model called firstRow
>>     (using
>>     >         marshmallow-sqlalchemy)
>>     >      3. Observe that the row shows a change for all columns and
>> child
>>     >         objects
>>     >      4. Add / Commit the row to the database
>>     >      5. Deserialize to a new instance of a model called secondRow
>>     >         (again, using marshmallow-sqlalchemy)
>>     >      6. marshmallow-sqlalchemy will return the persistent row from
>>     the
>>     >         database and apply the changes over the top (there are no
>>     >         changes in this case)
>>     >      7. Checking the Session.dirty at this point shows a changed
>>     state
>>     >         (expected - we used the same data)
>>     >      8. Checking the hasChanged function I've created shows the
>>     websites
>>     >         column has changed
>>     >      9. Checking the contents of websites shows the same data when
>>     done
>>     >         for firstRow (pre-commit) and secondRow
>>     >     10. get_history for the websites column shows it was deleted and
>>     >         re-added
>>     >
>>     >     So, what is strange is that it only happens for the website
>>     >     relationship (and one other column from the parent table in my
>>     >     actual test environment.) If it was marshmallow-sqlalchemy,
>>     wouldn't
>>     >     it do this for all the relationships? Also, the values are the
>>     same
>>     >     - shouldn't SQLAlchemy check for this?
>>     >
>>     >     Anyway - does anyone have any thoughts on this?
>>     >
>>     >
>>     >
>>     > --
>>     > 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 <javascript:>
>>     > <mailto:sqlalchemy+unsubscr...@googlegroups.com <javascript:>>.
>>     > To post to this group, send email to sqlal...@googlegroups.com
>>     <javascript:>
>>     > <mailto:sqlal...@googlegroups.com <javascript:>>.
>>     > Visit this group at https://groups.google.com/group/sqlalchemy
>>     <https://groups.google.com/group/sqlalchemy>.
>>     > For more options, visit https://groups.google.com/d/optout
>>     <https://groups.google.com/d/optout>.
>>
>> --
>> 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
>> <mailto:sqlalchemy+unsubscr...@googlegroups.com>.
>> To post to this group, send email to sqlalchemy@googlegroups.com
>> <mailto:sqlalchemy@googlegroups.com>.
>> Visit this group at https://groups.google.com/group/sqlalchemy.
>> For more options, visit https://groups.google.com/d/optout.
>>
>
> --
> You received this message because you are subscribed to a topic in the
> Google Groups "sqlalchemy" group.
> To unsubscribe from this topic, visit https://groups.google.com/d/to
> pic/sqlalchemy/TKyszEueyIg/unsubscribe.
> To unsubscribe from this group and all its topics, 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.
>

-- 
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.

Reply via email to