session.add() vs session.merge() and delete child
avdd
because I don't want to merge them with the persistent state. But it
appears deletes aren't carrying across to child relations:
$ cat listdelete.py; python listdelete.py
import sqlalchemy as sql
from sqlalchemy import orm
from sqlalchemy.ext.declarative import declarative_base
echo = 0
engine = sql.create_engine("sqlite:///:memory:", echo=bool(echo))
metadata = sql.MetaData(bind=engine)
DB = orm.sessionmaker(bind=engine)
T = declarative_base(metadata=metadata)
class A(T):
__tablename__ = 'a'
id = sql.Column(sql.Integer, primary_key=True)
info = sql.Column(sql.String)
cc = orm.relation('C',
backref='a',
cascade='all,delete-orphan')
def __repr__(self):
return " >> a: %s cc=%s" % (self.info, len(self.cc))
class C(T):
__tablename__ = 'c'
a_id = sql.Column(sql.Integer, sql.ForeignKey('a.id'),
primary_key=True)
i = sql.Column(sql.Integer, primary_key=True)
def get():
return DB().query(A).first()
def change(a, s, i):
orm.object_session(a).close()
db = DB()
db.add(a)
a.info = s
del a.cc[-1]
a.cc.append(C(i=i))
db.close()
metadata.create_all()
A.__table__.delete().execute()
db = DB()
a = A(id=1, info='blah', cc=[C(i=1), C(i=2)])
db.add(a)
db.commit()
db.close()
print get()
# merge and flush
a = get()
change(a, 'change one', 3)
db = DB()
db.merge(a)
db.commit()
db.close()
print get()
# add and flush
a = get()
change(a, 'change two', 4)
db = DB()
db.add(a)
db.commit()
db.close()
print get()
>> a: blah cc=2
>> a: change one cc=2
>> a: change two cc=3
Michael Bayer
On Jan 30, 2010, at 9:07 AM, avdd wrote:
> I'm using session.add() to refresh my objects while working on them,
> because I don't want to merge them with the persistent state. But it
> appears deletes aren't carrying across to child relations:
this example is too compliated for me to understand without great effort, perhaps someone else has the time to follow it more closely - it appears to be creating and closing many new sessions and add()ing objects between them - an unusual series of events. The policy of add() is that it puts an object in the session. If its already there, nothing happens. It doesnt invalidate any state or reconcile with what's currently visible in the transaction, so if the example is attempting to illustrate, "transaction A changed a row, but transaction B doesn't see it!", you'd have to expire the appropriate parts of session B for those changes to be seen.
> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
>
Michael Bayer
On Jan 30, 2010, at 9:07 AM, avdd wrote:
> I'm using session.add() to refresh my objects while working on them,
> because I don't want to merge them with the persistent state. But it
> appears deletes aren't carrying across to child relations:
in r6711 the save-update cascade operation will cascade related objects present in the full history of a scalar- or collection- holding relation() attribute into the new session, thus allowing objects marked as deleted or disassociated from the parent to be present in the new session where they will particpate in the next flush().
avdd
> this example is too compliated for me to understand without great effort, perhaps someone else has the time to follow it more closely - it appears to be creating and closing many new sessions and add()ing objects between them - an unusual series of events.
My use case is this: I am writing a framework that allows you to
create and update objects over a series of requests, stashing the
object in the session between requests. I undersood this to be one of
the supported use-cases of SQLAlchemy.
So the general sequence of events is:
1. create or load object, don't flush, and detach
2. reattach object, modify, don't flush and detach
3. repeat 2 as necessary
4. when ready, reattach object and commit
I had previously used merge(), but that causes too much database
chatter and slows things down considerably. dont_load doesn't support
"dirty" objects. So I saw that add() can reattach a detached instance
to a session, and it all appears to work as expected (namely changing
attributes and adding/changing child relations) *except* child
deletions.
I hope that is clearer.
Actually, I just tried it against 0.6 trunk and this script works. It
doesn't work in 0.5 (r6712)
Sorry about clagging inline, google groups doesn't have an attachment
facility.
"""
import sqlalchemy as sql
from sqlalchemy import orm
from sqlalchemy.ext.declarative import declarative_base
engine = sql.create_engine("sqlite:///:memory:")
metadata = sql.MetaData(bind=engine)
DB = orm.sessionmaker(bind=engine)
T = declarative_base(metadata=metadata)
class A(T):
__tablename__ = 'a'
id = sql.Column(sql.Integer, primary_key=True)
info = sql.Column(sql.String)
cc = orm.relation('C',
backref='a',
cascade='all,delete-orphan')
def __repr__(self):
return " >> a: %s cc=%s" % (self.info, len(self.cc))
class C(T):
__tablename__ = 'c'
a_id = sql.Column(sql.Integer, sql.ForeignKey('a.id'),
primary_key=True)
i = sql.Column(sql.Integer, primary_key=True)
metadata.create_all()
A.__table__.delete().execute()
def get():
return DB().query(A).first()
def change_detached(a, s, i):
# attach instance to a session to operate on it
# then detach it again to store it in a session
# change and attribute, delete one child and add another
# so len(cc) is unchanged
db = DB()
db.add(a)
a.info = s
del a.cc[-1]
a.cc.append(C(i=i))
db.close()
def store(a, merge=False):
db = DB()
if merge:
db.merge(a)
else:
db.add(a)
db.commit()
db.close()
# create and store instance
store(A(id=1, info='blah', cc=[C(i=1), C(i=2)]))
assert len(get().cc) == 2 # ok
# get and detach instance
a = get()
orm.object_session(a).close()
# change 1
change_detached(a, 'change-one', 3)
# store, reattaching with merge()
store(a, True)
assert len(get().cc) == 2 # ok
# get and detach instance
a = get()
orm.object_session(a).close()
# change 2
change_detached(a, 'change-two', 4)
# store, reattaching with add()
store(a, False)
assert len(get().cc) == 2 # fails!
"""
avdd
# called on every request
def refresh_model(context, obj):
context.get_db().add(obj)
def store_model(context, obj):
db = object_session(obj)
if db:
db.expunge(obj)
obj = db.merge(obj)
db.flush()
return obj
Which seems to be working well so far.
Mike, I understand you are busy and all, but I was hoping to learn
what you thought of the usage I mentioned:
Michael Bayer
the behavior you first illustrated, that of merge() and add() not acting
the same regarding pending changes, was a behavior that was somewhat in
the realm of a bug. I mentioned the other day it was fixed in r6711.
The save-update cascade should place all the objects as well as those
which have been removed from collections in the new session.
avdd
> the behavior you first illustrated, that of merge() and add() not acting
> the same regarding pending changes, was a behavior that was somewhat in
> the realm of a bug. I mentioned the other day it was fixed in r6711.
Well no, not in 0.5 (r6712).
Michael Bayer
its not an entirely backwards compatible change, since it is add()ing an
object that otherwise would not be added, thus causing an error if that
object is attached to some other session.
I'd recommend upgrading to 0.6, beta1 release is any day this week I get a
chance to do it.