my apologies in advance as I see there are a bunch of answers that are close to what I need, but no cigar - I'm a C# developer mostly, but am stuck on what should be basic boilerplate setup.
I need a small PostGRE SQL client for relatively basic queries (basic set or non-complex selects), but I want to re-use the connection in the proper manner, with my current working setup throwing a lot of errors in regards to max connections, and potentially even burning through those connections and no longer working until I reset the function - here is my Database class (with all logging, comments and other code stripped out, and one tab level removed for easier reading):
def __get_client(self) -> sqlalchemy.engine.base.Engine:
connector = Connector()
def getconn() -> pg8000.dbapi.Connection:
conn: pg8000.dbapi.Connection = connector.connect(
{project:region:instance}
"pg8000",
user={user},
password={password},
db={db},
ip_type=IPTypes.PUBLIC,
)
return conn
pool = sqlalchemy.create_engine(
"postgresql+pg8000://",
creator=getconn,
pool_size=5,
max_overflow=2,
pool_timeout=30,
pool_recycle=1800,
)
return pool
def get_value(self, statement) -> str:
try:
with self.__get_client().connect() as connection:
result = connection.execute(statement).fetchall()
except Exception as e:
{Logging and error handling goes here}
return result
As I mentioned, this works, I get my results, but this is used for high frequency operations, with over 120 calls to the function via PubSub at a time, and this seems to cause the connections to run out - I had the exact error message prepped but I lost it and am so tired currently I've fallen asleep multiple times just attempting to write this sentence, but the issue is just that the connections start failing, it starts saying the max has been used and simple backoff isn't enough.
In order to deal with that issue, I added the following:
def __init__(self, ...):
...
self._engine = None <== Added to constructor
...
...
def __connect(self): <== Added this method
self._log.debug("Starting")
connection = None
@contextlib.contextmanager
def connect():
nonlocal connection
if connection is None:
if self._engine is None:
self._engine = self.__get_engine()
connection = self._engine.connect()
with connection:
with connection.begin():
yield connection
else:
yield connection
return connect
...
def get_values(self, statement, *, retry: int = 0) -> str:
try:
with self.__connect() as connection: <== Use new method
And now the issue is much more simple, TypeError: 'function' object does not support the context manager protocol - obviously related to the @contextlib.contextmanager being on the inner function instead of the outer, but before I go ahead and tackle this next bit - does anyone have or have a link to an example of how properly to set up a basic pg8000 + SQLAlchemy connection for ongoing re-use? Even better if it's the latest version as my code was working with 1.X.
Again I apologise if this seems similar to existing, but I did look and they don't quite fit.
