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.

import psycopg2

# Establish a connection
conn = psycopg2.connect(
    dbname="yourdbname",
    user="yourusername",
    password="yourpassword",
    host="yourhost",
    port="yourport"
)

# Perform database operations
# ...

# Close the connection
conn.close()

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.

import psycopg2

# Using a context manager to handle connection
with psycopg2.connect(
    dbname="yourdbname",
    user="yourusername",
    password="yourpassword",
    host="yourhost",
    port="yourport"
) as conn:
    # Perform database operations
    # ...
    pass  # Connection is automatically closed here

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.

import psycopg2

conn = psycopg2.connect(
    dbname="yourdbname",
    user="yourusername",
    password="yourpassword",
    host="yourhost",
    port="yourport"
)

try:
    # Create a cursor
    cur = conn.cursor()
    
    # Perform operations using the cursor
    # ...

    # Close the cursor
    cur.close()
finally:
    # Close the connection
    conn.close()

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.

import psycopg2
from psycopg2 import sql

# Database connection parameters
dbname = "yourdbname"
user = "yourusername"
password = "yourpassword"
host = "yourhost"
port = "yourport"

def connect_and_query():
    try:
        # Establish a connection to the database
        conn = psycopg2.connect(
            dbname=dbname,
            user=user,
            password=password,
            host=host,
            port=port
        )
        
        # Create a cursor object using the connection
        with conn.cursor() as cur:
            # Define a simple SQL query
            query = sql.SQL("SELECT version();")
            
            # Execute the query
            cur.execute(query)
            
            # Fetch the result
            result = cur.fetchone()
            print("Database Version:", result[0])
    
    except Exception as e:
        print("An error occurred:", e)
    
    finally:
        # Ensure the connection is closed
        if conn is not None:
            conn.close()
            print("Connection closed.")

# Run the function
connect_and_query()

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.