python - How can I prevent this SQLAlchemy one-to-many relationship from trying to insert duplicate data? -

i have following relationship in sqlalchemy:

class post(db.base):     __tablename__ = 'posts'     id = column(integer, primary_key=true)     url = column(string(512), unique=true)     timestamp = column(datetime)     comments = relationship("comment", backref=backref("post", cascade="save-update"))      @classmethod     def merge(cls, dst, src):         # self.url doesn't change (if does, consider new post)         dst.title = src.title         dst.author = src.author         dst.timestamp = src.timestamp         dst.comments = src.comments  class comment(db.base):     __tablename__ = 'comments'     id = column(integer, primary_key=true)     post_id = column(integer, foreignkey('posts.id'))     # switched using backref, obviates need following line:     # post = relationship("post", back_populates="comments")     text = column(unicodetext)     author = column(string(128))     timestamp = column(datetime)     score = column(integer)      def __init__(self, text, author, timestamp):         self.text = text         self.author = author         self.timestamp = timestamp 

i first create , insert posts (and associated comments), @ later time, come , re-pull posts , comments website. reason, when session.commit(), error when update comments, changing other fields fine.

my question is: how should updating these posts? i've been told sort of upsert functionality not ideal, nor built-in .merge() expect (i.e., tries recreate post).

here's i'm doing now:

# update existing posts if exist (look url), otherwise insert p in posts:     existing_post = db.session.query(post.post).filter(post.post.url==p.url).first()     if existing_post:         post.post.merge(existing_post, p)     else:         pass         db.session.add(p)  db.session.commit() 

if comment out dst.comments = src.comments class method .merge(dst,src), have no issue.

traceback (most recent call last):   file "/users/p/src/python-envs/app/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context context)   file "/users/p/src/python-envs/app/lib/python3.5/site-packages/sqlalchemy/engine/default.py", line 450, in do_execute cursor.execute(statement, parameters) psycopg2.integrityerror: duplicate key value violates unique constraint "posts_url_key" detail:  key (url)=(http://www.example/post) exists.  above exception direct cause of following exception:  traceback (most recent call last):   file "/applications/pycharm.app/contents/helpers/pydev/pydevd.py", line 2407, in <module>     globals = debugger.run(setup['file'], none, none, is_module)   file "/applications/pycharm.app/contents/helpers/pydev/pydevd.py", line 1798, in run     launch(file, globals, locals)  # execute script   file "/applications/pycharm.app/contents/helpers/pydev/_pydev_imps/_pydev_execfile.py", line 18, in execfile     exec(compile(contents+"\n", file, 'exec'), glob, loc)    file "/users/p/src/bss/slurper.py", line 75, in <module>     db.session.commit()   file "/users/p/src/python-envs/bss/lib/python3.5/site-packages/sqlalchemy/orm/session.py", line 813, in commit     self.transaction.commit()   file "/users/p/src/python-envs/bss/lib/python3.5/site-packages/sqlalchemy/orm/session.py", line 392, in commit     self._prepare_impl()   file "/users/p/src/python-envs/bss/lib/python3.5/site-packages/sqlalchemy/orm/session.py", line 372, in _prepare_impl     self.session.flush()   file "/users/p/src/python-envs/bss/lib/python3.5/site-packages/sqlalchemy/orm/session.py", line 2027, in flush     self._flush(objects)   file "/users/p/src/python-envs/bss/lib/python3.5/site-packages/sqlalchemy/orm/session.py", line 2145, in _flush     transaction.rollback(_capture_exception=true)   file "/users/p/src/python-envs/bss/lib/python3.5/sitepackages/sqlalchemy/util/langhelpers.py", line 60, in __exit__     compat.reraise(exc_type, exc_value, exc_tb)   file "/users/p/src/python-envs/bss/lib/python3.5/sitepackages/sqlalchemy/util/compat.py", line 183, in reraise     raise value   file "/users/p/src/python-envs/bss/lib/python3.5/site-packages/sqlalchemy/orm/session.py", line 2109, in _flush     flush_context.execute()   file "/users/p/src/python-envs/bss/lib/python3.5/site-packages/sqlalchemy/orm/unitofwork.py", line 373, in execute     rec.execute(self)   file "/users/p/src/python-envs/bss/lib/python3.5/site-packages/sqlalchemy/orm/unitofwork.py", line 532, in execute uow   file "/users/p/src/python-envs/bss/lib/python3.5/site-packages/sqlalchemy/orm/persistence.py", line 174, in save_obj mapper, table, insert)   file "/users/p/src/python-envs/bss/lib/python3.5/site-packages/sqlalchemy/orm/persistence.py", line 800, in _emit_insert_statements execute(statement, params)   file "/users/p/src/python-envs/bss/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 914, in execute return meth(self, multiparams, params)   file "/users/p/src/python-envs/bss/lib/python3.5/site-packages/sqlalchemy/sql/elements.py", line 323, in _execute_on_connection return connection._execute_clauseelement(self, multiparams, params)   file "/users/p/src/python-envs/bss/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1010, in _execute_clauseelement compiled_sql, distilled_params   file "/users/p/src/python-envs/bss/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1146, in _execute_context context)   file "/users/p/src/python-envs/bss/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1341, in _handle_dbapi_exception exc_info   file "/users/p/src/python-envs/bss/lib/python3.5/site-packages/sqlalchemy/util/compat.py", line 189, in raise_from_cause reraise(type(exception), exception, tb=exc_tb, cause=exc_value)   file "/users/p/src/python-envs/bss/lib/python3.5/site-packages/sqlalchemy/util/compat.py", line 182, in reraise raise value.with_traceback(tb)   file "/users/p/src/python-envs/bss/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context context)   file "/users/p/src/python-envs/bss/lib/python3.5/site-packages/sqlalchemy/engine/default.py", line 450, in do_execute       cursor.execute(statement, parameters)   sqlalchemy.exc.integrityerror: (psycopg2.integrityerror) duplicate key value violates unique constraint "posts_url_key"   detail:  key (url)=(http://www.example.com/post) exists.    [sql: 'insert posts (url, title, author, timestamp) values (%(url)s, %(title)s, %(author)s, %(timestamp)s) returning posts.id'] [parameters: {'timestamp': datetime.datetime(2016, 1, 13, 21, 0), 'title': ‘blog post title’, 'url': 'http://www.example.com/post', 'author': ‘authorname’}]    process finished exit code 1 

you should use hybrid_method decorator builtin sqlalchemy. works me.

class post(base):     __tablename__ = 'posts'     id = column(integer, primary_key=true)     url = column(string(512), unique=true)     timestamp = column(datetime)     comments = relationship("comment", backref=backref("post", cascade="save-update"))      @hybrid_method     def merge(self, dst):         # self.url doesn't change (if does, consider new post)         self.timestamp = dst.timestamp         self.comments = dst.comments  class comment(base):     __tablename__ = 'comments'     id = column(integer, primary_key=true)     post_id = column(integer, foreignkey('posts.id'))     text = column(unicodetext)     author = column(string(128))     timestamp = column(datetime)     score = column(integer)  c1 = comment() c2 = comment() c3 = comment(text=u"comment3")  p1 = post(url="foo") p1.comments = [c1, c2] session.add(p1) session.commit()  p1 = session.query(post).filter_by(url="foo").one() p2 = post() p2.comments = [c3] p1.merge(p2)  session.commit()  p1 = session.query(post).filter_by(url="foo").one() print p1.comments[0].text # comment3