Geek Answers Handbook

Why does SQLAlchemy / associationproxy duplicate my tags?

I am trying to use an association proxy for tags in a very similar example script in the docs . Here is a subset of my schema (this is 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')

This is how I try 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 u'bar' tag already exists with id 2; why didn't SQLAlchemy just attach this one and not try to create it? Is my circuit incorrect?

+4
source share
2 answers

Disclaimer: These have been ages since I used SQLAlchemy, so this is more a hunch than anything.

It looks like you expect SQLAlchemy to magically take the string “bar” and look at the corresponding tag for it when pasting into a many-to-many table. I expect this to be wrong because the field ("tag") is not a primary key.

Imagine a similar situation where your tag table is actually a comment, as well as with an identifier and a text field. You expect that you can add comments to a post with the same syntax e.comments = ['u'Foo', 'u'Bar'] that you used above, but you want it to just execute INSERT, do not check existing comments with the same content.

So this is probably what it does here, but it hits the uniqueness constraint of your tag name and fails, suggesting that you are trying to do the wrong thing.

How to fix it? Creating a primary key tags.tag is probably the right thing, but I don’t know how efficient it is or how SQLAlchemy handles this. Otherwise, try querying for Tag objects by name before assigning their entries. You may need to write a small utility function that takes a Unicode string and either returns an existing tag or creates a new one for you.

+3
source

I haven't used SQLAlchemy 0.5 yet (my last application used it based on 0.4), but I see one quirk in your code: you should change the association_proxy object, not reassign it.

Try to do something like:

 e.tags.append(u"bar")

Instead

 e.tags = ...

If this does not work, try pasting a full working example for these tables (including import, please!), And I will give you some more tips.

0
source

More articles:


All Articles