#!/usr/bin/env python # -*- coding: utf-8 -*- # @Author: 风过无言花易落 # @Date : 2022/07/04 22:30 # @Desc : polardb-o (底层连接 postgres 数据库) 数据库连接池操作 import psycopg2.extras from DBUtils.PooledDB import PooledDB import threading class PsycopgConn: _instance_lock = threading.Lock() def __init__(self,host, port, user, password, database): """ 初始化连接池 :return: """ try: pool = PooledDB( creator=psycopg2, # 使用连接数据库的模块 psycopg2 maxconnections=10, # 连接池允许的最大连接数,0 和 None 表示不限制连接数 mincached=1, # 初始化时,链接池中至少创建的空闲的链接,0 表示不创建 maxcached=8, # 链接池中最多闲置的链接,0 和 None 不限制 blocking=True, # 连接池中如果没有可用连接后,是否阻塞等待。True,等待;False,不等待然后报错 maxusage=None, # 一个链接最多被重复使用的次数,None 表示无限制 setsession=[], # 开始会话前执行的命令列表 host=host, port=port, user=user, password=password, database=database) self._pool = pool except: print('connect postgresql error') self.close_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 close_pool(self): """ 关闭连接池连接 :return: """ if self._pool != None: self._pool.close() def fetch_one(self, sql): """ 查询 :param sql: :return: """ try: conn = self.get_pool_conn() # cursor = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor) # 设置返回格式为字典 cursor = conn.cursor() cursor.execute(sql) result = cursor.fetchone() except Exception as e: print('execute sql {0} is error:{1}'.format(sql,e)) finally: cursor.close() conn.close() return result def fetch_all(self, sql): """ 查询 :param sql: :return: """ try: conn = self.get_pool_conn() # cursor = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor) # 设置返回格式为字典 cursor = conn.cursor() cursor.execute(sql) result = cursor.fetchall() except Exception as e: print('execute sql {0} is error:{1}'.format(sql,e)) finally: cursor.close() conn.close() return result def InsertSql(self, sql): """ 插入数据 :param sql: :return: """ try: conn = self.get_pool_conn() cursor = conn.cursor() cursor.execute(sql) result = True except Exception as e: print('execute sql {0} is error:{1}'.format(sql,e)) finally: cursor.close() conn.commit() conn.close() return result def execute_sql(self, sql): """ 更新数据 :param sql: :return: """ try: conn = self.get_pool_conn() cursor = conn.cursor() cursor.execute(sql) result = True except Exception as e: print('execute sql {0} is error:{1}'.format(sql,e)) finally: cursor.close() conn.commit() conn.close() return result if __name__ == "__main__": a= PsycopgConn('10.10.10.255',12345,'dbname','dbpwd','database') b = a.fetch_one('select 1 ') print(b) a.close_pool()
python3创建连接池连接polardb-o(postgre)数据库
最新推荐文章于 2024-03-29 11:58:22 发布