Python SQLAlchemy-"MySQL服务器已消失" [英] Python SQLAlchemy - "MySQL server has gone away"
问题描述
让我们看看下一个片段-
Lets have a look at the next snippet -
@event.listens_for(Pool, "checkout")
def check_connection(dbapi_con, con_record, con_proxy):
cursor = dbapi_con.cursor()
try:
cursor.execute("SELECT 1") # could also be dbapi_con.ping(),
# not sure what is better
except exc.OperationalError, ex:
if ex.args[0] in (2006, # MySQL server has gone away
2013, # Lost connection to MySQL server during query
2055): # Lost connection to MySQL server at '%s', system error: %d
# caught by pool, which will retry with a new connection
raise exc.DisconnectionError()
else:
raise
engine = create_engine('mysql://user:puss123@10.0.51.5/dbname', pool_recycle = 3600,pool_size=10, listeners=[check_connection])
session_factory = sessionmaker(bind = engine, autoflush=True, autocommit=False)
db_session = session_factory()
...
some code that may take several hours to run
...
db_session.execute('SELECT * FROM ' + P_TABLE + " WHERE id = '%s'" % id)
我认为在checkout事件下注册checkout_connection函数可以解决该问题,但并没有解决 现在的问题是我该如何告诉SQLAlchemy处理连接丢失,因此每次我调用execute()时,它都会检查连接是否可用,如果不可用,它将再次启动它?
I thought that registering the checkout_connection function under the checkout event would solve it but it didnt now the question is how am i suppose to tell SQLAlchemy handle connection dropouts so every time i call execute() it will check if connection is available and if not it will initiate it once again?
---- UPDATE ----
SQLAlchemy的版本为0.7.4
The version of SQLAlchemy is 0.7.4
---- UPDATE ----
def checkout_listener(dbapi_con, con_record, con_proxy):
try:
try:
dbapi_con.ping(False)
except TypeError:
dbapi_con.ping()
except dbapi_con.OperationalError as exc:
if exc.args[0] in (2006, 2013, 2014, 2045, 2055):
raise DisconnectionError()
else:
raise
engine = create_engine(CONNECTION_URI, pool_recycle = 3600,pool_size=10)
event.listen(engine, 'checkout', checkout_listener)
session_factory = sessionmaker(bind = engine, autoflush=True, autocommit=False)
db_session = session_factory()
session_factory发送到每个新创建的线程
session_factory is sent to every newly created thread
class IncidentProcessor(threading.Thread):
def __init__(self, queue, session_factory):
if not isinstance(queue, Queue.Queue):
raise TypeError, "first argument should be of %s" (type(Queue.Queue))
self.queue = queue
self.db_session = scoped_session(session_factory)
threading.Thread.__init__(self)
def run(self):
self.db_session().execute('SELECT * FROM ...')
...
some code that takes alot of time
...
self.db_session().execute('SELECT * FROM ...')
现在,当执行在很长一段时间后运行时,我收到"MySQL服务器已消失"错误
now when execute runs after a big period of time i get the "MySQL server has gone away" error
推荐答案
对此进行了讨论,该文档很好地描述了该问题,因此我使用了他们推荐的方法来处理此类错误:
There was a talk about this, and this doc describes the problem pretty nicely, so I used their recommended approach to handle such errors: http://discorporate.us/jek/talks/SQLAlchemy-EuroPython2010.pdf
它看起来像这样:
from sqlalchemy import create_engine, event
from sqlalchemy.exc import DisconnectionError
def checkout_listener(dbapi_con, con_record, con_proxy):
try:
try:
dbapi_con.ping(False)
except TypeError:
dbapi_con.ping()
except dbapi_con.OperationalError as exc:
if exc.args[0] in (2006, 2013, 2014, 2045, 2055):
raise DisconnectionError()
else:
raise
db_engine = create_engine(DATABASE_CONNECTION_INFO,
pool_size=100,
pool_recycle=3600)
event.listen(db_engine, 'checkout', checkout_listener)
这篇关于Python SQLAlchemy-"MySQL服务器已消失"的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!