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
-~----------~----~----~----~------~----~------~--~---
