How to Close Connections in psycopg2 using Python

PostgreSQL database connection in psycopg2 is somewhat of a session with the database. When the connection is created it makes a path through which Python application is able to communicate with the database. This connection enables you to run a command in SQL, perform one or more operations that are known as transactions, and fetch data. Every connection requires certain resources at the client and the server end, therefore, it becomes obligatory to deal with connections clinically.

Closing Connections

1. Explicitly Close Connections

It is recommended that the connection should be closed using the close() method on the connection object. This method unc frees the resources linked with the connection and closes a session with the used database.

2. Use Context Managers

The connections may be safely and properly handled using Python’s with statement from the context manager package. Closing the connection is done automatically when the block is done or an exception occurs within it.

3. Close Cursors Before Connections

closing the connection forces you to close multiple cursors if you were using them before. Although committing or closing a connection will kill all cursors that were opened for that connection, it is advised that they be closed.

4. Handle Exceptions Gracefully

Make sure to close the connections correctly to avoid open connections left in Windows, Linux and Unix. Try-finally blocks or context managers should be employed while executing operations on the databases, because they assist in the closing of connections even when an error is encountered.

Example Code

1. Connection Setup: The psycopg2. connect() function is used for connecting establishment to the PostgreSQL database through the given parameters.
2. Cursor Creation: The with conn. cursor() as cur line generates a cursor which is needed to perform SQL statements. The context manager guarantees that the cursor will be closed each time the block is exited.
3. Query Execution: The cur. The execute(query) method in the code performs the SQL operation and cur. The query is executed and fetchone() gets the result of the query.
4. Exception Handling: If there are any mistakes with the connection or the general query they are displayed.
5. Connection Closure: The finally block is used to ensure that the connection is closed whether there is an error passes the condition or not. Thus, to guard against resource leaks, resource sharing is greatly encouraged.

Output

Assuming your PostgreSQL server is running and the connection details are correct, the output of the code will be something like this:Assuming your PostgreSQL server is running and the connection details are correct, the output of the code will be something like this:

Database Version: PostgreSQL 13.4 (Ubuntu 13.4-1.pgdg20.04+1)
Connection closed.