psycopg2 Fundamentals — Safe Connections & Parameterised Queries

psycopg2 is the standard PostgreSQL adapter for Python. This lesson covers the right way to connect, execute queries, and pass parameters — and why string formatting is dangerous.

import psycopg2
from psycopg2.extras import RealDictCursor
import os

# ── Connecting ────────────────────────────────────────────────────
# Option 1: keyword arguments
conn = psycopg2.connect(
    host="127.0.0.1",
    dbname="shared_db",
    user="myuser",
    password="mypassword",
    connect_timeout=5,
)

# Option 2: connection string (DSN) — easier to read from env
conn = psycopg2.connect(os.environ["DATABASE_URL"])

# Option 3: context manager (auto-closes)
with psycopg2.connect(host="127.0.0.1", dbname="shared_db",
                      user="myuser", password="mypassword") as conn:
    with conn.cursor() as cur:
        cur.execute("SELECT NOW()")
        print(cur.fetchone())

# ── Cursors ───────────────────────────────────────────────────────
cur = conn.cursor()                        # returns tuples
cur = conn.cursor(cursor_factory=RealDictCursor)  # returns dicts

cur.execute("SELECT id, username FROM tw_users LIMIT 5")
rows = cur.fetchall()
for row in rows:
    print(row["username"])   # dict access with RealDictCursor

# ── Parameterised Queries — ALWAYS do this ────────────────────────
username = "user_00001"

# ❌ NEVER do this — SQL injection risk
query = "SELECT * FROM tw_users WHERE username = '" + username + "'"

# ✅ Correct: pass parameters as a tuple (second argument)
cur.execute(
    "SELECT * FROM tw_users WHERE username = %s",
    (username,)   # note the trailing comma — it must be a tuple
)

# Multiple parameters
cur.execute("""
    SELECT * FROM tw_tweets
    WHERE user_id = %s
      AND created_at >= %s
      AND like_count > %s
""", (42, "2024-01-01", 100))

# Named parameters with a dict
cur.execute("""
    SELECT * FROM tw_users
    WHERE verified = %(verified)s
      AND follower_count > %(min_followers)s
""", {"verified": True, "min_followers": 10000})