假设我们在多对多关系中有两个表,如下所示:
class User(db.Model): __tablename__ = 'user' uid = db.Column(db.String(80), primary_key=True) languages = db.relationship('Language', lazy='dynamic', secondary='user_language') class UserLanguage(db.Model): __tablename__ = 'user_language' __tableargs__ = (db.UniqueConstraint('uid', 'lid', name='user_language_ff'),) id = db.Column(db.Integer, primary_key=True) uid = db.Column(db.String(80), db.ForeignKey('user.uid')) lid = db.Column(db.String(80), db.ForeignKey('language.lid')) class Language(db.Model): lid = db.Column(db.String(80), primary_key=True) language_name = db.Column(db.String(30))
现在在python shell中:
In [4]: user = User.query.all()[0] In [11]: user.languages = [Language('1', 'English')] In [12]: db.session.commit() In [13]: user2 = User.query.all()[1] In [14]: user2.languages = [Language('1', 'English')] In [15]: db.session.commit() IntegrityError: (IntegrityError) column lid is not unique u'INSERT INTO language (lid, language_name) VALUES (?, ?)' ('1', 'English')
我怎样才能让关系知道它应该忽略重复而不破坏Language表的唯一约束?当然,我可以单独插入每种语言并检查表中是否已经预先存在该条目,但是sqlalchemy关系提供的大部分好处都消失了.
我建议阅读Association Proxy:简化关联对象.在这种情况下,您的代码将转换为如下所示:
# NEW: need this function to auto-generate the PK for newly created Language # here using uuid, but could be any generator def _newid(): import uuid return str(uuid.uuid4()) def _language_find_or_create(language_name): language = Language.query.filter_by(language_name=language_name).first() return language or Language(language_name=language_name) class User(Base): __tablename__ = 'user' uid = Column(String(80), primary_key=True) languages = relationship('Language', lazy='dynamic', secondary='user_language') # proxy the 'language_name' attribute from the 'languages' relationship langs = association_proxy('languages', 'language_name', creator=_language_find_or_create, ) class UserLanguage(Base): __tablename__ = 'user_language' __tableargs__ = (UniqueConstraint('uid', 'lid', name='user_language_ff'),) id = Column(Integer, primary_key=True) uid = Column(String(80), ForeignKey('user.uid')) lid = Column(String(80), ForeignKey('language.lid')) class Language(Base): __tablename__ = 'language' # NEW: added a *default* here; replace with your implementation lid = Column(String(80), primary_key=True, default=_newid) language_name = Column(String(30)) # test code user = User(uid="user-1") # NEW: add languages using association_proxy property user.langs.append("English") user.langs.append("Spanish") session.add(user) session.commit() user2 = User(uid="user-2") user2.langs.append("English") # this will not create a new Language row... user2.langs.append("German") session.add(user2) session.commit()
SQLAlchemy wiki有一组示例,其中一个示例是如何检查实例的唯一性.
但是这些例子有点令人费解.基本上,创建一个classmethod get_unique
作为备用构造函数,它首先检查会话缓存,然后尝试查询现有实例,最后创建一个新实例.然后打电话Language.get_unique(id, name)
而不是Language(id, name)
.
为了回应OP在另一个问题上的赏金,我写了一个更详细的答案.