flask 使用pymysql 操作mysql 老断开链接报错 10054

Question

使用flask写的项目 不定时的就回有这样的报错。然后mysql 服务就停止了，需要手动开启mysql服务

Traceback (most recent call last):
  File "D:\newflaskdome\venv\lib\site-packages\pymysql\connections.py", line 732, in _read_bytes
    data = self._rfile.read(num_bytes)
  File "C:\Users\huoda\AppData\Local\Programs\Python\Python39\lib\socket.py", line 704, in readinto
    return self._sock.recv_into(b)
ConnectionResetError: [WinError 10054] 远程主机强迫关闭了一个现有的连接。

During handling of the above exception, another exception occurred:

Answer 1

是因为mysql长链接超时断开，可以考虑采用检测到断开后尝试重连。

可以参考下面的代码

#! /usr/bin/python
# coding=utf-8

import pymysql as MySQL

import time

class mysql:
    def __init__(self,
              host = '',
              user = '',
              passwd = '',
              db = '',
              port = 3306,
              charset = 'utf8'):
        self.host = host
        self.host   = host
        self.user   = user
        self.passwd = passwd
        self.db     = db
        self.port   = port
        self.charset= charset
        self.conn   = None
        self._conn()

    def _conn(self):
        try:
            self.conn = MySQL.Connection(host = self.host, user = self.user, passwd = self.passwd, db = self.db, port = self.port , charset = self.charset)
            print("数据库连接成功")
            return True
        except Exception as e:
            print("数据库连接失败:"+str(e))
            return False

    #通过ping()实现数据库的长连接
    def _reConn(self,num = 28800,stime = 3):
        _number = 0
        _status = True
        while _status and _number <= num:
            try:
                #ping校验连接是否异常
                self.conn.ping()
                _status = False
            except:
                print("数据库断开连接，重连")
                if self._conn()==True: 
                    _status = False
                    break
                _number +=1
                time.sleep(stime)      

              
    def querry(self,sql):
        try:
            self._reConn()
            self.cursor = self.conn.cursor()
            self.cursor.execute(sql)
            results = self.cursor.fetchall()
            self.cursor.close()
            return results;
        except Exception as e:
            print("querry异常:"+str(e))
            return None;

    def insert(self,sql,param):
        try:
            self._reConn()
            self.cursor = self.conn.cursor()
            res = self.cursor.execute(sql,param);
            self.conn.commit();
            self.cursor.close()
            return res;
        except Exception as e:
            self.conn.rollback();
            print("insert异常:"+str(e))
            return False;

    def close(self):
        self._reConn()
        self.conn.close();