使用sqlalchemy在组合键上建立关系

Question

我有这个简单的Author - Books模型，无法找到一种方法使firstName和lastName成为组合键并在关系中使用它。什么好主意吗?

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

问题是您已经分别将每个依赖列定义为外键，当这不是您真正想要的时候，您当然希望使用复合外键。Sqlalchemy对此的回应是(以一种不太清楚的方式)，它无法猜测要使用哪个外键(firstName或lastName)。

声明复合外键的解决方案在声明性中有点笨拙，但仍然相当明显:

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]),
                      {})

这里重要的是，ForeignKey定义从各个列中删除，ForeignKeyConstraint添加到__table_args__类变量中。这样，Author.books上定义的relationship就可以正常工作了。