I have an issue with Postgresql psycopg2. I receive the error :
error in executing with exception: connection pool exhausted
My code :
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' : []})
and this request should return only 5 rows, but i get this error.
Do you have any idea why i get this error ?
My Postgresql Database (medium instance) is deployed on a public cloud.
Thank you in advance
Thread and process safetyThe Psycopg module and the connection objects are thread-safe: many threads can access the same database either using separate sessions and creating a connection per thread or using the same connection and creating separate cursors. In DB API 2.0 parlance, Psycopg is level 2 thread safe.
Prerequisites. The current psycopg2 implementation supports: Python versions from 3.6 to 3.10. PostgreSQL server versions from 7.4 to 15.
The connect() function starts a new database session and returns a connection class instance. We can construct a new cursor to perform any SQL statements by putting the connection object to use. Syntax: psycopg2.connect(database=”dbname”, user='postgres', password=passwords, host=local_host, port= port_number)
Psycopg2 is a PostgreSQL database driver, it is used to perform operations on PostgreSQL using python, it is designed for multi-threaded applications. SQL queries are executed with psycopg2 with the help of the execute() method. It is used to Execute a database operation query or command.
it would appear you need to return the connection back to the pool at some point, see:
http://initd.org/psycopg/docs/pool.html#psycopg2.pool.AbstractConnectionPool.putconn
i.e. something like:
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' : []})
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With