Relations on composite keys using sqlalchemy

Question

I have this simple model of Author - Books and can't find a way to make firstName and lastName a composite key and use it in relation. Any ideas?

from sqlalchemy import create_engine, ForeignKey, Column, String, Integer from sqlalchemy.orm import relationship, sessionmaker from sqlalchemy.ext.declarative import declarative_base  Base = declarative_base() engine = create_engine('mssql://user:pass@library') engine.echo = True session = sessionmaker(engine)()  class Author(Base):     __tablename__ = 'authors'     firstName = Column(String(20), primary_key=True)     lastName = Column(String(20), primary_key=True)     books = relationship('Book', backref='author')  class Book(Base):     __tablename__ = 'books'     title = Column(String(20), primary_key=True)     author_firstName = Column(String(20), ForeignKey('authors.firstName'))     author_lastName = Column(String(20), ForeignKey('authors.lastName'))             

Answer 1

The problem is that you have defined each of the dependent columns as foreign keys separately, when that's not really what you intend, you of course want a composite foreign key. Sqlalchemy is responding to this by saying (in a not very clear way), that it cannot guess which foreign key to use (firstName or lastName).

The solution, declaring a composite foreign key, is a tad clunky in declarative, but still fairly obvious:

class Book(Base):     __tablename__ = 'books'     title = Column(String(20), primary_key=True)     author_firstName = Column(String(20))     author_lastName = Column(String(20))     __table_args__ = (ForeignKeyConstraint([author_firstName, author_lastName],                                            [Author.firstName, Author.lastName]),                       {}) 

The important thing here is that the ForeignKey definitions are gone from the individual columns, and a ForeignKeyConstraint is added to a __table_args__ class variable. With this, the relationship defined on Author.books works just right.