Python和Sqlalchemy-连接模式->随机与远程服务器断开连接 [英] Python & Sqlalchemy - Connection pattern -> Disconnected from the remote server randomly
本文介绍了Python和Sqlalchemy-连接模式->随机与远程服务器断开连接的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我的后端API服务器使用的是python。我的后端使用SQLALCHEMY连接到DISTANT Postgres数据库（不使用flask-sqlalchemy，因为即使没有烧瓶上下文，我也需要该连接器（用于其他特定任务）
I'm using a python for my backend API server. My backend connect to a DISTANT Postgres database using SQLALCHEMY (without flask-sqlalchemy because i need that connector even without flask context (for others specific tasks)
我的麻烦是：有时，我的SQLConnector崩溃，说：
My trouble is : Sometime, my SQLConnector crash, saying :
(psycopg2.OperationalError) server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
这是随机发生的，我尝试修复它使用冲洗机制，但没有成功...
（据记录，它在Docker中）
This happends randomly. I tried to fix it using a flush mechanism, without success... (for the record, It's in a Docker)
我想要什么？定期获取一个python软件（sqlalchemy）从远程数据库（postgres）抓取数据，而不会随时间随机断开连接
What do I want ? Get a python software (sqlalchemy) grabbing data from a remote database (postgres) periodically, WITHOUT disconnecting randomly over the time
我该如何解决？ 尝试在出现错误时自动重新连接，并更改引擎连接，但操作失败或代码段不正确
What did I try to fix it trying auto reconnect on error, and change the engine connection without success or proper code snippet
我在哪里？我被困住了，当我想（为了更快的测试/调试过程）不喜欢那些烦人的东西时，我不知道如何重现该错误。在我的SQL连接器上断开连接
Where I am at ? I'm stuck, i can't figure out HOW to reproduce the bug WHEN I WANT (for faster test/debug process) not avid thoses annoying disconnect on my sql connector
(psycopg2.OperationalError) server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
[SQL: 'INSERT INTO scan_oee (uniqid, id_kanban, created_at, finished, debug) VALUES (%(uniqid)s, %(id_kanban)s, %(created_at)s, %(finished)s, %(debug)s)'] [parameters: {'uniqid': '2a1c80df-e676-456c-9e8e-a1a32a4b4171', 'id_kanban': '1804033-A02-289', 'created_at': '2018-11-07T09:38:00.125942', 'finished': False, 'debug': True}] (Background on this error at: http://sqlalche.me/e/e3q8)
=== BackListener Exception ===
Traceback (most recent call last):
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
context)
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 509, in do_execute
cursor.execute(statement, parameters)
psycopg2.OperationalError: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "backlistener.py", line 126, in <module>
raise e
File "backlistener.py", line 121, in <module>
backlistener.run()
File "backlistener.py", line 112, in run
self.route_data(n)
File "backlistener.py", line 69, in route_data
insert_oee_into_db(n, self.action_stop_oee)
File "/usr/src/app/taktTime.py", line 109, in insert_oee_into_db
db.add(new_scan)
File "/usr/src/app/db/base.py", line 55, in __exit__
self.session_factory.commit()
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 943, in commit
self.transaction.commit()
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 467, in commit
self._prepare_impl()
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 447, in _prepare_impl
self.session.flush()
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 2254, in flush
self._flush(objects)
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 2380, in _flush
transaction.rollback(_capture_exception=True)
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py", line 66, in __exit__
compat.reraise(exc_type, exc_value, exc_tb)
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 249, in reraise
raise value
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 2344, in _flush
flush_context.execute()
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/unitofwork.py", line 391, in execute
rec.execute(self)
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/unitofwork.py", line 556, in execute
uow
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/persistence.py", line 181, in save_obj
mapper, table, insert)
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/persistence.py", line 830, in _emit_insert_statements
execute(statement, multiparams)
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 948, in execute
return meth(self, multiparams, params)
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/sql/elements.py", line 269, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1060, in _execute_clauseelement
compiled_sql, distilled_params
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1200, in _execute_context
context)
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1413, in _handle_dbapi_exception
exc_info
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 265, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb, cause=cause)
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 248, in reraise
raise value.with_traceback(tb)
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
context)
File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 509, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
[SQL: 'INSERT INTO scan_oee (uniqid, id_kanban, created_at, finished, debug) VALUES (%(uniqid)s, %(id_kanban)s, %(created_at)s, %(finished)s, %(debug)s)'] [parameters: {'uniqid': '2a1c80df-e676-456c-9e8e-a1a32a4b4171', 'id_kanban': '1804033-A02-289', 'created_at': '2018-11-07T09:38:00.125942', 'finished': False, 'debug': True}] (Background on this error at: http://sqlalche.me/e/e3q8)
还有我正在使用的连接器代码段
And there is the Connector snippet I'm using
base.py
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import scoped_session, sessionmaker
from config.settings import SETTINGS
from psycopg2 import IntegrityError as PgIntegrityError
from sqlalchemy.exc import IntegrityError as SQLIntegrityError
from server.flaskutils import print_flush
engine = create_engine("postgresql+psycopg2://" + SETTINGS['POSTGRES_USER'] + ":" +
SETTINGS['POSTGRES_PASSWORD'] + '@' + SETTINGS['POSTGRES_HOST'] + ':' + SETTINGS['POSTGRES_PORT'] + '/' + SETTINGS['POSTGRES_DB'])
# use session_factory() to get a new Session
_SessionFactory = scoped_session(sessionmaker(bind=engine))
Base = declarative_base()
class Database():
def __init__(self, auto_commit=False, create_all=False):
self.auto_commit = auto_commit
self.session_factory = session_factory()
if create_all:
Base.metadata.create_all(engine)
def __enter__(self):
return self.session_factory
def __exit__(self, type, value, traceback):
if self.auto_commit:
self.session_factory.commit()
self.session_factory.close()
def session_factory():
# Base.metadata.create_all(engine)
return _SessionFactory()
backlistener.py ->我的文件（不使用烧瓶，这是一个守护进程）
来自数据库.base import数据库
backlistener.py -> My file (NOT using flask, it's a deamon) from db.base import Database
def insert_oee_into_db(xid, ended):
with Database(auto_commit=True) as db:
new_scan = ScanOEE(xid, ended)
db.add(new_scan)
models.py
class ScanOEE(Base):
__tablename__ = 'scan_oee'
uniqid = Column(String(36), primary_key=True)
id_kanban = Column(String(50), unique=False)
created_at = Column(DateTime, unique=False)
finished = Column(Boolean, unique=False)
debug = Column(Boolean, unique=False)
def __init__(self, name, finished):
self.uniqid = str(uuid4())
self.id_kanban = name
self.created_at = datetime.datetime.utcnow().isoformat()
self.finished = finished
self.debug = SETTINGS['DEBUG_MODE']
def __repr__(self):
return '<ScanOEE %r (%s) %s %s>' % (self.id_kanban, self.uniqid, str(self.created_at), str(finished))
任何片段或帮助向我展示出什么问题了，这是什么好的做法将是完美的
Any snippet or help to show me what's wrong, what's the good practice would be perfect
编辑1
docker logs --tail all kanban_postgres_1
LOG: database system was interrupted; last known up at 2018-10-19 10:06:37 UTC
LOG: database system was not properly shut down; automatic recovery in progress
LOG: invalid record length at 0/422B1B8: wanted 24, got 0
LOG: redo is not required
LOG: MultiXact member wraparound protections are now enabled
LOG: database system is ready to accept connections
LOG: autovacuum launcher started
LOG: received smart shutdown request
LOG: autovacuum launcher shutting down
LOG: shutting down
LOG: database system is shut down
LOG: database system was shut down at 2018-10-19 10:21:33 UTC
LOG: MultiXact member wraparound protections are now enabled
LOG: autovacuum launcher started
LOG: database system is ready to accept connections
LOG: could not receive data from client: Connection reset by peer
LOG: could not receive data from client: Connection reset by peer
LOG: could not receive data from client: Connection reset by peer
LOG: could not receive data from client: Connection reset by peer
LOG: could not receive data from client: Connection reset by peer
LOG: could not receive data from client: Connection reset by peer
LOG: could not receive data from client: Connection reset by peer
LOG: could not receive data from client: Connection reset by peer
LOG: could not receive data from client: Connection reset by peer
LOG: unexpected EOF on client connection with an open transaction
LOG: unexpected EOF on client connection with an open transaction
LOG: database system was interrupted; last known up at 2018-10-23 12:29:06 UTC
LOG: database system was not properly shut down; automatic recovery in progress
LOG: invalid record length at 0/4419C98: wanted 24, got 0
LOG: redo is not required
LOG: MultiXact member wraparound protections are now enabled
LOG: database system is ready to accept connections
LOG: autovacuum launcher started
LOG: database system was interrupted; last known up at 2018-10-23 12:53:43 UTC
LOG: database system was not properly shut down; automatic recovery in progress
LOG: redo starts at 0/4419D08
LOG: invalid record length at 0/4422358: wanted 24, got 0
LOG: redo done at 0/44213E0
LOG: MultiXact member wraparound protections are now enabled
LOG: database system is ready to accept connections
LOG: autovacuum launcher started
LOG: could not receive data from client: Connection reset by peer
LOG: could not receive data from client: Connection reset by peer
LOG: could not receive data from client: Connection reset by peer
LOG: could not receive data from client: Connection reset by peer
LOG: could not receive data from client: Connection reset by peer
LOG: incomplete startup packet
LOG: invalid length of startup packet
LOG: invalid length of startup packet
LOG: could not receive data from client: Connection reset by peer
LOG: could not receive data from client: Connection reset by peer
LOG: could not receive data from client: Connection reset by peer
LOG: could not receive data from client: Connection reset by peer
LOG: could not receive data from client: Connection reset by peer
LOG: could not receive data from client: Connection reset by peer
LOG: could not receive data from client: Connection reset by peer
LOG: could not receive data from client: Connection reset by peer
LOG: could not receive data from client: Connection reset by peer
LOG: incomplete startup packet
LOG: invalid length of startup packet
LOG: invalid length of startup packet
LOG: invalid length of startup packet
LOG: invalid length of startup packet
LOG: could not receive data from client: Connection reset by peer
LOG: could not receive data from client: Connection reset by peer
LOG: could not receive data from client: Connection reset by peer
LOG: could not receive data from client: Connection reset by peer
LOG: could not receive data from client: Connection reset by peer
LOG: could not receive data from client: Connection reset by peer
LOG: could not receive data from client: Connection reset by peer
LOG: could not receive data from client: Connection reset by peer
LOG: could not receive data from client: Connection reset by peer
LOG: could not receive data from client: Connection reset by peer
LOG: could not receive data from client: Connection reset by peer
LOG: could not send data to client: Broken pipe
STATEMENT: SELECT scan_sm.uniqid AS scan_sm_uniqid, scan_sm.id_kanban AS scan_sm_id_kanban, scan_sm.created_at AS scan_sm_created_at, scan_sm.pos_x AS scan_sm_pos_x, scan_sm.pos_y AS scan_sm_pos_y, scan_sm.pos_z AS scan_sm_pos_z, scan_sm.seconds_since_last_scan AS scan_sm_seconds_since_last_scan, scan_sm.proceed_at AS scan_sm_proceed_at, scan_sm.state AS scan_sm_state
FROM scan_sm
WHERE scan_sm.proceed_at IS NOT NULL AND scan_sm.state = 'SUPERMARKET_FAST' ORDER BY scan_sm.created_at DESC
FATAL: connection to client lost
STATEMENT: SELECT scan_sm.uniqid AS scan_sm_uniqid, scan_sm.id_kanban AS scan_sm_id_kanban, scan_sm.created_at AS scan_sm_created_at, scan_sm.pos_x AS scan_sm_pos_x, scan_sm.pos_y AS scan_sm_pos_y, scan_sm.pos_z AS scan_sm_pos_z, scan_sm.seconds_since_last_scan AS scan_sm_seconds_since_last_scan, scan_sm.proceed_at AS scan_sm_proceed_at, scan_sm.state AS scan_sm_state
FROM scan_sm
WHERE scan_sm.proceed_at IS NOT NULL AND scan_sm.state = 'SUPERMARKET_FAST' ORDER BY scan_sm.created_at DESC
LOG: could not receive data from client: Connection reset by peer
LOG: unexpected EOF on client connection with an open transaction
LOG: unexpected EOF on client connection with an open transaction
LOG: could not receive data from client: Connection reset by peer
LOG: unexpected EOF on client connection with an open transaction
LOG: unexpected EOF on client connection with an open transaction
LOG: unexpected EOF on client connection with an open transaction
WARNING: worker took too long to start; canceled
WARNING: autovacuum worker started without a worker entry
WARNING: worker took too long to start; canceled
WARNING: autovacuum worker started without a worker entry
LOG: could not receive data from client: Connection reset by peer
LOG: invalid length of startup packet
LOG: could not receive data from client: Connection reset by peer
LOG: could not receive data from client: Connection reset by peer
LOG: database system was interrupted; last known up at 2018-11-02 12:40:55 UTC
LOG: database system was not properly shut down; automatic recovery in progress
LOG: redo starts at 0/4D4FD10
LOG: invalid record length at 0/4D7A8F8: wanted 24, got 0
LOG: redo done at 0/4D7A840
LOG: last completed transaction was at log time 2018-11-02 12:44:43.914143+00
LOG: MultiXact member wraparound protections are now enabled
LOG: autovacuum launcher started
LOG: database system is ready to accept connections
LOG: database system was interrupted; last known up at 2018-11-02 13:16:17 UTC
FATAL: the database system is starting up
FATAL: the database system is starting up
FATAL: the database system is starting up
FATAL: the database system is starting up
FATAL: the database system is starting up
FATAL: the database system is starting up
LOG: database system was not properly shut down; automatic recovery in progress
LOG: invalid record length at 0/4D7A968: wanted 24, got 0
LOG: redo is not required
LOG: MultiXact member wraparound protections are now enabled
LOG: database system is ready to accept connections
LOG: autovacuum launcher started
LOG: could not receive data from client: Connection reset by peer
LOG: invalid length of startup packet
LOG: could not receive data from client: Connection reset by peer
LOG: could not receive data from client: Connection reset by peer
LOG: database system was interrupted; last known up at 2018-11-05 10:36:02 UTC
LOG: database system was not properly shut down; automatic recovery in progress
LOG: invalid record length at 0/4DF53E0: wanted 24, got 0
LOG: redo is not required
LOG: MultiXact member wraparound protections are now enabled
LOG: autovacuum launcher started
LOG: database system is ready to accept connections
LOG: could not receive data from client: Connection reset by peer
LOG: database system was interrupted; last known up at 2018-11-05 14:15:34 UTC
LOG: database system was not properly shut down; automatic recovery in progress
LOG: invalid record length at 0/4E1CAE0: wanted 24, got 0
LOG: redo is not required
LOG: MultiXact member wraparound protections are now enabled
LOG: database system is ready to accept connections
LOG: autovacuum launcher started
LOG: could not receive data from client: Connection reset by peer
LOG: could not receive data from client: Connection reset by peer
LOG: could not receive data from client: Connection reset by peer
LOG: could not receive data from client: Connection reset by peer
LOG: could not receive data from client: Connection reset by peer
LOG: could not receive data from client: Connection reset by peer
推荐答案
@skapin，我已经解决了问题。由于服务器连接sql需要一个参数pool_pre_ping。 如何修复 OperationalError：（psycopg2.OperationalError）服务器
@skapin, I had solve the problem.As the server connect sql need a paramteter pool_pre_ping. how to fix "OperationalError: (psycopg2.OperationalError) server closed the connection unexpectedly"
from flask_sqlalchemy import SQLAlchemy as _BaseSQLAlchemy
class SQLAlchemy(_BaseSQLAlchemy):
def apply_pool_defaults(self, app, options):
super(SQLAlchemy, self).apply_pool_defaults(self, app, options)
options["pool_pre_ping"] = True
db = SQLAlchemy()
这篇关于Python和Sqlalchemy-连接模式->随机与远程服务器断开连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
