python - Insert or update duplicate key in SQLAlchemy (IntegrityError) -

python - Insert or update duplicate key in SQLAlchemy (IntegrityError) -

i trying parse rss feeds , write urls database using flask-sqlalchemy.

some of these feeds overlap (i.e. same article appears in multiple feeds or multiple times in same feed), i've defined primary key (non-random) hash of url.

my problem when looping through urls , adding them session, nail exception when seek commit them database.

here's code:

for entry in feed.entries: # create database row article = article(entry) # if row exists in database if db.session.query(article).filter_by(uuid=article.uuid).first(): print "duplicate" else: db.session.merge(article) db.session.commit()

when article exists in database, ignored. however, if exists in session hasn't been committed database, sqlalchemy tries write multiple times in same transaction, , sqlalchemy.exc.integrityerror: (integrityerror) column hash not unique.

my intuition need check object hash doesn't exist in session (as querying database), , thought that's merge do, still error.

you should utilize unique object pattern examples.

this creates in memory cache session, , guarantees 1 unique result. if instance in cache, utilize it, otherwise seek database. if it's not in database, create it. add together instance cache.

here's take on pattern, simplifies linked example.

class uniquemixin(object): @classmethod def get_unique(cls, **kwargs): session = current_app.extensions['sqlalchemy'].db.session session._unique_cache = cache = getattr(session, '_unique_cache', {}) key = (cls, tuple(kwargs.items())) o = cache.get(key) if o none: o = session.query(cls).filter_by(**kwargs).first() if o none: o = cls(**kwargs) session.add(o) cache[key] = o homecoming o class mymodel(uniquemixin, db.model): # ... name = db.column(db.string, nullable=false) # ... m1 = mymodel.get_unique(name='test') # new instance m2 = mymodel.get_unique(name='test') # cache assert m1 m2 db.session.commit() # inserts 1 row m1 = mymodel.get_unique(name='test') # database m2 = mymodel.get_unique(name='test') # cache assert m1 m2

inherit uniquemixin in model might encounter situation, , utilize get_unique instead of normal constructor.

python flask sqlalchemy