Why is SQLAlchemy/associationproxy duplicating my tags?

Question

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?

Answer 1

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.

Answer 2

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:

e.tags.append(u"bar")

Instead of

e.tags = ...

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.