今天干活的时候把代码里的数据库连接全部改成了长连接,然后就出现了奇怪的问题。

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

应该是数据库连接的空闲时间过长,导致了连接丢失。没办法只能自己封装一遍MySQLdb,在每次执行前ping一下,并且重新连接。

#!/usr/bin/env python2
# coding: utf-8
#file: ConnectDB.py

import socket
import MySQLdb

__author__ = "lightless"
__email__ = "root@lightless.me"


class DB(object):
    """
    对MySQLdb重新封装一遍
    query方法检测超时问题
    Fuck MySQLdb!!!
    """
    def __init__(self, host, port, database, username, password, charset="utf8", autocommit=True):
        super(DB, self).__init__()
        self.db = None
        self.cursor = None
        self.result = None
        self.execute_result = None

        self.host = host
        self.database = database
        self.port = port
        self.username = username
        self.password = password
        self.charset = charset
        self.autocommit = autocommit

    def __del__(self):
        self.db.close()
        del self.db
        del self.cursor

    def __connect(self):
        try:
            self.db = MySQLdb.connect(
                host=self.host, port=int(self.port), user=self.username, passwd=self.password,
                db=self.database, charset=self.charset, autocommit=self.autocommit
            )
            self.cursor = self.db.cursor()
        except MySQLdb.MySQLError as e:
            print "Error Message:"
            print e

    def connect(self):
        self.__connect()

    def __query(self, sql, commit=False):
        self.db.ping(True)
        self.execute_result = self.cursor.execute(sql)
        if commit:
            self.db.commit()
        self.result = self.cursor.fetchall()
        return self.result

    def query(self, sql, commit=False):
        try:
            if self.cursor is None:
                self.__connect()
            return self.__query(sql, commit)
        except MySQLdb.OperationalError:
            # Mysql Connection Lost
            self.__connect()
            try:
                return self.__query(sql, commit)
            except MySQLdb.MySQLError as e:
                print "Error Message:"
                print e

这样问题就解决了。