Python自制简易MySQL连接池实现指南

# 推荐目录格式, ThemisPool.py & db.cnf 只需要在同级目录下即可
[your python project]
    |
    |
    |-- util
         |
         |-- db.cnf
         |
         |-- ThemisPool.py

# 导入依赖
# mysql连接基本库
import pymysql

# 读取配置文件所需要的库
import configparser
import os

# 线程管理所需要的库
import threading


# 创建配置类用户读取配置文件
class Config(object):
    def __init__(self, configFileName='db.cnf'):
        file = os.path.join(os.path.dirname(__file__), configFileName)
        self.config = configparser.ConfigParser()
        self.config.read(file)

    def getSections(self):
        return self.config.sections()

    def getOptions(self, section):
        return self.config.options(section)

    def getContent(self, section):
        result = {}
        for option in self.getOptions(section):
            value = self.config.get(section, option)
            result[option] = int(value) if value.isdigit() else value
        return result
 
# 将连接所需要的参数封装在对象中 
# 依次为: 数据库密码、需要连接的库名、主机地址[默认 localhost]、端口号[默认 3306]、初始化连接数[默认 3]、最大连接数[默认 6]
class parameter(object):
    def __init__(self, password, database, host="localhost",port="3306" user="root", initsize=3, maxsize=6):
        self.host = str(host)
        self.port = int(port)
        self.user = str(user)
        self.password = str(password)
        self.database = str(database)
        self.maxsize = int(maxsize)
        self.initsize = int(initsize)

# 连接池
class ThemisPool(parameter):
    def __init__(self, fileName='db.cnf', configName='mysql'):
        # 加载配置文件, 配置文件名默认为 'db.cnf', 配置标签默认为 'mysql'
        self.config = Config(fileName).getContent(configName)
        super(ThemisPool, self).__init__(**self.config)
        # 创建队列作为 池
        self.pool = queue.Queue(maxsize=self.maxsize)
        self.idleSize = self.initsize
        # 创建线程锁
        self._lock = threading.Lock()
        # 初始化连接池
        for i in range(self.initsize):
            # 创建 初始化连接数 数量的连接放入池中
            self.pool.put(self.createConn())
        # 启动日志
        print('\033[1;32m ThemisPool connect database {database}, login is {user} \033[0m'.format(database=self.database,
                                                                                 user=self.user))
                                                                          
    # 生产连接
    def createConn(self):
        # 使用mysql基本类 
        # pymysql.connect 参数这里不做解释,具体请查阅官网 https://pypi.org/project/PyMySQL/
        return pymysql.connect(host=self.host,
                               port=self.port,
                               user=self.user,
                               password=self.password,
                               database=self.database,
                               charset='utf8')
    
    # 获取连接
    def getConn(self):
        self._lock.acquire()
        try:
            # 如果池中连接够直接获取
            if not self.pool.empty():
                self.idleSize -= 1
            else:
                # 否则重新添加新连接
                if self.idleSize < self.maxsize:
                    self.idleSize += 1
                    self.pool.put(self.createConn())
        finally:
            self._lock.release()
            return self.pool.get()
     
    # 释放连接
    def releaseCon(self, conn=None):
        try:
            self._lock.acquire()
            # 如果池中大于初始值就将多余关闭，否则重新放入池中
            if self.pool.qsize() < self.initsize:
                self.pool.put(conn)
                self.idleSize += 1
            else:
                try:
                    # 取出多余连接并关闭
                    surplus = self.pool.get()
                    surplus.close()
                    del surplus
                    self.idleSize -= 1
                except pymysql.ProgrammingError as e:
                    raise e
        finally:
            self._lock.release()
      
      
    # 拉取数据（查询）
    # 可用语句类型 (select)
    def fetchone(self, sql):
        themis = None
        cursor = None
        try:
            themis = self.getConn()
            cursor = themis.cursor()
            cursor.execute(sql)
            return cursor.fetchall()
        except pymysql.ProgrammingError as e:
            raise e
        except pymysql.OperationalError as e:
            raise e
        except pymysql.Error as e:
            raise e
        finally:
            cursor.close()
            self.releaseCon(themis)
     
    # 更新
    # 可用语句类型 (insert, update, delete)
    def update(self, sql):
        themis = None
        cursor = None
        try:
            themis = self.getConn()
            cursor = themis.cursor()
            cursor.execute(sql)
            return cursor.lastrowid
        except pymysql.ProgrammingError as e:
            raise e
        except pymysql.OperationalError as e:
            raise e
        except pymysql.Error as e:
            raise e
        finally:
            themis.commit()
            cursor.close()
            self.releaseCon(themis)
            
     # 释放连接池本身
     def __del__(self):
        try:
            while True:
                conn = self.pool.get_nowait()
            if conn:
                conn.close()
        except queue.Empty:
            pass

[mysql]
host = localhost
user = root
password = 12345678
database = practice
initsize = 3
maxsize = 6

from util.ThemisPool import ThemisPool

# 初始化ThemisPool连接池 (Initialize the ThemisPool connection pool)
db = ThemisPool()

# 查询拉取数据,函数会直接返回数据 (Query pull data.It returns data directly)
selectSql = "select * from user;"
data = db.fetchone(selectSql)

# 增、删、改语句, 如果有使用mysql自增长插入的值函数会返回自增长的数据 (insert,upate delete and alter. If there is a value function inserted using mysql self-growth, it will return self-growth data)
insertSql = "insert into user values(null,'user001','123456')"
id = db.update(selectSql)

# myDB.cnf

[mysqlConfig]
host = localhost
user = root
password = 12345678
database = practice
initsize = 3
maxsize = 6

# 使用时
...
db = ThemisPool(fileName='myDB.cnf', configName='mysqlConfig')
...