PostgreSQL with Python

Python accesses Postgres via psycopg (v3) or async drivers. Always parameterize queries—never interpolate user input into SQL strings.

psycopg basics

import psycopg

with psycopg.connect('postgresql://user:pass@localhost/practice') as conn:
    with conn.cursor() as cur:
        cur.execute(
            'SELECT id, email FROM customers WHERE id = %s',
            (customer_id,),
        )
        row = cur.fetchone()

Practice: Use local or staging environments only for backup/restore and pooling experiments.

Context managers and transactions

with psycopg.connect(conninfo) as conn:
    with conn.transaction():
        conn.execute('UPDATE accounts SET balance = balance - %s WHERE id = %s', (100, 1))
        conn.execute('UPDATE accounts SET balance = balance + %s WHERE id = %s', (100, 2))

SQLAlchemy and pandas

SQLAlchemy ORM/Core sits above psycopg for larger apps. pandas read_sql feeds analytics pipelines—still respect least-privilege DB roles.

Important interview questions and answers

1. Q: %s placeholder?
   A: psycopg adapts Python values safely—driver handles escaping.
2. Q: asyncpg?
   A: Popular asyncio driver alternative to sync psycopg.

Self-check

1. How do you prevent SQL injection in psycopg?
2. What manages commit/rollback in the transaction example?

Pitfall: Never interpolate user input into SQL—always bind %s parameters.