python - 连接池耗尽 psycopg2

Question

我对 Postgresql psycopg2 有疑问。我收到错误:

error in executing with exception: connection pool exhausted

我的代码:

from psycopg2 import pool
import pandas.io.sql as sqlio
import pandas as pd
db = pool.ThreadedConnectionPool(5, 100,host=POSTGRES['host'],
database=POSTGRES['database'],user=POSTGRES['username'],
password=POSTGRES['password'],port=POSTGRES['port'])


try:
    sql = "select * from role"  
    data = sqlio.read_sql_query(sql, db.getconn())
    return data.to_json(orient='records')
except Exception as e:
    print "error in executing with exception: ", e
    return pd.DataFrame({'empty' : []})

这个请求应该只返回 5 行，但我得到了这个错误。

你知道我为什么会收到这个错误吗？

我的 Postgresql 数据库(中型实例)部署在公共(public)云上。

提前致谢

Answer 1

看起来您需要在某个时候将连接返回到池中，请参阅:

http://initd.org/psycopg/docs/pool.html#psycopg2.pool.AbstractConnectionPool.putconn

即像这样的东西:

sql = "select * from role"  
try:
    conn = db.getconn()
    try:
        data = sqlio.read_sql_query(sql, conn)
    finally:
        pool.putconn(conn)
    return data.to_json(orient='records')
except Exception as e:
    print "error in executing with exception: ", e
    return pd.DataFrame({'empty' : []})