数据库链接池

1.目录结构

image.png

2.代码

• singleton.py单例模式装饰器，解决多线程

import threading

def singleton(cls,*args,**kw):
    instances = {}
    instance_lock = threading.Lock()
    def _singleton():
        with instance_lock:
            if cls not in instances:
                instances[cls] = cls(*args,**kw)
            return instances[cls]
    return _singleton

• db_dbutils_init.py数据库连接池类

#coding:utf-8

from DBUtils.PooledDB import PooledDB
import db_config as config
import random
import pymssql
from DBUtility.singleton import singleton

class MyConnectionPool(object):
    __pool = None

    def __enter__(self):
        self.conn = self.__getconn()
        self.cursor = self.conn.cursor()

    def __getconn(self):
        if self.__pool is None:
            i = random.randint(1, 100)
            print("线程池的随机数" + str(i))
            self.__pool = PooledDB(pymssql, 5, host=config.DB_HOST, user=config.DB_USER, password=config.DB_PASSWORD,
                                 database=config.DB_DBNAME, maxconnections=10)

        conn = self.__pool.connection()

        return conn

    def __exit__(self, exc_type, exc_val, exc_tb):
        self.cursor.close()
        self.conn.close()

    def getconn(self):
        conn = self.__getconn()
        cursor = conn.cursor()
        return cursor, conn

class MyConnectionPoolDown(object):
    __pool = None

    def __enter__(self):
        self.conn = self.__getconn()
        self.cursor = self.conn.cursor()

    def __getconn(self):
        if self.__pool is None:
            i = random.randint(1, 100)
            print("线程池的随机数" + str(i))
            self.__pool = PooledDB(pymssql, 5, host=config.DB_HOST, user=config.DB_USER, password=config.DB_PASSWORD,
                                 database=config.DB_DBNAME_DOWN, maxconnections=10)

        conn = self.__pool.connection()

        return conn

    def __exit__(self, exc_type, exc_val, exc_tb):
        self.cursor.close()
        self.conn.close()

    def getconn(self):
        conn = self.__getconn()
        cursor = conn.cursor()
        return cursor, conn

@singleton
def get_my_connection():
    return MyConnectionPool()

@singleton
def get_my_connection_down():
    return MyConnectionPoolDown()

• SqlHelper.pySql数据库

#coding:utf-8

from DBUtility.db_dbutils_init import get_my_connection,get_my_connection_down

class SqLHelper(object):

    def __init__(self):
        self.db = get_my_connection()

    def close(self, cursor, conn):

        cursor.close()
        conn.close()

    def selectOne(self, sql):

        cursor,conn  = self.db.getconn()
        cursor.execute(sql)
        res = cursor.fetchone()
        self.close(cursor, conn)
        return res

    def selectAll(self, sql):
        cursor,conn  = self.db.getconn()
        cursor.execute(sql)
        res = cursor.fetchall()
        self.close(cursor, conn)
        return res

    def insertOne(self, sql, data):

        cursor,conn  = self.db.getconn()
        cursor.execute(sql, data)
        count = conn.commit()
        self.close(cursor, conn)
        return count

    def insertMany(self, sql, data):

        cursor,conn  = self.db.getconn()
        cursor.executemany(sql, data)
        count = conn.commit()
        self.close(cursor, conn)
        return count

• dbconfig.py