使用sqlalchemy的组合键上的关系 [英] Relations on composite keys using sqlalchemy

问题描述

我有一个简单的Author-Books模型，无法找到一种方法来使firstName和lastName成为复合键并在关联中使用它.有什么想法吗?

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'))            

推荐答案

问题是您已将每个相关列分别定义为外键，当这不是您真正想要的时，您当然想要一个复合外键. Sqlalchemy通过说(以一种不太清楚的方式)对此做出响应，即它无法猜测要使用哪个外键(firstName或lastName).

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

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

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.

这篇关于使用sqlalchemy的组合键上的关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！