One thing to notice about ignoring the ids completely. This can introduce inefficiencies when updating records. In the sample function tst3() the line
child.parent = parent causes SA to read the old parent record to get the attribute child.parent. Changing to: child.id_d = parent.id eliminates the extra SELECT statement. Inefficiencies like that are pretty rare. On Jan 9, 11:33 pm, MikeCo <mconl...@gmail.com> wrote: > I spent some time understanding this a little while ago. Here is the > construct you need > (thanks to Michael Bayer for making it clear for me) > > # D self referential > class D(Base): > __tablename__ = 'D' > id = Column(Integer, primary_key=True) > id_d = Column(Integer, ForeignKey('D.id')) > name = Column(Text) > child = relation('D', backref=backref('parent', > remote_side='D.id')) > > The 'child' relation establishes the two way linkage you want. > > One of the key things to think about is to use objects as normal > python data structures. Generally you don't need to manipulate ids; > let SQLAlchemy worry about that. > > Here is sample script I used when learning the topic > > - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - > > from sqlalchemy import * > from sqlalchemy.orm import * > from sqlalchemy.ext.declarative import declarative_base > > dbname = '' > dburl = 'sqlite:///%s' % dbname > > # set up environment > eng = create_engine(dburl, echo=False) > meta = MetaData(bind=eng) > Base = declarative_base(metadata=meta) > Session = sessionmaker() > > # D self referential > class D(Base): > __tablename__ = 'D' > id = Column(Integer, primary_key=True) > id_d = Column(Integer, ForeignKey('D.id')) > name = Column(Text) > child = relation('D', > backref=backref('parent', remote_side='D.id')) > def __repr__(self): > return '--<D> (id:%s->id_d:%s) %s --' % (self.id, self.id_d, > self.name) > > def initdb(): > print '# create the database' > meta.drop_all(checkfirst=True) > meta.create_all() > sess = Session() > > # insert some data for self-reference relationship > for ddata in ('d-one', 'd-two'): > dobj = D(name=ddata) > sess.add(dobj) > dobj2 = D(name=ddata+'-child1') > dobj.child.append(dobj2) > dobj2 = D(name=ddata+'-child2') > dobj.child.append(dobj2) > dobj3 = D(name=ddata+'-child2'+'-child3') > dobj2.child.append(dobj3) > sess.commit() > sess.close() > > def printobj(obj, indent=''): > # recursive list > print '%s%s' % (indent, obj) > if hasattr(obj, 'child'): > for ch in obj.child: > printobj(ch, indent=indent+' ') > > def listd(): > # retrieve and list > sess = Session() > print '# D->D' > query = sess.query(D).filter(D.id_d==None).order_by(D.name) > for dobj in query: > printobj(dobj) > sess.close() > > def tst3(): > # do stuff with self referential class D > sess = Session() > print "#switch parentage of name 'd-two-child1' to 'd-one'" > print '# before' > listd() > child = sess.query(D).filter(D.name=='d-two-child1').one() > parent = sess.query(D).filter(D.name=='d-one').one() > eng.echo=True > child.parent = parent > child.name += ' MOVED' > sess.commit() > eng.echo=False > print '# after' > listd() > > initdb() > tst3() > - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - > > On Jan 9, 10:03 pm, Darren Govoni <dar...@ontrenet.com> wrote: > > > Hi, > > Thanks for the response. I'm still fairly new to SA but am very > > impressed with this package! > > > I tried a variety of combinations of mappings. If I use just the > > parent_id,parent it would seem to make sense logically if my children > > only have one parent. > > > The tables generate fine. But when I try to set the parent of a child at > > runtime it throws an exception about Conversion not being a list or > > collection or something. So I was experimenting with the M2M, and as you > > see, its not right. > > > Logically, the self referring parent should be enough I would think. Is > > there another way using Base without having to use another mapping > > technique since all my other classes are Base derived? > > > Thank you. > > > On Fri, 2009-01-09 at 20:17 -0500, Michael Bayer wrote: > > > > On Jan 9, 2009, at 7:42 PM, project2501 wrote: > > > > > Hi, > > > > I'm trying to create a mapped class that allows parent/child > > > > relation of itself. I've spent the last 3 hours searching and reading > > > > the other posts on this, but can't get anything to work. > > > > > I'm on 0.5rc4 and just want to declare this in a mapped class as such: > > > > > class Conversion(Base): > > > > __tablename__ = 'conversion' > > > > > id = Column(Integer, primary_key=True) > > > > name = Column(String(20)) > > > > > parent_id = Column(Integer, ForeignKey('conversion.id')) > > > > parent = relation("Conversion", backref="child") > > > > > children = relation('Conversion', secondary=conversion_conversion, > > > > backref='parents') > > > > > I was hoping to use the same style many-to-many declarations I do > > > > elsewhere. It creates fine, but doesn't work when adding objects. > > > > The many-to-many is possible but is not really necessary for the > > > typical self-referential mapping. It depends on if you'd like a > > > single object to have many parents. However in your example above, it > > > appears you are creating two separate self-referring bidirectional > > > relationships, "parent/child" (which is also misconfigured, the many- > > > to-one side needs a remote_side argument) and "parents/children". > > > This would create two entirely isolated methods of associating one > > > Conversion object with another - there's no link between parent/child > > > and parents/children. Is this the desired effect ? --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---