解决Windows下使用waitress+WSGI服务器pymysql报错AttributeError: ‘NoneType‘ object has no attribute ‘read‘

问题

之前一直是在vmware里开虚拟机跑服务器的，用的是gunicorn，由于gunicorn只支持UNIX系统，将项目迁移到windows下时折腾了好久，后面用的waitress跑起来了。在调试接口的时候发现查询mysql时一直报错，提示下面的错误。

  File "F:\Anaconda\envs\venv\lib\site-packages\dbutils\steady_db.py", line 606, in tough_method
    result = method(*args, **kwargs)  # try to execute
  File "F:\Anaconda\envs\venv\lib\site-packages\pymysql\cursors.py", line 163, in execute
    result = self._query(query)
  File "F:\Anaconda\envs\venv\lib\site-packages\pymysql\cursors.py", line 321, in _query
    conn.query(q)
  File "F:\Anaconda\envs\venv\lib\site-packages\pymysql\connections.py", line 505, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "F:\Anaconda\envs\venv\lib\site-packages\pymysql\connections.py", line 724, in _read_query_result
    result.read()
  File "F:\Anaconda\envs\venv\lib\site-packages\pymysql\connections.py", line 1069, in read
    first_packet = self.connection._read_packet()
  File "F:\Anaconda\envs\venv\lib\site-packages\pymysql\connections.py", line 663, in _read_packet
    recv_data = self._read_bytes(bytes_to_read)
  File "F:\Anaconda\envs\venv\lib\site-packages\pymysql\connections.py", line 683, in _read_bytes
    data = self._rfile.read(num_bytes)
AttributeError: 'NoneType' object has no attribute 'read'

查了很多资料，发现是在多线程环境下调用mysql连接池的时候，各线程交叉释放了其他线程中正在执行的任务，因此导致报错

解决办法

给线程加锁，在线程执行mysql查询任务之前加一个锁，在执行完毕之后释放掉，这样就能正常使用了

def connect(func):

    def wrapper(self, *args, **kw):
        try:
            lock.acquire()
            self._conn.ping()
        except Exception:
            self.get_connection()
        result = func(self, *args, **kw)
        lock.release()
        return result 

    return wrapper


class Mysql:
    def __init__(self, host, port, user=None, pwd=None):
        self._host = host
        self._port = port
        self._user = user
        self._pwd = pwd
        self._query_list = []
        try:
            self.pool = PooledDB(
                creator=pymysql, # 使用连接数据库的模块
                maxconnections=10,  # 连接池允许的最大连接数，0 和 None表示不限制连接数
                mincached=3, # 初始化时，链接池中至少创建的空闲的链接，0表示不创建
                maxcached=3, # 链接池中最多闲置的链接，0和None表示不限制
                maxshared=0, # 链接池中最多共享的链接数量，0和None表示全部共享， 注：因为pymysql 和 mysqldb等模块的threadsafety都为1，所以值无论设置为多少，_maxcached永远为0，所有永远是所有链接都共享
                blocking=True, # 链接池中如果没有可用连接后，是否阻塞等待，True：等待  False：不等待然后报错
                maxusage=None, # 一个连接最多被重复使用的次数，None表示无限制
                setsession=[], # 开始会话迁执行的命令列表。如：["set datestyle to ...","set time zone ..."]
                ping=1, #ping mysql服务端，检查服务是否可用 0 = None = never, 1 = default = whenever it is requested, 2 = when a cursor is created, 4 = when a query is executed, 7 = always
                host=host,
                port=port,
                user=user,
                password=pwd,
                database='dmp'
            )
            self._conn = self.get_connection()
        except Exception:
            return

    def get_connection(self):
        return self.pool.connection()
    
    @connect
    def query(self, sql):
    	with self._conn.cursor(cursor=pymysql.cursors.DictCursor) as cursor:
             cursor.execute(sql)
             data = cursor.fetchall()
             self._conn.commit()
         return data