As always, thanks for the excellent and thorough response.
It does seem that if the offending object is in session.new when the
commit() fails it is removed from the session without me doing a
rollback(). e.g:
u = User()
session.add(u)
session.commit()
(IntegrityError) user.name may not be NULL u'INSERT INTO user DEFAULT
VALUES' []
u in session
>> False
I guess you can't just rollback(), fix the problem and re-commit()
without re-add()ing the same objects to the session. I assume this is
because sessions are designed to be more fine grained, i.e. create
them for what you need them for and throw them out when you're done.
Not to be reused throughout the life of your application.
On Oct 18, 2:14 pm, Michael Bayer <mike...@zzzcomputing.com> wrote:
> On Oct 18, 2009, at 3:24 PM, Brett wrote:
>
>
>
> > I just came across this issue where if an IntegrityError is raised on
> > session.commit() then the offending object that caused the error is
> > automatically removed from the session.
>
> its removed when you say rollback(). any pending add()s are
> reverted during rollback. since those objects weren't in the
> database before the transaction started, they're not in the session
> after the transaction rolls back.
>
> > If the object is a "child"
> > object is related to "parent" object and the child object is in
> > session.new then it is removed from the parent's collection. If it's
> > not in the session.new then it is removed from the session but not the
> > parent's collection.
>
> the first part is right - the parent's collections are expired during
> the rollback so the collection would be reloaded again from the
> database - and the offending object was never inserted, so it isn't
> there. If the child object is not in session.new, that implies it
> was flushed successfully, so it would not be removed during a
> rollback, since it represents a real row in the database.
>
> The second test case you have illustrates all three of user, address,
> and phone are not in the session after the rollback. All three were
> pending when the transaction failed, so they cannot be "expired" -
> they're sent back out into a non-database-bound context.
>
> In both test cases, after a rollback(), the contents within the
> session are exactly that of which are in the database before the
> transaction.
>
>
>
> > Is this the intended result? If so, why?
>
> > I think this is best explained with code:
>
> > --------+ cut +----------
>
> > from sqlalchemy import *
> > from sqlalchemy.orm import *
> > from sqlalchemy.ext.declarative import declarative_base
>
> > uri = 'sqlite:///:memory:'
>
> > Base = declarative_base()
>
> > class User(Base):
> > __tablename__ = 'user'
> > name = Column(String, primary_key=True)
> > address = relation('Address', uselist=False, cascade='all, delete-
> > orphan')
> > phones = relation('Phone', cascade='all, delete-orphan',
> > backref=backref('accession', uselist=False))
>
> > class Address(Base):
> > __tablename__ = 'address'
> > address = Column(String, primary_key=True)
> > user_key = Column(String, ForeignKey('user.name'))
>
> > class Phone(Base):
> > __tablename__ = 'phone'
> > number = Column(String, primary_key=True)
> > user_key = Column(String, ForeignKey('user.name'))
>
> > engine = create_engine(uri)
> > Base.metadata.bind = engine
> > Base.metadata.create_all()
> > session = sessionmaker()()
>
> > def test1():
> > """Create the users and address and commit() the changes and then
> > later add the phone number...in this case the phone number will be
> > removed from user.phones and the session
> > """
> > user = User(name='Bob')
> > address = Address(address='Somewhere')
> > user.address = address
> > session.add_all([user, address])
> > session.commit()
>
> > phone = Phone()
> > user.phones.append(phone)
> > session.add(phone)
>
> > try:
> > session.commit()
> > except Exception, e:
> > session.rollback()
> > print e
>
> > assert phone in user.phones # fails
> > assert phone in session # fails
>
> > def test2():
> > """Create the users and address and commit() the changes and then
> > later add the phone number...in this case the phone number will
> > still be in user.phones but removed from the session
> > """
> > user = User(name='Bob')
> > address = Address(address='Somewhere')
> > phone = Phone()
> > user.phones.append(phone)
> > user.address = address
> > session.add_all([user, address, phone])
>
> > objs = list(session)
>
> > try:
> > session.commit()
> > except Exception, e:
> > session.rollback()
> > print e
>
> > # if we re-add all the objects that were in the session before the
> > # commit and fix the problem then then everything works fine...
> > # is there a downside to this?
> > #session.add_all(objs)
> > #phone.number=1
> > #session.commit()
>
> > assert user.address == address # passes
> > assert phone in user.phones # passes
> > assert phone in session # fails
>
> > #test1()
> > test2()
>
> > -------------------------
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
