I have the following relationship in SQLAlchemy:
我在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 it does, we consider it a 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 to using backref, which obviates the need for 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 and insert the posts (and associated comments), then at a later time, come back and re-pull the posts and comments from the website. For some reason, when I session.commit(), I get an error only when I update the comments, but changing the other fields is fine.
我首先创建并插入帖子（以及相关的评论），然后在以后回来并重新提取网站上的帖子和评论。出于某种原因，当我session.commit（）时，我只在更新注释时才会收到错误，但更改其他字段很好。
My question is: how should I be updating these posts? I've been told that some sort of UPSERT functionality is not ideal, nor does the built-in .merge() do what I expect (i.e., it tries to recreate the post).
我的问题是：我该如何更新这些帖子？我被告知某种UPSERT功能并不理想，内置的.merge（）也没有达到我的预期（即，它试图重新创建帖子）。
Here's what I'm doing now:
这就是我现在正在做的事情：
# update existing posts if they exist (look up by url), otherwise insert
for 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 I comment out the dst.comments = src.comments from the class method .merge(dst,src), then I have no issue.
如果我从类方法.merge（dst，src）中注释掉dst.comments = src.comments，那么我没有问题。
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) already exists.
The above exception was the direct cause of the 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 the 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) already exists.
[SQL: 'INSERT INTO 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 with exit code 1
1 个解决方案
#1
0
You should use the hybrid_method decorator builtin to SQLALchemy. This works for me.
您应该使用内置于SQLALchemy的hybrid_method装饰器。这对我有用。
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 it does, we consider it a 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
#1
0
You should use the hybrid_method decorator builtin to SQLALchemy. This works for me.
您应该使用内置于SQLALchemy的hybrid_method装饰器。这对我有用。
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 it does, we consider it a 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