Connection Management

Context Manager Pattern

import psycopg2

# Recommended: Using context manager
with psycopg2.connect(
    host="localhost",
    database="mydb",
    user="postgres",
    password="password"
) as conn:
    with conn.cursor() as cur:
        cur.execute("SELECT version();")
        print(cur.fetchone())
# Connection auto-committed and closed

Manual Connection Handling

conn = None
try:
    conn = psycopg2.connect(
        host="localhost",
        database="mydb",
        user="postgres",
        password="password"
    )

    cur = conn.cursor()
    cur.execute("SELECT * FROM users")
    rows = cur.fetchall()

    for row in rows:
        print(row)

    cur.close()
    conn.commit()

except psycopg2.Error as e:
    print(f"Database error: {e}")
    if conn:
        conn.rollback()

finally:
    if conn:
        conn.close()

Connection Parameters

# Using environment variables (recommended)
import os

conn = psycopg2.connect(
    host=os.getenv("DB_HOST", "localhost"),
    database=os.getenv("DB_NAME"),
    user=os.getenv("DB_USER"),
    password=os.getenv("DB_PASSWORD"),
    port=os.getenv("DB_PORT", 5432),
    connect_timeout=10,  # seconds
    application_name="my_app"
)

Connection States

# Check connection status
print(conn.closed)  # 0 = open, non-zero = closed

# Check transaction status
from psycopg2.extensions import (
    TRANSACTION_STATUS_IDLE,
    TRANSACTION_STATUS_ACTIVE,
    TRANSACTION_STATUS_INTRANS
)

status = conn.get_transaction_status()
print(f"Transaction status: {status}")