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.

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.