自定义pymysql工具类

一、目标：建立一个pymysql工具类，方便后续使用的时候直接导入

对pymysql进行封装，提供一个接口，直接完成增删改查操作。

二、功能：增删改查，断开自动重连

pymysql 断开自动重连

问题：

后台服务在运行时发现一个问题，运行一段时间后，接口请求报错：

pymysql.err.InterfaceError: (0, '')

数据库操作对象实例未注销，但是持有的数据库连接已经过期，已经和数据库断开连接，导致后续数据库操作不能正常进行。比如在爬虫过程中向mysql插入数据，如果长时间没有数据插入，则可能报错：pymysql.err.InterfaceError: (0, '')。

解决方法：

1 pymysql 的ping 函数会校验连接的可用性,如果连接不可用将会产生异常

def ping(self, reconnect=True):
    """
    Check if the server is alive.

    :param reconnect: If the connection is closed, reconnect.
    :raise Error: If the connection is closed and reconnect=False.
    """
    if self._sock is None:
        if reconnect:
            self.connect()
            reconnect = False
        else:
            raise err.Error("Already closed")
    try:
        self._execute_command(COMMAND.COM_PING, "")
        self._read_ok_packet()
    except Exception:
        if reconnect:
            self.connect()
            self.ping(False)
        else:
            raise

2 利用这一特性，构造一个连接丢失的循环，不断尝试连接数据库，直到连接恢复。

新建mysql_tool.py文件，写入：

import contextlib
import pymysql
from pymysql.cursors import DictCursor, Cursor

MYSQL_confgi = {
    'host': "127.0.0.1",
    'port': 3306,
    'user': 'root',
    'password': 'mypassword',
    'database': 'test',
}


class MySQLConnect():
    def __init__(self, cursorclass=DictCursor, config=MYSQL_config):
        self.connection = pymysql.connect(host=config['host'],
                                          port=config['port'],
                                          user=config['user'],
                                          password=config['password'],
                                          db=config['database'],
                                          cursorclass=cursorclass,
                                          charset='utf8mb4'
                                          )
        self.connection.autocommit(True)

    # 通过以下两个方法判断mysql是否连通，以及重连。
    def is_connected(self,num=28800,stime=3):  # 重试连接总次数为1天,这里根据实际情况自己设置,如果服务器宕机1天都没发现就......
        _number = 0
        _status = True
        while _status and _number <=num:
            """Check if the server is alive"""
            try:
                self.connection.ping(reconnect=True) #ping 校验连接是否异常
                _status = False
            except:
                if self.re_connect() == True: # 重新连接,成功退出
                    _status = False
                    break
                _number += 1
                time.sleep(stime)  # 连接不成功,休眠3秒钟,继续循环，知道成功或重试次数结束




    def re_connect(self):
        try:
            self.connection = pymysql.connect(host=self.MYSQL_config['host'],
                                              port=self.MYSQL_config['port'],
                                              user=self.MYSQL_config['user'],
                                              password=self.MYSQL_config['password'],
                                              db=self.MYSQL_config['db'],
                                              cursorclass=self.cursorclass,
                                              )
            self.connection.autocommit(True)
            return True
        except:
            return False




    @contextlib.contextmanager
    def cursor(self, cursor=None):
        """通过yield返回一个curosr对象
        """
        cursor = self.connection.cursor(cursor)
        try:
            yield cursor
        except Exception as err:
            self.connection.rollback()
            raise err
        finally:
            cursor.close()

    def close(self):
        self.connection.close()

   

    def fetchone(self, sql=None):
        self.is_connected()
        if sql:
            with self.cursor() as cursor:
                cursor.execute(sql)
                return cursor.fetchone()

        if self.connection.cursorclass == Cursor:
            return ()
        else:
            return {}

    def fetchall(self, sql=None):
        self.is_connected()
        if sql:
            with self.cursor() as cursor:
                cursor.execute(sql)
                return cursor.fetchall()
        return []

    def execute(self, sql=None):
        self.is_connected()
        if sql:
            with self.cursor() as cursor:
                return cursor.execute(sql)

    def executemany(self, sql=None, data=None):
        self.is_connected()
        if sql and data:
            with self.cursor() as cursor:
                return cursor.executemany(sql, data)


def get_a_conn(cursorclass=DictCursor):
    return MySQLConnect(cursorclass)

三、使用示例

test数据库下面有一个名为name_age的表，包含三个字段：id,name,age

3.1 查询，当查询多个的时候，返回一个列表，列表中每个元素为一个字典，通过字段名获取字段值。查询一个的时候，返回一个字典。

from mysql_tool import get_a_conn
mysql = get_a_conn()
sql = 'select * from name_age'
result = mysql.fetchall(sql)
for item in result:
    print(item['id'],item['name'],item['age'])

3.2 插入一条数据。如果字段为字符串，则需要用 "%s"作为占位符。如果字段的值中包含双引号，则使用pymysql的escape_string方法进行转义。

name = 'KoNo"Dio"Da'
age = 18
name = pymysql.escape_string(name)
sql = 'insert into name_age (name,age) values("%s",%s)' % (name, age)
mysql.execute(sql)

3.3 插入多条数据。此时可以不使用转义，也不要使用"%s"。

name = 'KoNo"Dio"Da'
age = 18
name2 = 'jojo'
age2 = 18
sql = 'insert into name_age (name,age) values(%s,%s)'
# 添加的数据的格式必须为list[tuple(),tuple(),tuple()]或者tuple(tuple(),tuple(),tuple())
data = [[name,age],[name2,age2]]
mysql.executemany(sql,data)

参考文献：

pymsql检查是否断开，断开重连: https://www.cnblogs.com/leijiangtao/p/11882107.html

python mysql使用持久链接: https://blog.csdn.net/wzm112/article/details/7745835