python pgsql连接池

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)