relationship with join to primary key column results in error

if the following is defined:

parent table:

members = sqlalchemy.Table(
    'members',z3c.zalchemy.metadata,
    sqlalchemy.Column('userName', sqlalchemy.String,primary_key = True),
    sqlalchemy.Column('firstName', sqlalchemy.Unicode),
    sqlalchemy.Column('lastName', sqlalchemy.Unicode),
    sqlalchemy.Column('contactEmail', sqlalchemy.Unicode),
    sqlalchemy.Column('photo', sqlalchemy.Binary,
                      sqlalchemy.PassiveDefault("")),
    redefine=True
    )

parent class mapper:

sqlalchemy.mapper(Member,members,properties={
    '_personalAddress':sqlalchemy.relation(AddressInfo,
                                    uselist=False,
                                    backref='__parent__',
                                    cascade="all, delete-orphan",
                                    primaryjoin=sqlalchemy.and_(
    members.c.userName==addresses.c.userName, 
    addresses.c.addressType==1
    )),})

joined table:

addresses = sqlalchemy.Table(
    'addresses',z3c.zalchemy.metadata,
    sqlalchemy.Column('userName', sqlalchemy.Unicode,
                      sqlalchemy.ForeignKey('members.userName'),
                      primary_key=True,
                      nullable=False),
    sqlalchemy.Column('addressType', sqlalchemy.Integer,
                      nullable=False,
                      primary_key=True),
    sqlalchemy.Column('organisation', sqlalchemy.Unicode),
    sqlalchemy.Column('address', sqlalchemy.Unicode),
    sqlalchemy.Column('zipCode', sqlalchemy.Unicode),
    sqlalchemy.Column('city', sqlalchemy.Unicode),
    sqlalchemy.Column('state', sqlalchemy.Unicode, default=u'AT'),
    sqlalchemy.Column('email', sqlalchemy.Unicode),
    sqlalchemy.Column('phone', sqlalchemy.Unicode),
    sqlalchemy.Column('homePage', sqlalchemy.Unicode),
    sqlalchemy.Column('isContact', sqlalchemy.Boolean),
    )

it results in the following error

Traceback (most recent call last):
  File "/Users/bd/zopes/fhvao/lib/python/fhvao/tests.py", line 5, in ?
    from fhvao.member import members,Member
  File "/Users/bd/zopes/fhvao/lib/python/fhvao/member.py", line 185, in ?
    addresses.c.addressType==1
  File "/Users/bd/zopes/fhvao/lib/python/sqlalchemy/orm/__init__.py", line 50, in mapper
    return Mapper(class_, table, *args, **params)
  File "/Users/bd/zopes/fhvao/lib/python/sqlalchemy/orm/mapper.py", line 253, in __init__
    prop.init(key, self)
  File "/Users/bd/zopes/fhvao/lib/python/sqlalchemy/orm/mapper.py", line 866, in init
    self.do_init(key, parent)
  File "/Users/bd/zopes/fhvao/lib/python/sqlalchemy/orm/properties.py", line 215, in do_init
    self._find_dependent()
  File "/Users/bd/zopes/fhvao/lib/python/sqlalchemy/orm/properties.py", line 291, in _find_dependent
    self.primaryjoin.accept_visitor(visitor)
  File "/Users/bd/zopes/fhvao/lib/python/sqlalchemy/sql.py", line 858, in accept_visitor
    c.accept_visitor(visitor)
  File "/Users/bd/zopes/fhvao/lib/python/sqlalchemy/sql.py", line 947, in accept_visitor
    visitor.visit_binary(self)
  File "/Users/bd/zopes/fhvao/lib/python/sqlalchemy/orm/properties.py", line 752, in visit_binary
    self.func(binary)
  File "/Users/bd/zopes/fhvao/lib/python/sqlalchemy/orm/properties.py", line 282, in foo
    raise exceptions.ArgumentError("bidirectional dependency not supported...specify foreignkey")
ArgumentError: bidirectional dependency not supported...specify foreignkey

this only happens if the column in the join is a primary_key in the child table, if you change the code above to:

    sqlalchemy.Column('addressType', sqlalchemy.Integer,
                      nullable=False,
                      primary_key=False),

it works.

a forther question:

shouldn't the addressType be set to 1 automatically if i assign an address object to obj._personalAddress which has no addressType specified?

Regards, Bernd

Comments (4)