Interesting to know. Appreciate the examples!
On Sat, 2009-01-10 at 05:38 -0800, MikeCo wrote: > 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 <[email protected]> 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 <[email protected]> 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 [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 -~----------~----~----~----~------~----~------~--~---
