Michael, Thank you,
reading trough your example (and running it) made me understand a bit more how SQLA works. I managed to get it all working, Since I was on my way to something a bit slightly more complex ...one last Question What if I would like to: Class RelationType(Base): __tablename__ = 'relationtypes' Id = Column(Integer, primary_key=True) ChildRelationType = Column(Unicode(20)) ParentRelationType = Column(Unicode(20)) Some data for information: Id ChildRelationType ParentRelationType 1 "Parent" "Child" 2 "Supplier" "Customer" I will put additional logic to print Mother - Son, in Fact this is not really what I am doing but I feel the need to make it a bit more understandable for the MailingList At the end it will be something with molecules, not really interesting and easy to understand. > > relation_table = Table('relation', Base.metadata, > Column('rid', Integer, ForeignKey('affiliations.id'), primary_key=True), Column('RelationType', integer, ForeignKey('relationtypes.Id')), > Column('lid', Integer, ForeignKey('affiliations.id'), primary_key=True), > ) Adding this column to the relation_table does not make a difference but.... Somehow I need to get a reference to the "type of relation" Setting the relation_type MUST be done whilst "Appending" it. i.e. every relation between "Affiliates" must have a reason Is this possible or is there another way to do such a thing, I feel logically it should be part of the relation_table. Thank you Again for your great help. I'm Not really good in Database stuff, but did/do a lot on the mod_python/mod_wsgi lists so I know about the huge amounts of time spent.... Martijn On Jan 4, 2011, at 7:20 PM, Michael Bayer wrote: > from sqlalchemy import * > from sqlalchemy.orm import * > from sqlalchemy.ext.declarative import declarative_base > > Base = declarative_base() > > relation_table = Table('relation', Base.metadata, > Column('rid', Integer, ForeignKey('affiliations.id'), primary_key=True), > Column('lid', Integer, ForeignKey('affiliations.id'), primary_key=True), > ) > > class Affiliation(Base): > __tablename__ = "affiliations" > id = Column(Integer, primary_key=True) > discriminator = Column('type', Unicode(20)) > __mapper_args__ = {'polymorphic_on': discriminator} > > ParentRelation = relation( > 'Affiliation', > primaryjoin=relation_table.c.rid==id, > secondaryjoin=relation_table.c.lid==id, > secondary=relation_table, > backref="ChildRelation") > > class Person(Affiliation): > __tablename__ = 'persons' > id = Column(Integer, ForeignKey('affiliations.id'), primary_key=True) > __mapper_args__ = {'polymorphic_identity':'person'} > > class Company(Affiliation): > __tablename__ = 'companies' > id = Column(Integer, ForeignKey('affiliations.id'), primary_key=True) > __mapper_args__ = {'polymorphic_identity':'company'} > > e = create_engine('sqlite://', echo =True) > Base.metadata.create_all(e) > sess = Session(e) > > p1, p2, c1, c2 = Person(), Person(), Company(), Company() > > p1.ParentRelation.append(c1) > p1.ChildRelation.append(c2) > c1.ParentRelation.append(p2) > c2.ChildRelation.append(p1) > > sess.add_all([p1, p2, c1, c2]) > sess.commit() -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@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.