我的目标是在Google App Engine上的Flask应用中使用SQLAlchemy的ORM,在Postgresql dv上(在Cloud SQL上)成功执行Select语句。
我能够成功部署该应用程序,并且它成功连接到数据库并发出select语句。但是,在应用程序运行约1个小时后,我开始从查询中得到以下错误。请注意,这些查询成功运行了1个小时之前,并且我能够通过pgAdmin或本地python代码在数据库上成功运行查询。
有两个错误,第一个是pg8000 / core.py中的1778行发生的KeyError。在git上,此行是https://github.com/tlocke/pg8000/blob/master/pg8000/core.py#L1783
这是一个错误,使用except块进行处理,但在该except块内会出现“残破点”错误。
File "/env/lib/python3.7/site-packages/pg8000/core.py", line 1778, in execute ps = cache['ps'][key] KeyError: ('SELECT voice_comments.id AS voice_comments_id, voice_comments.store_id AS voice_comments_store_id, voice_comments.comment_date AS voice_comments_comment_date, voice_comments.survey_item AS voice_comments_survey_item, voice_comments.comment_text AS voice_comments_comment_text, voice_comments.overall_satisfaction AS voice_comments_overall_satisfaction, voice_comments.visit_date AS voice_comments_visit_date \nFROM voice_comments \nWHERE voice_comments.store_id = %s AND voice_comments.comment_date >= %s AND voice_comments.comment_date <= %s AND voice_comments.overall_satisfaction = %s AND voice_comments.id NOT IN (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)', ((705, 0, <function Connection.__init__.<locals>.text_out at 0x3eb1a3fb7c20>), (1114, 1, <function timestamp_send_integer at 0x3eb1a4a6d320>), (1114, 1, <function timestamp_send_integer at 0x3eb1a4a6d320>), (21, 1, <built-in method pack of Struct object at 0x3eb1a4a41030>), (21, 1, <built-in method pack of Struct object at 0x3eb1a4a41030>), (21, 1, <built-in method pack of Struct object at 0x3eb1a4a41030>), (21, 1, <built-in method pack of Struct object at 0x3eb1a4a41030>), (21, 1, <built-in method pack of Struct object at 0x3eb1a4a41030>), (21, 1, <built-in method pack of Struct object at 0x3eb1a4a41030>), (21, 1, <built-in method pack of Struct object at 0x3eb1a4a41030>), (21, 1, <built-in method pack of Struct object at 0x3eb1a4a41030>), (21, 1, <built-in method pack of Struct object at 0x3eb1a4a41030>), (21, 1, <built-in method pack of Struct object at 0x3eb1a4a41030>), (21, 1, <built-in method pack of Struct object at 0x3eb1a4a41030>), (21, 1, <built-in method pack of Struct object at 0x3eb1a4a41030>), (21, 1, <built-in method pack of Struct object at 0x3eb1a4a41030>), (21, 1, <built-in method pack of Struct object at 0x3eb1a4a41030>), (21, 1, <built-in method pack of Struct object at 0x3eb1a4a41030>)))
在处理上述KeyError的except块中,引发了BrokenPipeError,这是这样的:
Traceback (most recent call last): File "/env/lib/python3.7/site-packages/flask/app.py", line 2292, in wsgi_app response = self.full_dispatch_request() File "/env/lib/python3.7/site-packages/flask/app.py", line 1815, in full_dispatch_request rv = self.handle_user_exception(e) File "/env/lib/python3.7/site-packages/flask/app.py", line 1718, in handle_user_exception reraise(exc_type, exc_value, tb) File "/env/lib/python3.7/site-packages/flask/_compat.py", line 35, in reraise raise value File "/env/lib/python3.7/site-packages/flask/app.py", line 1813, in full_dispatch_request rv = self.dispatch_request() File "/env/lib/python3.7/site-packages/flask/app.py", line 1799, in dispatch_request return self.view_functions[rule.endpoint](**req.view_args) File "/srv/routes/voice.py", line 26, in voice_comments result = get_voice_comments(store_id, start_date, end_date, ids_to_filter_out=used_comment_ids) File "/srv/routes/pgsql_api.py", line 18, in get_voice_comments voice_comments.id.notin_(ids_to_filter_out)).all() File "/env/lib/python3.7/site-packages/sqlalchemy/orm/query.py", line 3233, in all return list(self) File "/env/lib/python3.7/site-packages/sqlalchemy/orm/query.py", line 3389, in __iter__ return self._execute_and_instances(context) File "/env/lib/python3.7/site-packages/sqlalchemy/orm/query.py", line 3414, in _execute_and_instances result = conn.execute(querycontext.statement, self._params) File "/env/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 982, in execute return meth(self, multiparams, params) File "/env/lib/python3.7/site-packages/sqlalchemy/sql/elements.py", line 293, in _execute_on_connection return connection._execute_clauseelement(self, multiparams, params) File "/env/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1101, in _execute_clauseelement distilled_params, File "/env/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1250, in _execute_context e, statement, parameters, cursor, context File "/env/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1478, in _handle_dbapi_exception util.reraise(*exc_info) File "/env/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 153, in reraise raise value File "/env/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1246, in _execute_context cursor, statement, parameters, context File "/env/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 588, in do_execute cursor.execute(statement, parameters) File "/env/lib/python3.7/site-packages/pg8000/core.py", line 861, in execute self._c.execute(self, operation, args) File "/env/lib/python3.7/site-packages/pg8000/core.py", line 1830, in execute self._flush() File "/opt/python3.7/lib/python3.7/socket.py", line 607, in write return self._sock.send(b) BrokenPipeError: [Errno 32] Broken pipe
我在三个方面考虑这个问题:
1。
我最近从MySQL切换到PostgresSQL。使用MySQL,我没有遇到断管错误。或者至少在我不知道的情况下,它们在后台得到了很好的处理。我担心自己在做一些高水平的蠢事..使用SQLAlchemy创建引擎时,我应该设置不同的pool_recycle或connect_timeout值。我正在对所有参数使用默认值,但pool_timeout = 30,pool_recycle = 1800除外
由于这是在应用程序运行约一个小时后发生的,所以我想知道连接是否正在断开连接(通过pg db),但是SQLAlchemy仍在尝试使用它们吗?
2。
我应该完全关心KeyError吗?还是这是代码中的预期路径。我倾向于它是一个期望,因为它被除外块等捕获...
3。
我应该在查询级别处理BrokenPipeError吗?即类似:
retry = 0
while retry<3:
try:
session.query(....
break
except BrokenPipeError:
retry += 1
continue
最佳答案
执行查询时可能正在关闭管道。根据关于How to prevent BrokenPipeError的另一个stackoverflow线程,这是一个常见问题。
这里有一个简短的摘要:
BrokenPipeError如幻影所示是正常现象,因为阅读
进程(头)终止并关闭其管道末端,而
编写过程(python)仍然尝试编写。