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.