Python + SQL (psycopg2)

Connect Python to PostgreSQL safely — connections, cursors, and parameterized queries

Knowledge0%

Learn & Drill

Fluency0%

Drill & Speed

Retention0%

Mastery & Review

Confidence0%

All modes

Practice

Free tier: read the explanation here. Upgrade to Pro for Drills, Speed challenges & Mastery badges.

Upgrade

Knowledge

Fluency

Retention

Knowledge Debt detected

You can study this freely — but your score may plateau if these foundations have gaps. The Mastery badge requires them to be solid.

Python Syntax0%

Removing Duplicates with DISTINCT0%

Explanation

psycopg2 is the standard Python library for connecting to PostgreSQL.

Connect, get a cursor, execute, fetch:

python import psycopg2 conn = psycopg2.connect( host="localhost", database="mydb", user="postgres", password="secret" ) cur = conn.cursor() cur.execute("SELECT * FROM claims WHERE status = 'pending'") rows = cur.fetchall() # list of tuples, one per row cur.close() conn.close()

fetchall vs fetchone vs fetchmany:

fetchall() — returns ALL matching rows as a list of tuples (careful with huge result sets)
fetchone() — returns the next single row (or None if no more rows)
fetchmany(n) — returns the next n rows

Parameterized queries — ALWAYS use %s, NEVER f-strings:

python patient_id = 42 status = "pending" # SAFE — psycopg2 escapes the values for you cur.execute( "SELECT * FROM claims WHERE patient_id = %s AND status = %s", (patient_id, status) ) # DANGEROUS — SQL injection risk cur.execute(f"SELECT * FROM claims WHERE patient_id = {patient_id}")

If status came from user input and someone passed "pending' OR '1'='1", the f-string version would let them read the entire table. The %s placeholder is not Python string formatting — psycopg2 sends the value separately and the database treats it strictly as data, never as SQL code.

INSERT and commit:

python cur.execute( "INSERT INTO claims (patient_id, amount, status) VALUES (%s, %s, %s)", (42, 1500.00, "pending") ) conn.commit() # required for INSERT/UPDATE/DELETE to persist

SELECT queries don't need commit() — they don't change data. INSERT, UPDATE, and DELETE do.

Context managers — automatically close connections/cursors, even on error: ``python with psycopg2.connect(host="localhost", database="mydb", user="postgres", password="secret") as conn: with conn.cursor() as cur: cur.execute("SELECT * FROM claims WHERE patient_id = %s", (42,)) rows = cur.fetchall() # connection automatically committed (on success) and closed

Examples

Safe parameterized query

The %s placeholders are filled in by psycopg2 itself — values are always treated as data, never executable SQL.

import psycopg2

conn = psycopg2.connect(host="localhost", database="mydb", user="postgres", password="secret")
cur = conn.cursor()

patient_id = 42
cur.execute("SELECT * FROM claims WHERE patient_id = %s AND status = %s", (patient_id, "pending"))
rows = cur.fetchall()

for row in rows:
    print(row)

cur.close()
conn.close()

INSERT with commit

commit() makes the change permanent. Forgetting it is a common bug — the insert appears to "disappear".

cur.execute(
    "INSERT INTO claims (patient_id, amount, status) VALUES (%s, %s, %s)",
    (42, 1500.00, "pending")
)
conn.commit()  # without this, the insert is rolled back when the connection closes

How well did you understand this?