Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

The SQLAlchemy documentation says " session.merge() reconciles the current state of an instance and its associated children with existing data in the database".

Does the existing object's state ever get updated by newer data from the database? How? When?

SQLAlchemy is designed to have single object with each identity in session. But sometimes you have to recreate an object with known identity, e.g. when you get it from network or when you implement offline lock to avoid long transactions. And when you create an object with known identity that might exist in database, there is a chance that session already track an object with this identity. That's what merge() method is for: it returns an object attached to the session, thus avoiding duplicate objects with the same identity in the session. Below is an example illustrating what is going on:

from sqlalchemy import *
from sqlalchemy.orm import *
metadata = MetaData()
t = Table(
    't', metadata,
    Column('id', Integer, primary_key=True),
    Column('state', String(10)),
class Model(object): pass
mapper(Model, t)
engine = create_engine('sqlite://')
metadata.create_all(engine)
session = sessionmaker(bind=engine)()
obj1 = Model()
obj1.state = 'value1'
session.add(obj1)
session.commit()
obj_id = obj1.id
obj2 = Model()
obj2.id = obj_id
obj2.state = 'value2'
obj3 = session.merge(obj2)
session.commit()
print obj3 is obj1, obj3 is obj2
print obj3.state
The output is:
True False
value2
Thus session.merge(obj2) discovers that there is an object with the same identity (obj1 created above), so it merges the state of obj2 into existing object and returns it.
Below is another example, which illustrate loading state from database:
# ...skipped...
t = Table(
    't', metadata,
    Column('id', Integer, primary_key=True),
    Column('state1', String(10)),
    Column('state2', String(10)),
# ...skipped...
obj1 = Model()
obj1.state1 = 'value1-1'
obj1.state2 = 'value2-1'
session.add(obj1)
session.commit()
obj_id = obj1.id
session.expunge_all()
obj2 = Model()
obj2.id = obj_id
obj2.state1 = 'value1-2'
obj3 = session.merge(obj2)
session.commit()
print obj3 is obj1, obj3 is obj2
print obj3.state1, obj3.state2
The output is:
False False
value1-2 value2-1
Now merge() didn't find the object with the same identity in the session, since we expunged it. Also I created new object with state partially assigned, but the rest is loaded from database.
                Thanks for nice explanation, I had some shadows on the idea behind merge(), now I understand it
– Humoyun Ahmad
                Feb 8, 2017 at 5:27
One thing I noticed about merge is that even accessing a field on the unattached object will cause it to be modified during a merge.  
For example, if I have a simple class
class X(Base):
    __tablename__= 'x'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    value = Column(String)
and a row
(1, 'foo', 'bar')
then the following seems to merge nicely:
x = X(id=1)
merged_x = session.merge(x)
print merged_x.name         # 'foo'
print merged_x.description  # 'bar'
But if I even read name or description, this happens
x = X(id=1)
print x.name                # None
merged_x = session.merge(x)
print merged_x.name         # None
print merged_x.description  # 'bar'
This is counterintuitive and, I'd argue, incorrect.  Is there a way to turn off this behavior?  Apparently the mere presence of an attribute in __dict__ causes this to happen. This 'feature' should be noted in the documentation.
                I'm not sure I agree with the edit @shane.  Can you explain?  What I meant in the original text was that if I accessed x.y and then ran session.merge(x) then x.y wouldn't get the merged value.  Do you mean will 'flag' it as being modified (and thus prevent it from being overwritten) in the merge?
– Adam Donahue
                Feb 1, 2017 at 19:59
                Yes, that is exactly what I meant. I ran into the same issue, but was not certain about the way it was worded here. I took 'modified' to mean 'persisted to database', as merged_x now represents newly persistent data. After session.merge(x), the values of attributes of x are not modified at all (and not meant to be), merged_x is a new object.
– shane
                Feb 1, 2017 at 20:26
                From documentation: The state of the given instance is then copied onto the located/newly created instance. For attributes which are present on the source instance, the value is transferred to the target instance. For mapped attributes which aren’t present on the source, the attribute is expired on the target instance, discarding its existing value
– shane
                Feb 1, 2017 at 21:04
                Honestly I asked this so long ago I've forgotten the context, but if by source you mean the data in the database (what's being merged from), then this seems wrong.  The point I was making was that if I merge something into a field I haven't written to I'd expect it to update that field.  (Dissimilar to a SELECT FOR UPDATE.) . I create an object with id=1.  I access .name on that object (it has a value of None). I merge in the database data (where the value is non-None).  I'd expect the merge to update .name. This behavior is consistent with how database reads typically occur.
– Adam Donahue
                Feb 2, 2017 at 19:56
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.