Reputation: 2834
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.
Upvotes: 10
Views: 2887
Reputation: 3628
Try to delete assoc links like this t2._tags = []
t2 = Task(task_oid = uuid4())
t2._tags = []
tags = ['foo', 'baz']
for tag in tags:
t2._tags.append(Tag(tag))
Upvotes: 0
Reputation: 75317
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.
Upvotes: 4