python调用mysql数据库sql语句过长有问题吗_Python：MySQL：处理超时

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.

but this does not specifically answer my query.

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)

and so on

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

and then

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!!!

解决方案

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

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

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)

Back in business!

Python 2.7, MySQL 5.5.41