python3----mysql线程池工具封装

python3----mysql线程池工具封装

#!/usr/python/bin/python3
# -*- coding:utf-8 -*-

import logging
from queue import Queue
import pymysql
LOG = logging.getLogger(__name__)


class ConnectionPool(object):

    def __init__(self, **kwargs):
        self.size = kwargs.get('size', 10)
        self.kwargs = kwargs
        self.conn_queue = Queue(maxsize=self.size)

        for i in range(self.size):
            self.conn_queue.put(self._create_new_conn())

    def _create_new_conn(self):
        return pymysql.connect(host=self.kwargs.get('host', '127.0.0.1'),
                               user=self.kwargs.get('user'),
                               db=self.kwargs.get('dbname'),
                               passwd=self.kwargs.get('password'),
                               port=self.kwargs.get('port'),
                               connect_timeout=5)

    def _put_conn(self, conn):
        self.conn_queue.put(conn)

    def _get_conn(self):
        conn = self.conn_queue.get()
        if conn is None:
            self._create_new_conn()
        return conn

    def exec_sql(self, sql):
        conn = self._get_conn()
        try:

            cur = conn.cursor()
            cur.execute(sql)
            return cur.fetchall()
        except pymysql.ProgrammingError as e:
            LOG.error('execute sql {{0}} error {1}'.format(sql, e))
            raise e
        except pymysql.OperationalError as e:
            conn = self._create_new_conn()
            raise e
        finally:
            self._put_conn(conn)

    def __del__(self):
        try:
            while True:
                conn = self.conn_queue.get_nowait()
                if conn:
                    conn.close()
        except Exception as e:
            pass


if __name__ == '__main__':
    mysql_tool = ConnectionPool(
        host='127.0.0.1',
        user='root',
        password='******',
        dbname = 'mysqldb',
        port=3306,
        size=5
    )
    res = mysql_tool.exec_sql('select * from t_user')
    print(res)

请勿重复造轮子，拿走不谢！！！