Python+sqlite:一个简单的数据库连接池

import sqlite3
import os
from threading import Lock, BoundedSemaphore


class ConnPool:

    def __init__(self, db_name, max_conn):
        self.__MaxConn = max_conn  # 最大连接数
        self.__CurConn = 0  # 当前连接数
        self.__FreeConn = 0  # 空闲连接数
        self.__lock = Lock()  # 锁
        self.__connList = []  # 连接池

        self.dbName = db_name  # 数据库名称
        self.init_conn()  # 初始化

    def init_conn(self):
        for i in range(self.__MaxConn):
            try:
                conn = sqlite3.connect(os.path.abspath('.') + '\\' + self.dbName + '.db', check_same_thread=False)
                if conn is not None:
                    self.__connList.append(conn)  # 将获取到的数据库连接加入连接池
                    self.__FreeConn += 1
            except Exception as e:
                print("Get Sqlite Connection Failed: {0}".format(i) + e)
        self.__sem = BoundedSemaphore(self.__FreeConn)  # 初始化信号量
        self.__MaxConn = self.__FreeConn

    # 每当有请求时，从数据库连接池中返回一个可用连接，并更新使用和空闲连接数
    def get_connection(self):
        if 0 == len(self.__connList):
            return None
        self.__sem.acquire()  # 消耗资源使计数器递减，为0时阻塞调用
        self.__lock.acquire()  # 加锁
        conn = self.__connList.pop()  # 从连接列表中弹出一个数据库连接

        self.__FreeConn -= 1  # 空闲连接计数-1
        self.__CurConn += 1  # 当前连接计数+1

        self.__lock.release()  # 释放锁
        return conn

    # 释放当前使用的连接
    def release_connection(self, conn):
        if conn is None:
            return False

        self.__lock.acquire()       # 加锁

        self.__connList.append(conn)    # 归还数据库连接
        self.__FreeConn += 1
        self.__CurConn -= 1

        self.__lock.release()   # 释放锁
        try:
            self.__sem.release()    # 归还资源使计数器递增
        except ValueError:
            print("超出计数器上限.")
        return True

    # 销毁数据库连接池
    def destroy_pool(self):
        self.__lock.acquire()       # 加锁
        if len(self.__connList) > 0:
            for it in self.__connList:
                it.close()
            self.__CurConn = 0
            self.__FreeConn = 0
            self.__connList.clear()
        self.__lock.release()       # 释放锁

    # 获取空闲的连接数
    def get_free_conn(self):
        return self.__FreeConn

    # 析构函数
    def __del__(self):
        self.destroy_pool()


# 创建一个数据库资源管理类，传入一个类型为ConnPool对象，生成一个数据库连接，析构函数中释放此连接
class ConnPoolRAII:
    def __init__(self, conn_pool: ConnPool):
        self.connRAII = conn_pool.get_connection()
        self.poolRAII = conn_pool
        self.__lock = Lock()    # 数据库操作锁
        self.init_db()          # 初始化数据库

    # 数据库初始化
    def init_db(self):
        # 若用于存储下位机的表不存在，则进行初始化创建
        if not self.is_exist('Client'):
            self.operate('''create table Client(
                            ID integer primary key autoincrement,
                            Client_name text(10) not null,
                            IPAddress text(20) not null,
                            Status numeric default 0
                            )''')

    # 检查指定表是否存在
    def is_exist(self, table_name):
        res = self.query('''select count(*) from sqlite_master where type='table' and name='{0}'
                        '''.format(table_name))
        if res.fetchone()[0] == 0:
            return False
        return True

    # 为每一个客户机创建一个用于存储数据的表格
    def create_table(self, client_name):
        return self.operate('''
                    create table {0}(
                        ID integer primary key autoincrement,
                        Weight real default 0,
                        Temperature real default 0,
                        Date integer not null
                        );
                        '''.format(client_name))

    # 新增客户机函数
    def add_client(self, ip):
        client_name = 'Client{0}'.format(ip[ip.rfind('.') + 1:])    # 获取客户端名称，以Clientxxx为格式
        res = self.query("select * from Client where IPAddress=='{0}'".format(client_name))     # 查询Client表中是否存在当前客户端信息
        sql_insert = "insert into Client (Client_name, IPAddress) values ('{0}','{1}')".format(client_name, ip)
        if res and (res.fetchone() is None):
            if self.operate(sql_insert) and self.create_table(client_name):
                print("创建表成功")      # 测试用，后续改为日志
                return True
            else:
                print("创建表失败,检查客户端是否已存在！")
                return False

    # 用于查询类操作，要求输入sql语句
    def query(self, sql):
        self.__lock.acquire()       # 加锁
        c = self.connRAII.cursor()  # 获取cursor
        try:
            res = c.execute(sql)    # 执行sql
            self.__lock.release()   # 释放锁
            return res              # 返回查询结果
        except Exception as e:
            print(e)
            self.__lock.release()   # 释放锁
            return False            # 返回False

    # 增删改等操作，提供sql语句，返回bool值
    def operate(self, sql):
        self.__lock.acquire()       # 加锁
        c = self.connRAII.cursor()  # 获取cursor
        try:
            c.execute(sql)          # 执行sql
            self.connRAII.commit()  # 提交执行结果
            self.__lock.release()   # 释放锁
            return True             # 返回操作成功
        except Exception as e:
            print(e)                # 打印错误信息
            self.__lock.release()   # 释放锁
            return False            # 返回False

    # 析构函数
    def __del__(self):
        self.poolRAII.release_connection(self.connRAII)