On Aug 26, 2011, at 4:06 PM, Thomas Jacob wrote: > > On Aug 26, 2011, at 9:36 PM, Michael Bayer wrote: > >> the error message here isn't fantastic but I think you mean to say: >> >> oa = ObjectAssociation() >> o2.relate_down.append(oa) >> o.relate_up.append(oa) >> >> as relate_up/relate_down accept ObjectAssociation objects, not Object >> objects, as members. > > Oops, that sounds plausible. I guess I was tacitly assuming that > SQLalchemy would somehow abstract away from the association > object and let me create relations between the Objects directly (but then how > would I define > the extra attributes of the association?).
So the next step with association objects is to use a proxy that will in most cases hide the "middle" object. I use these to a huge degree and you can really do some amazing stuff with them. You can read up on that at http://www.sqlalchemy.org/docs/orm/extensions/associationproxy.html . > > Thanks! > >> >> >> >> On Aug 26, 2:53 pm, Thomas Jacob <[email protected]> wrote: >>> Hello List >>> >>> I'd like to define a many-to-many relationship using the declarative >>> syntax between a table to itself, but employing an association >>> object (since I require the association to have attributes >>> also). >>> >>> Here's what I tried: >>> >>> #================================================================= >>> >>> from sqlalchemy import * >>> from sqlalchemy.ext.declarative import declarative_base >>> from sqlalchemy.orm import sessionmaker, relation, backref >>> >>> DB_engine = create_engine('sqlite:///:memory:', echo=True) >>> DB_Base = declarative_base(bind=DB_engine) >>> DB_Session = sessionmaker() >>> DB_Session.configure(bind=DB_engine) >>> DB_session = DB_Session() >>> >>> class Object(DB_Base): >>> __tablename__ = 'object' >>> >>> object_id = Column(Integer, primary_key=True) >>> label = Column(Text) >>> >>> class ObjectAssociation(DB_Base): >>> __tablename__ = 'object_association' >>> __table_args__ = ( >>> PrimaryKeyConstraint('left_object_id', 'right_object_id'), >>> {} >>> ) >>> >>> left_object_id = Column(Integer, ForeignKey('object.object_id')) >>> right_object_id = Column(Integer, ForeignKey('object.object_id')) >>> label = Column(Text) >>> >>> left_object = relation(Object, >>> primaryjoin=(left_object_id==Object.object_id), >>> backref=backref("relate_down") >>> ) >>> right_object = relation(Object, >>> primaryjoin=(right_object_id==Object.object_id), >>> backref=backref("relate_up") >>> ) >>> >>> DB_Base.metadata.create_all() >>> >>> o = Object() >>> o.label = 'Object1' >>> >>> o2 = Object() >>> o2.label = 'Object2' >>> >>> o2.relate_down.append(o) >>> >>> DB_session.commit() >>> >>> #================================================================= >>> >>> But this raises an exception: >>> >>> #================================================================= >>> >>> 2011-08-26 20:48:28,422 INFO sqlalchemy.engine.base.Engine PRAGMA >>> table_info("object") >>> 2011-08-26 20:48:28,422 INFO sqlalchemy.engine.base.Engine () >>> 2011-08-26 20:48:28,422 INFO sqlalchemy.engine.base.Engine PRAGMA >>> table_info("object_association") >>> 2011-08-26 20:48:28,422 INFO sqlalchemy.engine.base.Engine () >>> 2011-08-26 20:48:28,423 INFO sqlalchemy.engine.base.Engine >>> CREATE TABLE object ( >>> object_id INTEGER NOT NULL, >>> label TEXT, >>> PRIMARY KEY (object_id) >>> ) >>> >>> 2011-08-26 20:48:28,423 INFO sqlalchemy.engine.base.Engine () >>> 2011-08-26 20:48:28,423 INFO sqlalchemy.engine.base.Engine COMMIT >>> 2011-08-26 20:48:28,423 INFO sqlalchemy.engine.base.Engine >>> CREATE TABLE object_association ( >>> left_object_id INTEGER, >>> right_object_id INTEGER, >>> label TEXT, >>> PRIMARY KEY (left_object_id, right_object_id), >>> FOREIGN KEY(left_object_id) REFERENCES object (object_id), >>> FOREIGN KEY(right_object_id) REFERENCES object (object_id) >>> ) >>> >>> 2011-08-26 20:48:28,423 INFO sqlalchemy.engine.base.Engine () >>> 2011-08-26 20:48:28,424 INFO sqlalchemy.engine.base.Engine COMMIT >>> Traceback (most recent call last): >>> File "bug.py", line 53, in <module> >>> o2.relate_down.append(o) >>> File >>> "/usr/local/lib/python2.6/dist-packages/sqlalchemy/orm/collections.py", >>> line 952, in append >>> item = __set(self, item, _sa_initiator) >>> File >>> "/usr/local/lib/python2.6/dist-packages/sqlalchemy/orm/collections.py", >>> line 927, in __set >>> item = getattr(executor, 'fire_append_event')(item, _sa_initiator) >>> File >>> "/usr/local/lib/python2.6/dist-packages/sqlalchemy/orm/collections.py", >>> line 618, in fire_append_event >>> item, initiator) >>> File >>> "/usr/local/lib/python2.6/dist-packages/sqlalchemy/orm/attributes.py", >>> line 745, in fire_append_event >>> value = fn(state, value, initiator or self) >>> File >>> "/usr/local/lib/python2.6/dist-packages/sqlalchemy/orm/attributes.py", >>> line 943, in emit_backref_from_collection_append_event >>> child_state.manager[key].impl.append( >>> KeyError: 'left_object' >>> >>> #=================================================================== >>> >>> Is this a bug? Am I doing something wrong? >>> >>> Any clue as to what is going on here would be greatly appreciated. >>> >>> The platform I ran this on was Ubuntu 10.04's Python 2.6.5 + SQLAlchemy >>> 0.7.2. >> >> -- >> You received this message because you are subscribed to the Google Groups >> "sqlalchemy" group. >> To post to this group, send email to [email protected]. >> To unsubscribe from this group, send email to >> [email protected]. >> 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 [email protected]. > To unsubscribe from this group, send email to > [email protected]. > 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 [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
