为什么 SQLAlchemy/associationproxy 重复我的标签？
我正在尝试在与 非常相似的情况下对标签使用关联代理文档中的示例。这是我的架构的一个子集（它是一个博客），使用声明式：
class Tag(Base):
__tablename__ = 'tags'
id = Column(Integer, primary_key=True)
tag = Column(Unicode(255), unique=True, nullable=False)
class EntryTag(Base):
__tablename__ = 'entrytags'
entry_id = Column(Integer, ForeignKey('entries.id'), key='entry', primary_key=True)
tag_id = Column(Integer, ForeignKey('tags.id'), key='tag', primary_key=True)
class Entry(Base):
__tablename__ = 'entries'
id = Column(Integer, primary_key=True)
subject = Column(Unicode(255), nullable=False)
# some other fields here
_tags = relation('Tag', backref='entries', secondary=EntryTag.__table__)
tags = association_proxy('_tags','tag')
这是我尝试使用它的方式：
>>> e = db.query(Entry).first()
>>> e.tags
[u'foo']
>>> e.tags = [u'foo', u'bar'] # really this is from a comma-separated input
db.commit()
Traceback (most recent call last):
[...]
sqlalchemy.exc.IntegrityError: (IntegrityError) duplicate key value violates unique constraint "tags_tag_key"
'INSERT INTO tags (id, tag) VALUES (%(id)s, %(tag)s)' {'tag': 'bar', 'id': 11L}
>>> map(lambda t:(t.id,t.tag), db.query(Tag).all())
[(1, u'foo'), (2, u'bar'), (3, u'baz')]
标签 u'bar' 已经存在，id 为 2；为什么 SQLAlchemy 不只是附加那个而不是尝试创建它？我的模式是不是有问题？
I'm trying to use association proxy for tags, in a very similar scenario to the example in the docs. Here is a subset of my schema (it's a blog), using declarative:
class Tag(Base):
__tablename__ = 'tags'
id = Column(Integer, primary_key=True)
tag = Column(Unicode(255), unique=True, nullable=False)
class EntryTag(Base):
__tablename__ = 'entrytags'
entry_id = Column(Integer, ForeignKey('entries.id'), key='entry', primary_key=True)
tag_id = Column(Integer, ForeignKey('tags.id'), key='tag', primary_key=True)
class Entry(Base):
__tablename__ = 'entries'
id = Column(Integer, primary_key=True)
subject = Column(Unicode(255), nullable=False)
# some other fields here
_tags = relation('Tag', backref='entries', secondary=EntryTag.__table__)
tags = association_proxy('_tags','tag')
Here's how I'm trying to use it:
>>> e = db.query(Entry).first()
>>> e.tags
[u'foo']
>>> e.tags = [u'foo', u'bar'] # really this is from a comma-separated input
db.commit()
Traceback (most recent call last):
[...]
sqlalchemy.exc.IntegrityError: (IntegrityError) duplicate key value violates unique constraint "tags_tag_key"
'INSERT INTO tags (id, tag) VALUES (%(id)s, %(tag)s)' {'tag': 'bar', 'id': 11L}
>>> map(lambda t:(t.id,t.tag), db.query(Tag).all())
[(1, u'foo'), (2, u'bar'), (3, u'baz')]
The tag u'bar' already existed with id 2; why didn't SQLAlchemy just attach that one instead of trying to create it? Is my schema wrong somehow?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
免责声明：自从我使用 SQLAlchemy 以来已经有很长时间了，所以这更多的是一个猜测。
看起来您期望 SQLAlchemy 在对多对多表执行插入时神奇地获取字符串“bar”并查找它的相关标签。我预计这是无效的，因为相关字段（“标签”）不是主键。
想象一下类似的情况，您的 Tag 表实际上是 Comment，也带有 id 和文本字段。您希望能够使用上面使用的相同 e.comments = ['u'Foo', 'u'Bar'] 语法向条目添加注释，但您希望它只执行插入，不检查是否有相同内容的现有注释。
所以这可能就是它在这里所做的，但它会遇到标签名称的唯一性约束并失败，假设您试图做错误的事情。
如何修复它？将 tag.tag 作为主键可以说是正确的做法，尽管我不知道这样做的效率如何，也不知道 SQLAlchemy 处理它的效果如何。如果失败，请尝试按名称查询 Tag 对象，然后再将它们分配给条目。您可能需要编写一个小实用函数，它接受一个 unicode 字符串，然后返回现有标签或为您创建一个新标签。
Disclaimer: it's been ages since I used SQLAlchemy so this is more of a guess than anything.
It looks like you're expecting SQLAlchemy to magically take the string 'bar' and look up the relevant Tag for it when performing the insert on the many-to-many table. I expect this is invalid, because the field in question ('tag') is not a primary key.
Imagine a similar situation where your Tag table is actually Comment, also with an id and a text field. You'd expect to be able to add Comments to an Entry with the same e.comments = ['u'Foo', 'u'Bar'] syntax that you've used above, but you'd want it to just perform INSERTs, not check for existing comments with the same content.
So that is probably what it's doing here, but it hits the uniqueness constraint on your tag name and fails, assuming that you're attempting to do the wrong thing.
How to fix it? Making tags.tag the primary key is arguably the correct thing to do, although I don't know how efficient that is nor how well SQLAlchemy handles it. Failing that, try querying for Tag objects by name before assigning them to the entry. You may have to write a little utility function that takes a unicode string and either returns an existing Tag or creates a new one for you.
我从未使用过 SQLAlchemy 0.5（我上一个使用它的应用程序是基于 0.4 的），但我可以在您的代码中看到一个怪癖：您应该修改 Association_proxy 对象，而不是重新分配它。
尝试做类似的事情：
而不是
如果这不起作用，请尝试粘贴这些表的完整工作示例（请包括导入！），我会给您更多建议。
I've never used SQLAlchemy 0.5 yet (my last app using it was 0.4 based) but I can see one quirk in your code: you should modify the association_proxy object, not reassign it.
Try doing something like:
Instead of
If that doesn't work, try pasting a complete working example for those tables (including the imports, please!) and I'll give you some more advice.