代码之家 › 专栏 › 技术社区 › wRAR

在SQLAlchemy中处理mysql重启

pylons sqlalchemy error-handling mysql

wRAR · 技术社区 · 15 年前

我的Pylons应用程序通过SQLAlchemy和python MySQLdb使用本地MySQL服务器。当服务器重新启动时,开放池连接显然是关闭的,但应用程序不知道这一点,而且当它尝试使用这样的连接时,显然会收到“MySQL server has gone away”:

File '/usr/lib/pymodules/python2.6/sqlalchemy/engine/default.py', line 277 in do_execute
  cursor.execute(statement, parameters)
File '/usr/lib/pymodules/python2.6/MySQLdb/cursors.py', line 166 in execute
  self.errorhandler(self, exc, value)
File '/usr/lib/pymodules/python2.6/MySQLdb/connections.py', line 35 in defaulterrorhandler
  raise errorclass, errorvalue
OperationalError: (OperationalError) (2006, 'MySQL server has gone away')

此异常不会在任何地方被捕获,因此它会出现在用户面前。如果我应该在代码中的某个地方处理这个异常,请在Pylons WSGI应用程序中显示这样的代码的位置。或者SA本身有解决方案?

2 回复 | 直到 15 年前

zifot 15 年前

测试溶液见底部编辑

PoolListener 还有路要走吗?

你可以这样做:

class MyListener(sqlalchemy.interfaces.PoolListener):
    def __init__(self):
       self.retried = False
    def checkout(self, dbapi_con, con_record, con_proxy):
       try:
           dbapi_con.info() # is there any better way to simply check if connection to mysql is alive?
       except sqlalchemy.exc.OperationalError:
           if self.retried:
               self.retried = False
               raise # we do nothing
           self.retried = True
           raise sqlalchemy.exc.DisconnectionError

# next, code according to documentation linked above follows

e = create_engine("url://", listeners=[MyListener()])

附言:我没有测试这是否有效。

编辑:对于挂架,上面显示的引擎初始化修改需要在 ~~您的\u app.model.init \u模型(挂架0.9.7)或~~ 您的\u app.config.environment.load \u环境 ~~这些是~~ 这就是

编辑

import sqlalchemy
import sqlalchemy.interfaces
import _mysql_exceptions

class MyListener(sqlalchemy.interfaces.PoolListener):
    def __init__(self):
       self.retried = False
    def checkout(self, dbapi_con, con_record, con_proxy):
       try:
           dbapi_con.cursor().execute('select now()')
       except _mysql_exceptions.OperationalError:
           if self.retried:
               self.retried = False
               raise
           self.retried = True
           raise sqlalchemy.exc.DisconnectionError

config['sqlalchemy.listeners'] = [MyListener()]

engine = engine_from_config(config, 'sqlalchemy.')
init_model(engine)

这次我能够测试它(在Pylons 1.0+SQLAlchemy 0.6.1上)和 它起作用了。 :)

estin 15 年前

from sqlalchemy.interfaces import ConnectionProxy
class MyProxy(ConnectionProxy):
    def cursor_execute(self, execute, cursor, statement, parameters, context, executemany):
        try:
            return execute(cursor, statement, parameters, context)
        except sqlalchemy.exc.OperationalError:
            # Handle this exception
            pass

engine = engine_from_config(config, 'sqlalchemy.', proxy=MyProxy())

或者为每个http查询编写异常处理中间件:

class MyMiddleware(object):
    def __init__(self, app):
        self.app = app

    def __call__(self, environ, start_response):
        try:
            return self.app(environ, start_response)
        except sqlalchemy.exc.OperationalError:
            start_response(
                '500 Internal Server Error',
                [('content-type', 'text/html')])
            return ['error page\n']

# CUSTOM MIDDLEWARE HERE (filtered by error handling middlewares)
app = MyMiddleware(app)