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