Many-to-many relationship: get-or-create

Question

I am working on a tagging system for a blog. Here is a stripped-down version of the code that creates the Flask app object and the relevant Post and Tag models.

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.ext.associationproxy import association_proxy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///test.sqlite'
db = SQLAlchemy(app)

post_tags = db.Table('post_tags',
                     db.Column('post_id', db.Integer,
                               db.ForeignKey('posts.id'),
                               nullable=False),
                     db.Column('tag_id', db.Integer,
                               db.ForeignKey('tags.id'),
                               nullable=False),
                     db.PrimaryKeyConstraint('post_id', 'tag_id'))

class Tag(db.Model):
    __tablename__ = 'tags'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(30), nullable=False, unique=True)

    @classmethod
    def get_or_create(cls, name):
        return cls.query.filter_by(name=name).scalar() or cls(name=name)

class Post(db.Model):
    __tablename__ = 'posts'

    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(80), nullable=False)
    content = db.Column(db.Text, nullable=False)

    _tags = db.relationship('Tag', secondary=post_tags)
    tags = association_proxy('_tags', 'name', creator=Tag.get_or_create)

    def __init__(self, title, content, tags=None):
        self.title = title
        self.content = content
        self.tags = tags

I am using an association_proxy to be able to use pass a list of strings and have it converted to a list of Tag objects. Notice that the string-to-Tag conversion happens at the time the tags property is set on a Post object (for example, at the time a Post object is instantiated).

After importing everything from the above module, the following works in a Python console:

>>> app.app_context().push()
>>> db.create_all()
>>> post1 = Post('Test', 'A test post', tags=['Test', 'Foo'])
>>> db.session.add(post1)
>>> db.session.commit()
>>> post2 = Post('A second test', 'Another test post', tags=['Test'])
>>> db.session.add(post2)
>>> db.session.commit()

The following, however, fails:

>>> app.app_context().push()
>>> db.create_all()
>>> post1 = Post('Test', 'A test post', tags=['Test', 'Foo'])
>>> post2 = Post('A second test', 'Another test post', tags=['Test'])
>>> db.session.add(post1)
>>> db.session.add(post2)
>>> db.session.commit()

The last line complains that the UNIQUE constraint on Tag.name fails:

sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) UNIQUE constraint failed:
  tag.name [SQL: 'INSERT INTO tag (name) VALUES (?)'] [parameters: ('Test',)]

I understand why this happens: in the first case, a Tag with the name Test is already in the database when post2 is created; in the second, db.session.new contains two Tag objects with that name that have not been persisted at commit time.

What I don't know is how to fix it. I thought of using the before_flush SQLAlchemy event to consolidate the Tag objects in db.session.new but I was unable to make it work. I am unsure if that is even the right strategy.

Does the StackOverflow collective wisdom have any insights or recommendations?

Answer 1

Your get_or_create needs to add the created tags to the session so that subsequent calls to it can find the uncommitted tag instances in the session and return the same instance.

@classmethod
def get_or_create(cls, name):
    tag = cls.query.filter_by(name=name).scalar()
    if not tag:
        tag = cls(name=name)
        db.session.add(tag)
    return tag