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

i working on tagging system blog. here stripped-down version of code creates flask app object , relevant post , tag models.

from flask import flask flask_sqlalchemy import sqlalchemy 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 using association_proxy able use pass list of strings , have converted list of tag objects. notice string-to-tag conversion happens @ time tags property set on post object (for example, @ time post object instantiated).

after importing above module, following works in 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 unique constraint on tag.name fails:

sqlalchemy.exc.integrityerror: (sqlite3.integrityerror) unique constraint failed:   tag.name [sql: 'insert tag (name) values (?)'] [parameters: ('test',)] 

i understand why happens: in first case, tag name test in database when post2 created; in second, db.session.new contains 2 tag objects name have not been persisted @ commit time.

what don't know how fix it. thought of using before_flush sqlalchemy event consolidate tag objects in db.session.new unable make work. unsure if right strategy.

does stackoverflow collective wisdom have insights or recommendations?

your get_or_create needs add created tags session subsequent calls can find uncommitted tag instances in session , return 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