Relations on composite keys using sqlalchemy
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.