the stack trace tells all for autoflush situations. Note this is an 0.7
stacktrace, 0.6 is slightly different but the same series of steps:
File "test.py", line 107, in <module>
group = Group([item1, item2])
File "<string>", line 4, in __init__
File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/state.py", line 105,
in initialize_instance
return manager.original_init(*mixed[1:], **kwargs)
File "test.py", line 68, in __init__
----> 1. self.items_by_owner[item.owner] = item
File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/ext/associationproxy.py",
line 600, in __setitem__
self.col[key] = self._create(key, value)
File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/ext/associationproxy.py",
line 585, in _create
----> 2. return self.creator(key, value)
File "test.py", line 62, in create_for_proxy
---> 3. return cls(None, owner, item)
File "<string>", line 4, in __init__
File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/state.py", line 105,
in initialize_instance
return manager.original_init(*mixed[1:], **kwargs)
File "test.py", line 57, in __init__
---> 4, 5. self.item = item
File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/attributes.py", line
149, in __set__
instance_dict(instance), value, None)
File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/attributes.py", line
642, in set
value = self.fire_replace_event(state, dict_, value, old, initiator)
File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/attributes.py", line
662, in fire_replace_event
value = fn(state, value, previous, initiator or self)
File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/attributes.py", line
932, in set_
---> 6. passive=PASSIVE_NO_FETCH)
File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/attributes.py", line
436, in append
self.set(state, dict_, value, initiator, passive=passive)
File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/attributes.py", line
638, in set
----> 7. old = self.get(state, dict_, passive=PASSIVE_ONLY_PERSISTENT)
File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/attributes.py", line
414, in get
value = self.callable_(state, passive)
File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/strategies.py", line
542, in _load_for_state
---> 8. result = q.all()
File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/query.py", line 1636,
in all
return list(self)
File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/query.py", line 1745,
in __iter__
self.session._autoflush()
File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/session.py", line 901,
in _autoflush
---> 9. self.flush()
1. items_by_owner assignment
2. association proxy receives assignment, calls creator
3. creator is GroupOwner.create_for_proxy
4. GroupOwner constructor
5. assign self.item =item
6. group_owners backref must assign
7. group_owners backref is uselist=False, old value must be placed in the
"deleted" collection since new value replaces. "deleted" collection is so
session knows to assign NULL to the item's foreign key.
8. Item.group_owners is not present in __dict__. Old value must be loaded.
There's no old value in this case, but SQLA doesn't know that until it loads
9. autoflush
Solution 1:
initialize group_owners to None:
class Item(object):
def __init__(self, name, owner):
self.name = name
self.owner = owner
self.group_owners = None
solution 2:
disable autoflush in the GroupOwner constructor. See
http://www.sqlalchemy.org/trac/wiki/UsageRecipes/DisableAutoflush .
On Feb 17, 2011, at 5:56 AM, Julien Demoor wrote:
> Hi,
>
> I'm running into a problem illustrated by the code below. The result
> is an IntegrityError:
>
> sqlalchemy.exc.IntegrityError: (IntegrityError) null value in column
> "group_id" violates not-null constraint
> 'INSERT INTO groups_owners (owner_id, item_id) VALUES (%(owner_id)s, %
> (item_id)s) RETURNING groups_owners.group_id' {'item_id': 1,
> 'owner_id': 1}
>
> Looking at the stack trace, autoflush is triggerred by the assignments
> in GroupOwner.__init__(), but I fail to see why or what to do about
> it.
>
> The error appeared when I set a backref with uselist=False on the
> GroupOwner.item relationship. I can work around the problem by using a
> list instead, although that would make less sense since there's at
> most one group per item. Is there an error in my relationship
> configuration?
>
> I'm running SA 0.6.6.
>
> ==== Code to reproduce the error ====
>
> from sqlalchemy.ext.associationproxy import association_proxy
> from sqlalchemy.orm.collections import attribute_mapped_collection
> from sqlalchemy import Table, Column, Integer, Numeric, String,
> ForeignKey, MetaData
> from sqlalchemy.engine import create_engine
> from sqlalchemy.orm import sessionmaker, mapper, relationship, backref
>
> meta = MetaData()
> engine = create_engine('postgresql://test:test@localhost/test')
> Session = sessionmaker(bind=engine)
> session = Session()
>
> owners = Table("owners", meta,
> Column('id', Integer, primary_key=True),
> Column('name', String)
> )
>
> items = Table("items", meta,
> Column('id', Integer, primary_key=True),
> Column('name', String),
> Column('owner_id', Integer, ForeignKey('owners.id'))
> )
>
> groups = Table("groups", meta,
> Column('id', Integer,primary_key=True),
> )
>
> groups_owners = Table("groups_owners", meta,
> Column('group_id', Integer,
> ForeignKey('groups.id'), primary_key=True),
> Column('owner_id', Integer,
> ForeignKey('owners.id'), primary_key=True),
> Column('item_id', Integer,
> ForeignKey('items.id'), nullable=False, unique=True) # notice
> unique=True; items can belong to one group at most
> )
>
> def _create_holding(stock, shares):
> """A creator function, constructs Holdings from Stock and share
> quantity."""
> return Holding(stock=stock, shares=shares)
>
> class Owner(object):
> def __init__(self, name):
> self.name = name
>
> holdings = association_proxy('by_stock', 'shares',
> creator=_create_holding)
>
> class Item(object):
> def __init__(self, name, owner):
> self.name = name
> self.owner = owner
>
>
> class GroupOwner(object):
> def __init__(self, group, owner, item):
> self.group = group
> self.owner = owner
> self.item = item
> #assert item.owner is owner
>
> @classmethod
> def create_for_proxy(cls, owner, item):
> return cls(None, owner, item)
>
> class Group(object):
> def __init__(self, items):
> if items:
> for item in items:
> self.items_by_owner[item.owner] = item
>
> items_by_owner = association_proxy('group_owners', 'item',
> creator=GroupOwner.create_for_proxy)
>
>
> mapper(Owner, owners)
> mapper(Item, items, properties={
> 'owner': relationship(Owner),
> })
> mapper(Group, groups, properties={
> 'group_owners': relationship(GroupOwner,
> collection_class=attribute_mapped_collection('owner')),
> })
> mapper(GroupOwner, groups_owners, properties={
> 'group': relationship(Group),
> # Fails
> 'item': relationship(Item, backref=backref('group_owners',
> uselist=False), single_parent=True),
> # Doesn't fail
> #'item': relationship(Item, backref=backref('group_owners'),
> single_parent=True),
> 'owner': relationship(Owner),
> })
>
> meta.drop_all(bind=engine, checkfirst=True)
> meta.create_all(bind=engine)
>
> owner1 = Owner('owner 1')
> item1 = Item('item 1', owner1)
>
> owner2 = Owner('owner 2')
> item2 = Item('item 2', owner2)
>
> session.add(owner1)
> session.add(owner2)
> session.add(item1)
> session.add(item2)
>
> session.flush()
>
> # Fails:
> group = Group([item1, item2])
> # Doesn't fail
> #group = Group([item1])
>
>
> session.add(group)
> session.commit()
>
> assert session.query(GroupOwner).filter_by(group_id=1, item_id=1,
> owner_id=1).first() is not None
> print 'Done.'
>
> --
> 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.
>
--
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.
