Database Connections

A database connection is the active link between a Python program and a database, established using credentials and kept open only as long as needed to run queries - managing connections properly is important for both performance and reliability.

Opening a Connection

import psycopg2

conn = psycopg2.connect(
    host="localhost",
    dbname="uncodemy",
    user="analyst",
    password="secure_password"
)

Using a Context Manager

Wrapping a connection in a with block ensures it's automatically closed even if an error occurs - a safer pattern than manually calling close():

with psycopg2.connect(host="localhost", dbname="uncodemy",
                       user="analyst", password="secure_password") as conn:
    with conn.cursor() as cursor:
        cursor.execute("SELECT * FROM customers LIMIT 5;")
        for row in cursor.fetchall():
            print(row)

Connection Pooling

For applications that run many queries, opening a fresh connection every time is inefficient. A connection pool keeps a set of ready-to-use connections open and reuses them, reducing overhead - libraries like SQLAlchemy handle this automatically.