SQL from Python

psycopg2: the direct driver

import psycopg2

conn = psycopg2.connect(host="localhost", dbname="mydb", user="alice", password="...")
cur = conn.cursor()
cur.execute("SELECT id, name FROM users WHERE active = %s", (True,))
rows = cur.fetchall()
conn.commit()   # required for any write — psycopg2 opens an implicit transaction per connection
cur.close()
conn.close()

psycopg2 (or its newer async-friendly sibling, psycopg3) is the standard low-level PostgreSQL driver for Python — almost everything else, including SQLAlchemy, uses one of these underneath.

parameterized queries: the actual SQL injection fix

# NEVER do this — string formatting builds SQL injection directly into the query
cur.execute(f"SELECT * FROM users WHERE name = '{user_input}'")
# if user_input is:  ' OR '1'='1
# the query becomes: SELECT * FROM users WHERE name = '' OR '1'='1'  — returns every row

# ALWAYS do this — the driver sends the value separately from the SQL text
cur.execute("SELECT * FROM users WHERE name = %s", (user_input,))
# user_input is never interpreted as SQL, no matter what it contains

This isn't a best practice with exceptions — it's the actual mechanism that prevents SQL injection. The driver sends the query text and the parameter values as separate messages to the database; the parameter can never be reinterpreted as part of the SQL syntax, regardless of its contents. String formatting/concatenation of untrusted input into a query is the vulnerability, full stop.

SQLAlchemy Core vs. the ORM

# Core: write SQL-shaped Python, close to the metal
from sqlalchemy import create_engine, text
engine = create_engine("postgresql+psycopg2://alice:...@localhost/mydb")
with engine.connect() as conn:
    result = conn.execute(text("SELECT id, name FROM users WHERE active = :active"), {"active": True})

# ORM: map rows to objects
from sqlalchemy.orm import Session
from models import User
with Session(engine) as session:
    users = session.query(User).filter(User.active == True).all()

	Core	ORM
Mental model	SQL, expressed as Python objects	Python objects, backed by SQL
Best for	reports, bulk operations, anything performance-sensitive where you want to see exactly what SQL runs	typical CRUD application code, where object identity and relationships matter more than query-shape control
Common pitfall	—	the N+1 problem — see Query Optimization

connection pooling

Opening a new PostgreSQL connection is relatively expensive (process fork + auth + setup), and each connection holds real server-side memory whether idle or busy — so a web app shouldn't open one per request. Two layers commonly solve this:

Layer	What it does
SQLAlchemy's built-in pool	reuses connections within a single application process
PgBouncer	an external pooler sitting between many application processes/servers and PostgreSQL, multiplexing a large number of client connections onto a much smaller number of actual database connections

PgBouncer matters especially with many application server processes/containers (e.g. one connection pool per process, multiplied across dozens of pods) — without it, it's easy to exceed PostgreSQL's max_connections purely from pool overhead, long before real query load is the bottleneck. In transaction pooling mode specifically, session-level features like SET and advisory locks (see Locking & Concurrency) don't behave the same as on a direct connection — worth checking before assuming PgBouncer is a drop-in, zero-behavior-change layer.

where to go from here

Python — the language side of everything on this page.
Query Optimization — the N+1 problem in more depth.
Locking & Concurrency — SELECT FOR UPDATE from application code.