python 主线程读取数据写入队列，多线程消费队列数据

备忘记录
主线程读取数据写入队列，多线程消费队列数据。

# -*- coding: UTF-8 -*-
# !/usr/bin/python3

import logging
import queue
import threading
import time

import pymysql
from dbutils.pooled_db import PooledDB

###############参数配置######################
host = "host"
port = 3306
db = "db"
user = "user"
passwd = "passwd"
# 连接池最小数
mincached = 1
# 连接池最大数
maxcached = 20
# 并发执行线程数
threads = 1
# 睡眠时间-秒
sleepSecend = 0
############################################

logging.basicConfig(filename="log.log", level=logging.INFO,
                    format="%(asctime)s: %(thread)d - %(levelname)s - %(message)s",
                    datefmt="%Y-%m-%d %H:%M:%S")

lock = threading.Lock()

job_begin = time.time() * 1000

def clearData(data):
    deleteIds = []

    try:
        querySql = "querySql2"

        param01 = bytes.decode(data['param01'])
        param02 = bytes.decode(data['param02'])

        param = (param01 + "," + param02).split(",")
        # 加锁 防止多线程模式下错误的释放了其他线程的连接，导致的AttributeError: 'NoneType' object has no attribute 'read'
        lock.acquire()
        rr = mysql.getAll(querySql, param)

        for r in rr:
            if r["delete"] == 1:
                deleteIds.append(bytes.decode(r['id']))

        if len(deleteIds) != 0:
            # 批量删除id 数组适配为'id1','id2','id3'格式
            deleteSql = "delete from table where id in (%s) " % ','.join(
                ['%s'] * len(deleteIds))
            mysql.delete(deleteSql, deleteIds)
            logging.info("delete sql :" + deleteSql + ",".join(deleteIds))

    except Exception as e:
        logging.error("clearData catch exception:" + str(e))
    finally:
        # 释放锁
        lock.release()
        # 手动提交事务
        mysql.end()


def consumer(threadNum):
    while True:
        if q.empty():
            logging.info(
                "[thread-" + str(threadNum) + "]queue is empty. cost: " + str(time.time() * 1000 - job_begin) + "ms")
            return
        else:
            data = q.get()
            logging.info("[thread-" + str(threadNum) + "]from queue got one record:" + str(data) + ".left size:" + str(
                q.qsize()))
            clearData(data)
            time.sleep(sleepSecend)


