Inserting data in Many to Many relationship in SQLAlchemy

Question

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

Is it possible to write something like:

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

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.

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

Is this possible?

Thanks, Boda Cydo.

Answer 1

Fist of all you could simplify your many-to-many relation by using association_proxy.

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