Python SQL CRUD with Python

Creating the table is a one-off schema step, separate from adding data:

import sqlite3
db = sqlite3.connect("repairs.db")
db.execute("""
    CREATE TABLE IF NOT EXISTS repairs (
        id INTEGER PRIMARY KEY,
        item TEXT NOT NULL,
        status TEXT NOT NULL DEFAULT 'waiting'
    )
""")

Create rows with INSERT. cursor.lastrowid gives the id the database generated, and executemany() inserts several rows in one call:

cur = db.execute("INSERT INTO repairs (item) VALUES (?)", ("Lamp",))
new_id = cur.lastrowid
db.executemany("INSERT INTO repairs (item) VALUES (?)", [("Kettle",), ("Radio",)])
db.commit()

Read with SELECT, naming the columns you need rather than SELECT *, so code doesn't break when columns are added:

row = db.execute("SELECT item, status FROM repairs WHERE id = ?", (new_id,)).fetchone()
waiting = db.execute("SELECT id, item FROM repairs WHERE status = ? ORDER BY id", ("waiting",)).fetchall()

fetchone() returns a tuple or None; fetchall() returns a list of tuples. Set db.row_factory = sqlite3.Row to access columns by name, as in row["item"].

UPDATE and DELETE change every row that matches the WHERE clause. Without one, they affect the whole table:

cur = db.execute("UPDATE repairs SET status = ? WHERE id = ?", ("ready", 1))
print(cur.rowcount)        # 1 if the repair existed, 0 if not

cur = db.execute("DELETE FROM repairs WHERE status = ?", ("collected",))
print(cur.rowcount, "collected repairs removed")
db.commit()

Check rowcount to find out whether anything was actually changed. An update that matches zero rows isn't an error in SQL, so if your code expects to update repair 99 and it doesn't exist, only rowcount == 0 will tell you.

Before running a destructive statement, run the matching SELECT with the same WHERE clause to see which rows it will affect. Many applications avoid deleting altogether and use a "soft delete" instead, setting a deleted_at column, so records can be restored and history is preserved.

A common need is "add this record, or update it if it already exists", often called an upsert. SQLite (3.24 and later) and PostgreSQL support it with ON CONFLICT:

db.execute("""
    INSERT INTO stock (item, quantity) VALUES (?, ?)
    ON CONFLICT(item) DO UPDATE SET quantity = quantity + excluded.quantity
""", ("fuse", 5))

The conflict target, item, must have a UNIQUE or PRIMARY KEY constraint. excluded refers to the values the failed insert tried to add. MySQL's equivalent is INSERT ... ON DUPLICATE KEY UPDATE. Doing it in one statement avoids the race in "check with SELECT, then INSERT or UPDATE", where another process could insert the same item between your two statements.

Every value from outside the program goes in through placeholders, never through string formatting. The placeholder style belongs to the driver:

item = "Lamp'); DROP TABLE repairs; --"
db.execute("INSERT INTO repairs (item) VALUES (?)", (item,))   # stored as plain text

The driver sends the value separately from the SQL, so it can't change the statement's meaning. That's the defence against SQL injection.

Placeholders work only for values. Table names, column names and keywords such as ASC/DESC can't be parameters. If a user chooses the sort column, check it against an allow-list: if column not in {"item", "status"}: raise ValueError. Only then insert it into the SQL text.

A transaction groups statements so they succeed or fail together. sqlite3 opens one automatically before a data-changing statement, and nothing is permanent until commit(). Used as a context manager, a connection commits when the block succeeds and rolls back if it raises:

with db:   # one transaction
    db.execute("UPDATE repairs SET status = ? WHERE id = ?", ("ready", 1))
    db.execute("INSERT INTO notes (repair_id, text) VALUES (?, ?)", (1, "Replaced fuse"))

If the insert fails, the status update is rolled back too, so a repair is never marked ready without its note. Committing after each statement separately could leave the data half-changed after an error.

Note that with db: manages the transaction only; it doesn't close the connection. Call db.close() when finished, or use contextlib.closing(), as explained in the database connections lesson.

Task: implement parameterized create, read, update and delete operations for a disposable repair table.

import sqlite3
from contextlib import closing

def add(db, item):
    return db.execute("INSERT INTO repairs (item, status) VALUES (?, 'waiting')", (item,)).lastrowid

def get(db, repair_id):
    return db.execute("SELECT item, status FROM repairs WHERE id = ?", (repair_id,)).fetchone()

def mark_ready(db, repair_id):
    return db.execute("UPDATE repairs SET status = 'ready' WHERE id = ?", (repair_id,)).rowcount == 1

def remove(db, repair_id):
    return db.execute("DELETE FROM repairs WHERE id = ?", (repair_id,)).rowcount == 1

with closing(sqlite3.connect(":memory:")) as db:
    db.execute("CREATE TABLE repairs (id INTEGER PRIMARY KEY, item TEXT, status TEXT)")
    with db:
        lamp = add(db, "Lamp")
        print(mark_ready(db, lamp), get(db, lamp))   # True ('Lamp', 'ready')
        print(remove(db, lamp), get(db, lamp))       # True None

Each function returns something checkable, so callers know whether the operation found its row. For the worked check, the clause that limits the rows affected by a simple UPDATE is WHERE.

The sqlite3 documentation covers placeholders, transactions and row factories. Continue with database connections and queries and the SQL course. Practise with the Python exercises and Python quiz.