Thanks for the tip Michael, that solved my problem! It now looks like:
category_association = Table('BankCategoryMap',
Base.metadata,
Column("ParentCategoryID", Integer,
ForeignKey('BankCategories.ID'), primary_key=True),
Column("ChildCategoryID", Integer,
ForeignKey('BankCategories.ID'), primary_key=True))
class Category(Base):
__tablename__ = 'BankCategories'
id = Column("ID", Integer, primary_key=True)
name = Column("Name", String)
description = Column("Description", String)
active = Column("Active", SmallInteger)
parents = relationship("Category",
secondary=category_association,
primaryjoin="Category.id ==
BankCategoryMap.c.ChildCategoryID",
secondaryjoin="BankCategoryMap.c.ParentCategoryID == Category.id",
backref='children')
Which works just dandy.
Thanks again,
Doug
On Mar 3, 3:07 pm, Michael Bayer <[email protected]> wrote:
> On Mar 3, 2011, at 5:11 PM, Doug wrote:
>
>
>
> > Hello all,
>
> > I've just started using SQLAlchemy recently (and it's been great) but
> > I have run into a problem recently. I am trying to map a table back to
> > itself via a second many-to-many table in a declarative fashion. I
> > have this working as so (mapper configuration):
>
> > However, when I try it the Declarative way:
>
> > category_association = Table('BankCategoryMap',
> > Base.metadata,
> > Column("ParentCategoryID", Integer,
> > ForeignKey('BankCategories.ID'), primary_key=True),
> > Column("ChildCategoryID", Integer,
> > ForeignKey('BankCategories.ID'), primary_key=True))
>
> > class Category(Base):
> > __tablename__ = 'BankCategories'
>
> > id = Column("ID", Integer, primary_key=True)
> > name = Column("Name", String)
> > description = Column("Description", String)
> > active = Column("Active", SmallInteger)
>
> > parents = relationship("Category",
> > secondary=category_association,
> > primaryjoin="BankCategories.ID" ==
> > category_association.c.ChildCategoryID,
>
> > secondaryjoin=category_association.c.ParentCategoryID ==
> > "BankCategories.ID",
> > backref='children')
>
> You need to use the actual attributes, or a full string, when using the
> primaryjoin and secondaryjoin arguments. You can't mix and match operators
> with strings. Some FAQ on a similar issue is here:
> http://www.sqlalchemy.org/trac/wiki/FAQ#ImusingDeclarativeandsettingp....
>
>
>
> > This does not work and I get the following error:
>
> > File '/home/doug/pyenv_svn/CreateService/surveyservice/surveyservice/
> > controllers/error.py', line 25 in document
> > cat.parents
> > File '/home/doug/pyenv_svn/CreateService/lib/python2.6/site-packages/
> > SQLAlchemy-0.6.5-py2.6.egg/sqlalchemy/orm/attributes.py', line 170 in
> > __get__
> > instance_dict(instance))
> > File '/home/doug/pyenv_svn/CreateService/lib/python2.6/site-packages/
> > SQLAlchemy-0.6.5-py2.6.egg/sqlalchemy/orm/attributes.py', line 390 in
> > get
> > value = callable_(passive=passive)
> > File '/home/doug/pyenv_svn/CreateService/lib/python2.6/site-packages/
> > SQLAlchemy-0.6.5-py2.6.egg/sqlalchemy/orm/strategies.py', line 670 in
> > __call__
> > result = q.all()
> > File '/home/doug/pyenv_svn/CreateService/lib/python2.6/site-packages/
> > SQLAlchemy-0.6.5-py2.6.egg/sqlalchemy/orm/query.py', line 1576 in all
> > return list(self)
> > File '/home/doug/pyenv_svn/CreateService/lib/python2.6/site-packages/
> > SQLAlchemy-0.6.5-py2.6.egg/sqlalchemy/orm/query.py', line 1688 in
> > __iter__
> > return self._execute_and_instances(context)
> > File '/home/doug/pyenv_svn/CreateService/lib/python2.6/site-packages/
> > SQLAlchemy-0.6.5-py2.6.egg/sqlalchemy/orm/query.py', line 1693 in
> > _execute_and_instances
> > mapper=self._mapper_zero_or_none())
> > File '/home/doug/pyenv_svn/CreateService/lib/python2.6/site-packages/
> > SQLAlchemy-0.6.5-py2.6.egg/sqlalchemy/orm/session.py', line 729 in
> > execute
> > clause, params or {})
> > File '/home/doug/pyenv_svn/CreateService/lib/python2.6/site-packages/
> > SQLAlchemy-0.6.5-py2.6.egg/sqlalchemy/engine/base.py', line 1191 in
> > execute
> > params)
> > File '/home/doug/pyenv_svn/CreateService/lib/python2.6/site-packages/
> > SQLAlchemy-0.6.5-py2.6.egg/sqlalchemy/engine/base.py', line 1269 in
> > _execute_clauseelement
> > parameters=params
> > File '/home/doug/pyenv_svn/CreateService/lib/python2.6/site-packages/
> > SQLAlchemy-0.6.5-py2.6.egg/sqlalchemy/engine/base.py', line 1377 in
> > __create_execution_context
> > connection=self, **kwargs)
> > File '/home/doug/pyenv_svn/CreateService/lib/python2.6/site-packages/
> > SQLAlchemy-0.6.5-py2.6.egg/sqlalchemy/engine/default.py', line 388 in
> > __init__
> > grp,m in enumerate(parameters)]
> > File '/home/doug/pyenv_svn/CreateService/lib/python2.6/site-packages/
> > SQLAlchemy-0.6.5-py2.6.egg/sqlalchemy/sql/compiler.py', line 291 in
> > construct_params
> > pd[self.bind_names[bindparam]] = bindparam.value()
> > File '/home/doug/pyenv_svn/CreateService/lib/python2.6/site-packages/
> > SQLAlchemy-0.6.5-py2.6.egg/sqlalchemy/orm/strategies.py', line 447 in
> > <lambda>
> > state, dict_, bind_to_col[bindparam.key])
> > File '/home/doug/pyenv_svn/CreateService/lib/python2.6/site-packages/
> > SQLAlchemy-0.6.5-py2.6.egg/sqlalchemy/orm/mapper.py', line 1303 in
> > _get_state_attr_by_column
> > return self._columntoproperty[column]._getattr(state, dict_, column,
> > passive=passive)
> > File '/home/doug/pyenv_svn/CreateService/lib/python2.6/site-packages/
> > SQLAlchemy-0.6.5-py2.6.egg/sqlalchemy/orm/mapper.py', line 2510 in
> > __missing__
> > (column, self.mapper))
> > UnmappedColumnError: No column :ChildCategoryID_1 is configured on
> > mapper Mapper|Category|BankCategories...
>
> > Any idea/help on why I'm getting the UnmappedColumnError?
>
> > Thanks,
> > Doug
>
> > --
> > 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
> > athttp://groups.google.com/group/sqlalchemy?hl=en.
--
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.
