While I was writing up a many-to-many relationship between tables
where one has a composite primary key, I ran into problems that
initially prompted me to start writing this post.
Here's my original code that was giving the confusing error about
which I originally began writing:
class AccountSyncedContact(Base):
__tablename__ = 'account_synced_contact'
account_name = Column(String(128),
ForeignKey('account.account_name'), primary_key=True)
contact_id = Column(Integer, ForeignKey('contact.id'),
primary_key=True)
contact = relationship('ZKContact', lazy='joined')
groups = relationship('ZKGroup',
secondary='account_synced_contact_group')
class Group(Base):
__tablename__ = 'group'
id = Column(Integer, primary_key=True)
#@ReservedAssignment
name = Column(String(128), nullable=False, unique=True)
service = Column(String(128), nullable=False)
list_name = Column(String(128), nullable=False)
description = Column(String(512), nullable=True)
account_synced_contact_group = Table('account_synced_contact_group',
Base.metadata,
Column('account_name', String(128), primary_key=True),
Column('contact_id', Integer, primary_key=True),
Column('group_id', Integer, ForeignKey('group.id'),
primary_key=True),
ForeignKeyConstraint(['account_name', 'contact_id'],
['account.account_name', 'contact.id'], name="acs_fk")
)
>From this code, I'd get this exception:
InvalidRequestError: One or more mappers failed to initialize - can't
proceed with initialization of other mappers. Original exception was:
Could not determine join condition between parent/child tables on
relationship AccountSyncedContact.groups. Specify a 'primaryjoin'
expression. If 'secondary' is present, 'secondaryjoin' is needed as
well.
At first, this confused me, because I thought that setting up the
ForeignKeyConstraint like I had would let SQLAlchemy know how I meant
to relate the three tables.
However, I didn't notice until some time later that there's a serious
bug in my ForeignKeyConstraint definition: the 'refcolumns' argument
isn't referring to columns within the same table. Rather than
['account.account_name', 'contact.id'], it should have been
['account_synced_contact.account_name',
'account_synced_contact.contact_id'].
Considering that the docs for refcolumns specifically state that "The
columns must all be located within the same Table," I believe that
SQLAlchemy should have thrown an exception for *that*, rather than for
it being unable to figure out the primaryjoin condition.
I figure that the reason it didn't throw an exception is because I
*have* defined an 'account' table with a pk called 'account_id', and a
'contact' table with an 'id' column. So SQLAlchemy may have found
those columns and figured it was OK.
--
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.
