Multi tool use

psycopg2 close connection pool

I'm developing a Flask API, and I have the following code to have a connection pool using Psycopg2. I wonder should I consider to close the connection pool when the program terminates and how should I do this?

@contextmanager
def get_cursor(:
global connection_pool
if not cls.connection_pool:
cls.connection_pool = ThreadedConnectionPool(5, 25, dsn=PoolingWrap.generate_conn_string())

con = cls.connection_pool.getconn()
try:
yield con.cursor(cursor_factory=RealDictCursor)
finally:
cls.connection_pool.putconn(con)


2 Answers
2

I believe that so long as you are closing the transactions in each connection correctly, then you should not have any problem just leaving the global pool.
I think the worst that can happen in that case is some connections on the DB side take a short while to work out that they've been closed on the client side - but this should not be able to cause any data consistency type issues.

However, if you really want to close the connection pool before you exit, one way to do this is to register an atexit function.

atexit

import atexit

@atexit.register
def close_connection_pool():
global connection_pool
connection_pool.close()


Lets talk about context manager first.

Basic context manager is

class File(object):
def __init__(self, file_name, method):
self.file_obj = open(file_name, method)
def __enter__(self):
return self.file_obj
def __exit__(self, type, value, traceback):
self.file_obj.close()

with File('working_file','r') as f:
your code


__enter__ method is called when you and return value will be assign to f.

__enter__

When you exit the with block it call __exit__ method in which you should close your open connection or file or any cleanup you want to do.

__exit__

when contextmanager decorator is used it combines __enter__ and __exit__
in one function. code before yield will be treated as __enter__ yield will be treated as return value from __enter__. Code after yield will be treated as __exit__.

__enter__

__exit__

__enter__

__enter__

__exit__

Basic structure to create context manager using contextmanager decorator is

@contextmanager
def my_context_manager():
<this is your enter code> # similar __enter__ method
try:
yield [value] # similar to return value provided in __enter__ method
<this code execute if no error occur in with block>
except:
<exceptional exit from with-block>
raise # use raise if you want to raise exception that occur in with block


So answer to your question is yes, you should close the connection pool when the program terminates.

@contextmanager
def get_cursor(:
global connection_pool
if not cls.connection_pool:
cls.connection_pool = ThreadedConnectionPool(5, 25, dsn=PoolingWrap.generate_conn_string())
con = cls.connection_pool.getconn()
try:
yield con.cursor(cursor_factory=RealDictCursor)
finally:
cls.connection_pool.putconn(con)
<close your connection here>


cursor

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.