sqlalchemy中的多对多.如果标签已经存在，则防止SQLAlchemy插入表中 [英] Many-to-many in sqlalchemy. Preventing SQLAlchemy from inserting into a table if a tag already exists

问题描述

我有一些ORM映射表，它们(被缩减)如下:

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

通过此设置，我可以使用新标签创建任务.它创建标签 tags表中的行，然后创建与新任务的关联 在tasktags表中可以.

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

当我尝试创建带有已经存在于其中的标签的任务时，问题就来了 tags表.

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

似乎SQLAlchemy总是 尝试将标签插入标签表， 它是否已经存在.我真的很希望它仅创建 关联(如果标签已存在).这似乎是很正常的 在很多情况下，但我在文档中找不到任何地方 显示我可能做错了.

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?

对于背景，我使用带有psycopg2驱动程序的PostgreSQL 9.1 DB，并且 SQLAlchemy 0.7.9(Python 2.7.3)

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

我正在考虑的万不得已的方法:标签在技术上是主键， 没有别的，我只能用一个task_id-> tag表而没有标签 桌子.但我希望能够将元数据附加到标签本身上 必要时上路.

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.

推荐答案

对于仅唯一标签"配方，我通常使用唯一对象配方或它的某些变体:

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.

这自然需要针对特定​​行的SELECT才能知道它是否首先存在.现在，ORM不直接支持使用特定于数据库的命令基于数据库侧的确定来插入或更新行的"upsert"技术.无论如何，您使用的是Postgresql，除了使用通用表表达式的一个非常尴尬的系统之外，它实际上不支持任何本机的"upsert"功能.

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.

这篇关于sqlalchemy中的多对多.如果标签已经存在，则防止SQLAlchemy插入表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！