5dollarwhitebox.org - theboxownsyou

It has been a while since I felt inclined to put my face through a wall, and endure physical pain rather than be defeated yet another hour (or day) on a Python/TurboGears road block. Well, over the last few days I have been stumped by self-referential relationships in SQLAlchemy. For those not familiar, this is a simple yet common scenario where a Model has a parent or child of itself that is the same Model. For example:

class Package(DeclarativeBase):
    _tablename_ = 'packages'
    id = Column(Integer, primary_key=True)
    label = Column(Unicode(32), nullable=False)
    parent_id = Column(Integer, ForeignKey('packages.id'), nullable=True)
    ...

In this model, I have a Package... that might have a parent package, or it might _be_ a parent and have children. Reading the SQLA documentation didn't get me too much closer to getting this to work, rather following the examples I would constantly get errors similar to:

ArgumentError: Relation Package.parent could not determine any local/remote column 
pairs from remote side argument set([<sqlalchemy.sql.expression.ColumnClause at 
0x102ef75d0; Package.c.id>])

After some googling around I was able to come up with the following solution thanks to a post on StackOverflow:

class Package(DeclarativeBase):
    _tablename_ = 'packages'
    id = Column(Integer, primary_key=True)
    label = Column(Unicode(32), nullable=False)
    parent_id = Column(Integer, ForeignKey('packages.id'), nullable=True)
    parent = relation('Package', remote_side=[id], backref='children')

Note the "parent" relation that I've created. This should allow for a bi-directional relationship between Parent and Child packages. Now I can move on to finishing the last two days of work I've not been able to get to because of this little pest.

Thanks to mpedersen in #turbogears for putting me in the right direction as well.