from psycopg2 import pool
class PostgreSql(object):
"""
minconn初始化时,链接池中至少创建的空闲的链接,0表示不创建
maxconn链接池中最多闲置的链接,0不限制
postgresql支持TCP_KEEPLIVE机制。有三个系统变量tcp_keepalives_idle,tcp_keepalives_interval,tcp_keepalives_count来设置postgresql如何处理死连接,
对于每个连接,postgresql会对这个连接空闲tcp_keepalives_idle秒后,主动发送tcp_keeplive包给客户端,以侦探客户端是否还活着,
当发送tcp_keepalives_count个侦探包,每个侦探包在tcp_keepalives_interval秒内没有回应,postgresql就认为这个连接是死的。于是切断这个死连接。
在postgresql,这三个参数都设为0将使用操作系统的默认值,在linux下,tcp_keepalives_idle一般是2个小时,也就是2个小时后,服务器才可以自动关掉死连接。
在实际应运中,可以自行调整以上参数
"""
def __init__(self, minconn, maxconn, host, port, user, password, dbname):
try:
self.connect_pool = pool.SimpleConnectionPool(minconn=minconn, maxconn=maxconn, host=host, port=port,
user=user,
password=password, dbname=dbname, keepalives=1,
keepalives_idle=30, keepalives_interval=10,
keepalives_count=5)
except Exception as e:
print(e)
def get_connect(self):
conn = self.connect_pool.getconn()
cursor = conn.cursor()
return conn, cursor
def close_connect(self, conn, cursor):
cursor.close()
self.connect_pool.putconn(conn)
def close_all(self):
self.connect_pool.closeall()
# 执行查询
def execute(self, sql, value=None):
conn, cursor = self.get_connect()
try:
cursor.execute(sql, value)
result = cursor.fetchall()
except Exception as e:
raise e
finally:
self.close_connect(conn, cursor)
return result
# 执行增删改
def execute_others(self, sql, value=None):
result = False
conn, cursor = self.get_connect()
try:
cursor.execute(sql,value)
# res = cursor.fetchone()
result = True
except Exception as e:
conn.rollback()
raise e
finally:
conn.commit()
self.close_connect(conn, cursor)
return result
'''
执行添加sql语句,并返回当前添加的数据id,使用此方法时注意insert语句的末尾加上 return id
: param sql: sql语句
: example eg: insert into lty_order_details(order_id, product_id, quantity, times) values(33,1,3,5) returning id
'''
def execute_insert(self, sql, value=None):
conn, cursor = self.get_connect()
try:
cursor.execute(sql, value)
res = cursor.fetchone()
except Exception as e:
conn.rollback()
raise e
finally:
conn.commit()
self.close_connect(conn, cursor)
return res
from app.config import settings
def create_db_pool():
return PostgreSql(minconn=settings.MINCONN, maxconn=settings.MAXCONN, host=settings.HOST, port=settings.PORT,
user=settings.USER, password=settings.PASSWORD, dbname=settings.DBNAME)
pg_pool = create_db_pool()
if __name__ == '__main__':
# sql = "ALTER TABLE test ADD COLUMN IF NOT EXISTS password4 TEXT;"
# sql = "update test set password=2 where id=1"
# sql = "CREATE TABLE test(ID INT PRIMARY KEY NOT NULL,NAME TEXT NOT NULL,AGE INT NOT NULL)"
# sql = "select id,name from test where id=1"
# sql = "select id, name,age from test where id=1"
sql = 'insert into test (id,name, age, password) VALUES (3,1, 1,1)'
# sql = 'delete from test where id= 2'
user = create_db_pool().execute_others(sql)
# user = create_db_pool().execute(sql)
print(user)
python pgsql连接池
最后编辑于 :
©著作权归作者所有,转载或内容合作请联系作者
平台声明:文章内容(如有图片或视频亦包括在内)由作者上传并发布,文章内容仅代表作者本人观点,简书系信息发布平台,仅提供信息存储服务。
平台声明:文章内容(如有图片或视频亦包括在内)由作者上传并发布,文章内容仅代表作者本人观点,简书系信息发布平台,仅提供信息存储服务。