class Mysql(object):
    """
    MYSQL数据库对象，负责产生数据库连接 , 此类中的连接采用连接池实现获取连接对象：conn = Mysql.getConn()
            释放连接对象;conn.close()或del conn
    """
    # 连接池对象
    __pool = None

    def __init__(self):
        # 数据库构造函数，从连接池中取出连接，并生成操作游标
        self._conn = Mysql.__getConn()
        self._cursor = self._conn.cursor()

    @staticmethod
    def __getConn():
        """
        @summary: 静态方法，从连接池中取出连接
        @return MySQLdb.connection
        """
        if Mysql.__pool is None:
            __pool = PooledDB(creator=pymysql, mincached=mincached, maxcached=maxcached,
                              host=host, port=port, user=user, passwd=passwd,
                              db=db, use_unicode=False, charset="utf8",
                              cursorclass=pymysql.cursors.DictCursor)
        return __pool.connection()

    def getAll(self, sql, param=None):
        """
        @summary: 执行查询，并取出所有结果集
        @param sql:查询ＳＱＬ，如果有查询条件，请只指定条件列表，并将条件值使用参数[param]传递进来
        @param param: 可选参数，条件列表值（元组/列表）
        @return: result list(字典对象)/boolean 查询到的结果集
        """
        if param is None:
            count = self._cursor.execute(sql)
        else:
            count = self._cursor.execute(sql, param)
        if count > 0:
            result = self._cursor.fetchall()
        else:
            result = False
        return result

    def getOne(self, sql, param=None):
        """
        @summary: 执行查询，并取出第一条
        @param sql:查询ＳＱＬ，如果有查询条件，请只指定条件列表，并将条件值使用参数[param]传递进来
        @param param: 可选参数，条件列表值（元组/列表）
        @return: result list/boolean 查询到的结果集
        """
        if param is None:
            count = self._cursor.execute(sql)
        else:
            count = self._cursor.execute(sql, param)
        if count > 0:
            result = self._cursor.fetchone()
        else:
            result = False
        return result

    def getMany(self, sql, num, param=None):
        """
        @summary: 执行查询，并取出num条结果
        @param sql:查询ＳＱＬ，如果有查询条件，请只指定条件列表，并将条件值使用参数[param]传递进来
        @param num:取得的结果条数
        @param param: 可选参数，条件列表值（元组/列表）
        @return: result list/boolean 查询到的结果集
        """
        if param is None:
            count = self._cursor.execute(sql)
        else:
            count = self._cursor.execute(sql, param)
        if count > 0:
            result = self._cursor.fetchmany(num)
        else:
            result = False
        return result

    def insertOne(self, sql, value):
        """
        @summary: 向数据表插入一条记录
        @param sql:要插入的ＳＱＬ格式
        @param value:要插入的记录数据tuple/list
        @return: insertId 受影响的行数
        """
        self._cursor.execute(sql, value)
        return self.__getInsertId()

    def insertMany(self, sql, values):
        """
        @summary: 向数据表插入多条记录
        @param sql:要插入的ＳＱＬ格式
        @param values:要插入的记录数据tuple(tuple)/list[list]
        @return: count 受影响的行数
        """
        count = self._cursor.executemany(sql, values)
        return count

    def __getInsertId(self):
        """
        获取当前连接最后一次插入操作生成的id,如果没有则为０
        """
        self._cursor.execute("SELECT @@IDENTITY AS id")
        result = self._cursor.fetchall()
        return result[0]['id']

    def __query(self, sql, param=None):
        if param is None:
            count = self._cursor.execute(sql)
        else:
            count = self._cursor.execute(sql, param)
        return count

    def update(self, sql, param=None):
        """
        @summary: 更新数据表记录
        @param sql: ＳＱＬ格式及条件，使用(%s,%s)
        @param param: 要更新的  值 tuple/list
        @return: count 受影响的行数
        """
        return self.__query(sql, param)

    def delete(self, sql, param=None):
        """
        @summary: 删除数据表记录
        @param sql: ＳＱＬ格式及条件，使用(%s,%s)
        @param param: 要删除的条件 值 tuple/list
        @return: count 受影响的行数
        """
        return self.__query(sql, param)

    def begin(self):
        """
        @summary: 开启事务
        """
        self._conn.autocommit(0)

    def end(self, option='commit'):
        """
        @summary: 结束事务
        """
        if option == 'commit':
            self._conn.commit()
        else:
            self._conn.rollback()

    def dispose(self, isEnd=1):
        """
        @summary: 释放连接池资源
        """
        if isEnd == 1:
            self.end('commit')
        else:
            self.end('rollback');
        self._cursor.close()
        self._conn.close()


if __name__ == "__main__":
    q = queue.Queue()
    mysql = Mysql()

    sql = "querySql"
    try:
        start = time.time() * 1000
        result = mysql.getAll(sql)
        for col in result:
            q.put(col)
        logging.info("log: " + str(time.time() * 1000 - start) + "ms")

    except Exception as e:
        logging.error("failed exception: " + str(e))

    try:
        for i in range(threads):
            threading.Thread(target=consumer, args=(i,)).start()

    except Exception as e:
        logging.error("threading crate exception: " + str(e))

参考资料：
https://docs.python.org/3/library/queue.html
https://www.cnblogs.com/hi3254014978/p/15084903.html
https://webwareforpython.github.io/DBUtils/main.html
https://github.com/WebwareForPython/DBUtils
https://www.jianshu.com/p/e30d302ebdeb