Many-to-many in sqlalchemy. Preventing SQLAlchemy from inserting into a table if a tag already exists

Question

I have a few ORM mapped tables, which (pared down) look like this:

class Tag(Base):
    __tablename__ = 'tags'

    tag_name = Column(String, primary_key=True)

task2tag_assoc = Table('tasktags', Base.metadata,
    Column('task_id', UUID, ForeignKey('tasks.task_id', ondelete='cascade'), 
           primary_key=True),
    Column('tag_name', String, ForeignKey('tags.tag_name', ondelete='cascade'),
           primary_key=True)
    )

class Task(Base):
    __tablename__ = 'tasks'

    task_id = Column(UUID, primary_key=True)
    _tags = relationship('Tag', secondary=task2tag_assoc, backref='tasks',
            collection_class=set)
    tags = association_proxy('_tags', 'tag_name')

    def __init__(self, task_id, tags):
        self.task_id = task_id
        self.tags = set([tags])

With this setup, I can create a task with new tags just fine. It creates the tag row in the tags table, and then creates the association to a new task just fine in the tasktags table.

t = Task(task_id = uuid4(), tags=['foo', 'bar']) #this works

The problem comes when I try to create a task with a tag that already exists in the tags table.

t2 = Task(task_oid = uuid4(), tags=['foo', 'baz']) #this will give an integrity error

It seems SQLAlchemy always tries to insert the tag into the tags table, whether or not it already exists. I'd really like it to only create the association if the tag already exists. This seems like it would be fairly normal in many-to-many situations, but I can't find anywhere in the documentation showing what I might be doing wrong.

Is there a way to get the behavior I want?

For background, I am using a postgresql 9.1 DB with the psycopg2 driver, and SQLAlchemy 0.7.9 (Python 2.7.3)

Things I am considering as a last resort: Tags are technically a primary key and nothing else, I could get away with just a task_id->tag table and no tags table. But I'd like to be able to attach metadata to the tags themselves down the road if it becomes necessary.

Answer 1

for the "unique tags only" recipe I usually use the unique object recipe, or some variant of it: http://www.sqlalchemy.org/trac/wiki/UsageRecipes/UniqueObject.

This naturally requires a SELECT against a particular row in order to tell if it exists first. The "upsert" technique, using database-specific commands to INSERT or UPDATE a row based on a database-side determination, is not directly supported by the ORM right now. You're on Postgresql anyway which doesn't actually support any native "upsert" feature except one very awkward system using common table expressions.