CODEWITHSUNDEEP
pythonpsycopg2
flybonzai
flybonzai

Reputation: 3941

Best practice for re-using a psycopg2 connection in a class?

Say I have a class to create Redshift object dependencies. I want to create a single connection, and then re-use it for many different transactions.

Should I create it in the __init__ function, and then set the self.conn.close() in the __del__ statement to mimic the with or try/finally model?

EDIT: Here is what I came up with:

class DatabaseConn:
    def __init__(self, env: DBEnvironment = DBEnvironment.PROD):
        """
        A database connection that can be safely instantiated once, and then 
        passed around inside a class or between functions.

        :param env: The environment to connect to, choices are `DEV` and 
         `PROD`. 
        """
        self._conn = ppg2.connect(**env.value)

    def __del__(self):
        self._conn.close()

    def execute(
            self,
            query_or_stmt: str,
            has_res: bool = True) -> Optional[List[Tuple[Any]]]:
        """
        Creates a new cursor object, and executes the query/statement.  If 
        `has_res` is `True`, then it returns the list of tuple results.

        :param query_or_stmt: The query or statement to run.
        :param has_res: Whether or not results should be returned.

        :return: If `has_res` is `True`, then a list of tuples. 
        """
        cur = self._conn.cursor()
        cur.execute(query_or_stmt)
        if has_res:
            return cur.fetchall()

    def return_cursor(self):
        """
        :return: A psycopg2 cursor. 
        """
        return self._conn.cursor()

Upvotes: 0

Views: 2491

Answers (1)

polo
polo

Reputation: 1452

I would recommend avoiding del as a destructor since it's calling pattern is nondeterministic and it is not guaranteed to be called at all.

If you want to get a with resource as bounded var behaviour, you can do that by using the contextlib module. 

from contextlib import contextmanager

@contextmanager
def tag(name):
    print("<%s>" % name)
    yield
    print("</%s>" % name)

>>> with tag("h1"):
...    print("foo")
...
<h1>
foo
</h1>

this example come from the python docs at: https://docs.python.org/3/library/contextlib.html

The disadvantage of this method is that the context of your resource needs to be coded.

An alternative is to write a real destructor that will be called when the connections reference counter hits 0. This is pretty similar to your original idea of using __del__ but to avoid the many of the issues with using __del__ directly, use weakref.finalize.

Upvotes: 1

Related Questions