Python:MySQL:处理超时 [英] Python: MySQL: Handling timeouts

问题描述

我使用的是Python和mySQL，两次查询之间的间隔很长.结果，我收到"MySQL连接已消失"错误，即超过了wait_timeout.

I am using Python and mySQL, and there is a long lag between queries. As a result, I get an 'MySQL connection has gone away' error, that is wait_timeout is exceeded.

例如，已经对此进行了讨论.在 优雅地处理"MySQL已经消失"

This has been discussed e.g. in Gracefully handling "MySQL has gone away"

但这不能专门回答我的查询.

but this does not specifically answer my query.

所以我处理这个问题的方法- 我已经将我所有的sql execute语句包装为-

So my approach to handling this - I have wrapped all my sql execute statements in a method as -

  def __execute_sql(self,sql,cursor):
    try:
        cursor.execute(sql)

    except MySQLdb.OperationalError, e:            
        if e[0] == 2006:
            self.logger.do_logging('info','DB', "%s : Restarting db" %(e))
            self.start_database()

我在调用此查询的代码中有多个位置.事实是，我也有几个游标，所以方法调用看起来像-

I have several places in the code which calls this query. The thing is, I also have several cursors, so the method invocations look like-

self.__execute_sql(sql,self.cursor_a)
self.__execute_sql(sql,self.cursor_b)

以此类推

在数据库启动后，我需要一种方法来优雅地重新执行查询.我可以将调用包装在if语句中，然后重新执行以使其成为

I need a way to gracefully re-execute the query after the db has been started. I could wrap the calls in an if statement, and re-execute so it would be

def __execute_sql(self,sql,cursor):
    try:
        cursor.execute(sql)
        return 1
except MySQLdb.OperationalError, e:            
    if e[0] == 2006:
        self.logger.do_logging('info','DB', "%s : Restarting db" %(e))
        self.start_database()
        return 0

然后

if (self.__execute_sql(sql,self.cursor_a) == 0):
   self.__execute_sql(sql,self.cursor_a)

但是这很笨拙.有一个更好的方法吗? 谢谢!!!

But this is clunky. Is there a better way to do this? Thanks!!!

推荐答案

我尝试了Crasched的方法，这使我陷入了一个新的OperationalError:

I tried Crasched's approach, which got me to a new OperationalError:

OperationalError: (2013, 'Lost connection to MySQL server during query')

我的最终解决方案是首先尝试ping，如果引发了另一个OperationalError，则使用新的连接重新连接并重新创建游标，如下所示:

My final solution was to first try the ping, and if another OperationalError was raised, to reconnect and recreate the cursor with the new connection, like so:

try:
    self.connection.ping(True)
except MySQLdb.OperationalError:
    self.connection = MySQLdb.connect(
        self.db_host,
        self.db_user,
        self.db_passwd,
        self.db_dbase,
        self.db_port)
    # reconnect your cursor as you did in __init__ or wherever    
    self.cursor = self.connection(
        MySQLdb.cursors.DictCursor)

重新营业！

Python 2.7，MySQL 5.5.41

Python 2.7, MySQL 5.5.41

这篇关于Python:MySQL:处理超时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！