在 SQLAlchemy 中以多对多关系插入数据 [英] Inserting data in Many to Many relationship in SQLAlchemy

问题描述

假设我在 SQLALchemy 中有 3 个类:Topic、Tag、Tag_To_Topic.

Suppose I have 3 classes in SQLALchemy: Topic, Tag, Tag_To_Topic.

是否可以写成这样:

new_topic = Topic("new topic")
Topics.tags = ['tag1', 'tag2', 'tag3']

我想在 Tag 表中自动插入 'tag1'、'tag2' 和 'tag3'，并在 Tag_To_Topic<中插入 new_topic 和这 3 个标签之间的正确关系/code> 表格.

Which I would like to automatically insert 'tag1', 'tag2' and 'tag3' in Tag table, and also insert the correct relationship between new_topic and these 3 tags in Tag_To_Topic table.

到目前为止，由于多对多关系，我还没有弄清楚如何做到这一点.(如果是一对多，那就很简单了，SQLAlchemy 已经默认做了.但这是多对多.)

So far I haven't been able to figure out how to do this because of many-to-many relationship. (If it was a one-to-many, it would be very easy, SQLAlchemy would does it by default already. But this is many-to-many.)

这可能吗?

谢谢，博达·西多.

推荐答案

使用 association_proxy.

然后，为了不干扰 SA 的工作，我会保持关系不变:

Then, I would leave the relation as it is in order not to interfere with what SA does:

# here *tag_to_topic* is the relation Table object
Topic.tags = relation('Tag', secondary=tag_to_topic)

而且我建议您只创建一个简单的包装器属性来完成将字符串列表转换为关系对象的工作(您可能会重命名关系).您的标签类将类似于:

And I suggest that you just create a simple wrapper property that does the job of translating the string list to the relation objects (you probably will rename the relation). Your Tags class would look similar to:

class Topic(Base):
    __tablename__ = 'topic'
    id = Column(Integer, primary_key=True)
    # ... other properties

    def _find_or_create_tag(self, tag):
        q = Tag.query.filter_by(name=tag)
        t = q.first()
        if not(t):
            t = Tag(tag)
        return t

    def _get_tags(self):
        return [x.name for x in self.tags]

    def _set_tags(self, value):
        # clear the list first
        while self.tags:
            del self.tags[0]
        # add new tags
        for tag in value:
            self.tags.append(self._find_or_create_tag(tag))

    str_tags = property(_get_tags,
                        _set_tags,
                        "Property str_tags is a simple wrapper for tags relation")

那么这段代码应该可以工作:

Then this code should work:

# Test
o = Topic()
session.add(o)
session.commit()
o.str_tags = ['tag1']
o.str_tags = ['tag1', 'tag4']
session.commit()

这篇关于在 SQLAlchemy 中以多对多关系插入数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！