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