【postgres】连接池接口

pg连接池

import sys
import psycopg2.extras
from dbutils.pooled_db import PooledDB
import threading
import time

class PsycopgConn:
    _instance_lock = threading.Lock()

    def __init__(self, host, port, user, password, database, maxconnections, mincached, maxcached):
        self.host = host
        self.port = port
        self.user = user
        self.password = password
        self.database = database
        self.maxconnections = maxconnections   # 连接池允许的最大连接数，0 和 None 表示不限制连接数
        self.mincached = mincached             # 初始化时，链接池中至少创建的空闲的链接，0 表示不创建
        self.maxcached = maxcached             # 链接池中最多闲置的链接，0 和 None 不限制
        self.init_pool()

    def __new__(cls, *args, **kwargs):
        if not hasattr(cls, '_instance'):
            with PsycopgConn._instance_lock:
                if not hasattr(cls, '_instance'):
                    PsycopgConn._instance = object.__new__(cls)
                return PsycopgConn._instance

    def get_pool_conn(self):
        """
        获取连接池连接
        :return:
        """
        if not self._pool:
            self.init_pool()
        return self._pool.connection()

    def init_pool(self):
        """
        初始化连接池
        :return:
        """
        try:
            pool = PooledDB(
                creator=psycopg2,  # 使用连接数据库的模块 psycopg2
                maxconnections=self.maxconnections,  # 连接池允许的最大连接数，0 和 None 表示不限制连接数
                mincached=self.mincached,            # 初始化时，链接池中至少创建的空闲的链接，0 表示不创建
                maxcached=self.maxcached,            # 链接池中最多闲置的链接，0 和 None 不限制
                blocking=True,  # 连接池中如果没有可用连接后，是否阻塞等待。True，等待；False，不等待然后报错
                maxusage=None,  # 一个链接最多被重复使用的次数，None 表示无限制
                setsession=[],  # 开始会话前执行的命令列表
                host=self.host,
                port=self.port,
                user=self.user,
                password=self.password,
                database=self.database)
            self._pool = pool
            print('connect postgresql success')
        except:
            print('connect postgresql error')
            self.close_pool()

    def close_pool(self):
        """
        关闭连接池连接
        :return:
        """
        if self._pool != None:
            self._pool.close()

    def SelectSql(self, sql):
        """
        查询
        :param sql:
        :return:
        """
        try:
            conn = self.get_pool_conn()
            cursor = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)  # 设置返回格式为字典
            cursor.execute(sql)
            result = cursor.fetchall()
        except Exception as e:
            print('execute sql {0} is error'.format(sql))
            sys.exit('ERROR: load data from database error caused {0}'.format(str(e)))
        finally:
            cursor.close()
            conn.close()
        return result

    def InsertSql(self, sql):
        """
        插入数据
        :param sql:
        :return:
        """
        try:
            conn = self.get_pool_conn()
            cursor = conn.cursor()
            #time.sleep(5)
            cursor.execute(sql)
            result = True
        except Exception as e:
            print('ERROR: execute  {0} causes error'.format(sql))
            conn.rollback()
            sys.exit('ERROR: insert data from database error caused {0}'.format(str(e)))
        finally:
            cursor.close()
            conn.commit()
            conn.close()
        return result

    def UpdateSql(self, sql):
        """
        更新数据
        :param sql:
        :return:
        """
        try:
            conn = self.get_pool_conn()
            cursor = conn.cursor()
            cursor.execute(sql)
            result = True
        except Exception as e:
            print('ERROR: execute  {0} causes error'.format(sql))
            conn.rollback()
            sys.exit('ERROR: update data from database error caused {0}'.format(str(e)))
        finally:
            cursor.close()
            conn.commit()
            conn.close()
        return result
    
    def DelectSql(self, sql):
        """
        删除数据
        :param sql:
        :return:
        """
        try:
            conn = self.get_pool_conn()
            cursor = conn.cursor()
            #time.sleep(5)
            cursor.execute(sql)
            result = True
        except Exception as e:
            print('ERROR: execute  {0} causes error'.format(sql))
            conn.rollback()
            sys.exit('ERROR: delect data from database error caused {0}'.format(str(e)))
        finally:
            cursor.close()
            conn.commit()
            conn.close()
        return result