使用 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屋!