PCPP2: Python-SQL and Python-NoSQL database access

Python database access: SQL and NoSQL

The exam expects DB-API 2.0 fluency, safe parameter binding, awareness of ORMs (SQLAlchemy-style sessions), and basic document and key-value access.

DB-API 2.0. conn = sqlite3.connect(path); cur = conn.cursor(); cur.execute(sql, params); cur.fetchone()/fetchmany(n)/fetchall(); conn.commit() or conn.rollback(); conn.close(). executemany() handles bulk inserts. Never build SQL with f-strings or % - pass a parameter sequence and let the driver quote and escape. sqlite3 uses ? and :name placeholders; psycopg2 uses %s (a literal % must be doubled). with conn: is a transaction block that commits or rolls back - it does not close the connection.

ORM. Declare models against a declarative_base, create a sessionmaker, then session.add(obj), session.commit(), session.query(Model).filter(...). Relationships default to lazy loading, which produces the N+1 query problem unless you eager-load with joinedload.

NoSQL. MongoDB via pymongo: MongoClient(uri), db.collection.insert_one(doc), find_one({"_id": ...}), update_one(filter, {"$set": {...}}), delete_one(...). Redis is key-value with optional TTLs. NoSQL trades joins and rigid schemas for denormalised documents and often eventual consistency.

Worked example

with sqlite3.connect("app.db") as conn:
    conn.execute("INSERT INTO users(name, age) VALUES (?, ?)",
                 (name, age))

The tuple is bound by the driver, so a name containing ' or -- is stored literally instead of altering the statement. The block commits on success; an exception rolls back and re-raises.

Traps

Forgetting commit() and losing writes when the connection closes.
Assuming with conn: closes the connection - it only manages the transaction.
fetchall() on a huge result set instead of iterating the cursor.
Sharing one cursor across threads.
Assuming MongoDB writes are strongly consistent across replicas by default.