PostgreSql连接池(psycopg2.pool)

连接池的作用及原理

正常访问数据库的过程中，每次访问都需要创建数据库的连接，这会消耗大量的资源；连接池的就是为数据库连接建立一个“缓冲区”，预先在缓冲池中放入一定数量的连接对象，当需要建立数据库连接时，只需从“缓冲池”中取出一个，使用完毕之后再放回去；且连接池允许多个客户端使用缓存起来的连接对象，这些对象可以连接数据库，它们是共享的、可被重复使用的；使用连接池可以节省大量资源，提高程序运行速度。

连接池的基本原理是：先初始化一定的数据库连接对象，并且把这些连接保存在连接池中。这些数据库连接的数量是由最小数据库连接数来设定的。连接池的最大数据库连接数量限定了这个连接池能占有的最大连接数，当应用程序向连接池请求的连接数超过最大连接数量时，这些请求将被加入到等待队列中。当程序需要访问数据库的时候，如果连接池中有空闲的连接，可直接得到一个连接；如果连接池对象中没有空闲的连接，且连接数没有达到最大，会创建一个新的连接从连接池中取出一个连接，数据库操作结束后，再把这个用完的连接重新放回连接池。

Python连接PostgreSql

首先安装 psycopg2模块，pycopg2是Python编程语言的PostgreSQL数据库的适配器。

pip install psycopg2

查阅psycopg2官方手册可以找到psycopg2.pool连接池，这里贴出连接池的介绍

psycopg2.pool.png

查阅完文档，我们对PostgreSql连接池进行封装，代码如下：

from config import *
from psycopg2 import pool
class PostgreSql:
    def __init__(self):
        try:
            self.connectPool = pool.SimpleConnectionPool(2, 10, host=POSTGRE_HOST, port=POSTGRE_PORT,
                                                         user=POSTGRE_USER, password=POSTGRE_PASSWORD,
                                                         database=POSTGRE_DATABASE, keepalives=1,
                                                         keepalives_idle=30, keepalives_interval=10,
                                                         keepalives_count=5)
        except Exception as e:
            print(e)

    def getConnect(self):
        conn = self.connectPool.getconn()
        cursor = conn.cursor()
        return conn, cursor

    def closeConnect(self, conn, cursor):
        cursor.close()
        self.connectPool.putconn(conn)

    def closeAll(self):
        self.connectPool.closeall()

    # 执行增删改
    def execute(self, sql, value=None):
        conn, cursor = self.getConnect()
        try:
            res = cursor.execute(sql, value)
            conn.commit(conn, cursor)
            self.closeConnect(conn, cursor)
            return res
        except Exception as e:
            conn.rollock()
            raise e

    def selectOne(self, sql):
        conn, cursor = self.getConnect()
        cursor.execute(sql)
        result = cursor.fetchone()
        self.closeConnect(conn, cursor)
        return result

    def selectAll(self, sql):
        conn, cursor = self.getConnect()
        cursor.execute(sql)
        result = cursor.fetchall()
        self.closeConnect(conn, cursor)
        return result

postgresql是如何处理死连接

在数据库postgresql中，一个客户端到服务器连接实际上是一个tcp socket连接，tcp连接是虚连接，一方非正常退出（如断电），另一方会继续维持这个连接。

举个例子，一个客户端电脑正常连上服务器后，强行拔掉电源造成人为断电，重新启动电脑，再此连上服务器。用SQL语句select * from pg_stat_activily 查看服务器的所有连接，会发现本客户端的连接除了本次外，断电前的连接还在。因为服务器根本不知道客户端的断电行为，还以为那连接正在空闲状态。

然而这个死连接不会永远存在，2个小时后，服务器上的这个连接会自动切掉，因为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个小时后，服务器才可以自动关掉死连接。在实际应运中，可以自行调整以上参数。

然而，单单依靠服务器以此方法来切掉死连接，是永远不够。假设有一个连接，在运行以下交互式命令中突然断电
begin transaction;
lock table xxx in exclusive mode;
-- 突然断电，这种可能很小，但肯定存在
。。。
commit

由于这个连接还保留着，且这个transaction还没结束（本来上1秒之内的事务，现在变成至少要2个小时），所以这个表的锁一直存在着，导致系统的并发性严重降低。

所以必需有手工杀掉连接的语句来切掉此连接，以释放锁。不幸的是，直到8.3，postgresql还没有此语句，源代码是有pg_terminate_query函数,因为有bug, 被屏蔽掉，必须到8.4才有。

所以只能用操作系统的命令杀掉此连接，或者重启服务器。 在linux下，杀掉此连接的命令是：
kill -s SIGTERM 进程号。