For lack of a better example, suppose you’re storing data about authors and articles and you have a many-to-many relationship between authors and articles. To keep things interesting, you’ve decided to use a composite primary key for your “author” table.
Since I couldn’t find an example of doing this online, here’s how I got it all to work:
from sqlalchemy import (create_engine, Column, String, ForeignKey, ForeignKeyConstraint) from sqlalchemy.orm import sessionmaker, relationship from sqlalchemy.ext.declarative import declarative_base engine = create_engine('sqlite:///:memory:', echo=True) Base = declarative_base() Session = sessionmaker(bind=engine) class Author(Base): __tablename__ = 'author' first_name = Column(String, primary_key=True) last_name = Column(String, primary_key=True) articles = relationship( 'Article', secondary='authorarticle', back_populates='authors', ) class Article(Base): __tablename__ = 'article' slug = Column(String, primary_key=True) authors = relationship( 'Author', secondary='authorarticle', back_populates='articles', ) class AuthorArticle(Base): __tablename__ = 'authorarticle' article_slug = Column(String, ForeignKey(Article.slug), primary_key=True) author_first = Column(String, primary_key=True) author_last = Column(String, primary_key=True) __table_args__ = ( ForeignKeyConstraint( ['author_first', 'author_last'], ['author.first_name', 'author.last_name'], ), ) if __name__ == '__main__': Base.metadata.create_all(engine) session = Session() author = Author( first_name='Name', last_name='Surname' ) article = Article(slug='some-article') author.articles.append(article) session.add(author) session.add(article) session.commit()
It’s crucial you use
ForeignKeyConstraint so that SQLAlchemy knows it
should use a composite foreign key in your association table. If you define the
author’s first and last name columns as separate foreign keys, you’ll see an
sqlalchemy.exc.AmbiguousForeignKeysError: Could not determine join condition between parent/child tables on relationship Author.articles - there are multiple foreign key paths linking the tables via secondary table ‘authorarticle’. Specify the ‘foreign_keys’ argument, providing a list of those columns which should be counted as containing a foreign key reference from the secondary table to each of the parent and child tables.
Now if you read that and see “Specify the ‘foreign_keys’ argument”, you might give that a try, but it’ll never work until you’ve defined the composite foreign key properly.
However, suppose you’re like me and you do try define the ‘foreign_keys’ argument, but then figure out the composite foreign key thing; you might have ended up with a misconfigured ‘foreign_keys’ that looks like this:
# in the Article class # XXX: this is the wrong way of doing it articles = relationship(..., foreign_keys=( '[AuthorArticle.author_first, ' ' AuthorArticle.author_last]' ), )
This’ll get you the following error:
sqlalchemy.exc.NoForeignKeysError: Could not determine join condition between parent/child tables on relationship Author.articles - there are no foreign keys linking these tables via secondary table ‘authorarticle’. Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or specify ‘primaryjoin’ and ‘secondaryjoin’ expressions.
If you do decide to define the foreign keys on the relationship, you’ll have to provide all the foreign keys, like the following:
# the right way articles = relationship(..., foreign_keys=( '[AuthorArticle.article_slug, ' # crucial ' AuthorArticle.author_first, ' ' AuthorArticle.author_last]' ), )
You’ll need to do that for the
relationship() call in both