为什么SQLAlchemy/associationproxy复制我的标签? [英] Why is SQLAlchemy/associationproxy duplicating my tags?

问题描述

在与

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

标记u'bar'已经存在，其ID为2；为什么SQLAlchemy不只是附加那个而不是尝试创建它?我的模式有问题吗?

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?

推荐答案

免责声明:自从我使用SQLAlchemy以来已经有很多年了，所以这比其他任何东西都更容易猜测.

Disclaimer: it's been ages since I used SQLAlchemy so this is more of a guess than anything.

您似乎希望SQLAlchemy在多对多表上执行插入操作时神奇地采用字符串"bar"并为其查找相关的Tag.我认为这是无效的，因为所讨论的字段(标记")不是主键.

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.

想象一下类似的情况，您的Tag表实际上是Comment，也带有id和文本字段.您希望能够使用与上面使用的相同的e.comments = ['u'Foo'，'u'Bar']语法将注释添加到条目，但是您希望它仅执行插入，而不检查内容相同的现有注释.

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.

如何解决?将tag.tag作为主键可以说是正确的做法，尽管我不知道SQLAlchemy的效率和效率.如果失败，请尝试按名称查询Tag对象，然后再将其分配给条目.您可能需要编写一个小的实用程序函数，该函数需要一个unicode字符串，并返回一个现有的Tag或为您创建一个新的Tag.

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/associationproxy复制我的标签?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！