01 | DATA THAT SURVIVES THE PROGRAM

A list exists in the running program’s memory. After the process ends, it is gone unless saved. A database stores organised records and supplies operations for retrieving and changing them.

SQLite stores a database in a file and runs inside your application process, so this lesson does not require a separate database server. Python supplies the sqlite3 module in its standard library.

A table has columns describing fields and rows describing records. A primary key identifies one row. SQL is the language used here to define tables, ask questions and change stored data. It is separate from Python syntax.

02 | BEGIN WITH A TABLE YOU CAN PICTURE

A reading table can contain id, title and pages. The id is an INTEGER PRIMARY KEY, title is required text and pages must be a positive integer under the Python input policy. The database CHECK below additionally rejects values whose comparison fails its condition.

SQLite ordinarily uses type affinity rather than enforcing every declared type exactly. Therefore a column declared INTEGER is not, by itself, a complete exact-int validation rule. Use Python checks for this course’s inputs and database constraints for shared invariants.

The example uses :memory: for a disposable demonstration. Replacing that with reading.sqlite3 opens or creates a persistent file at a resolved path. Do not confuse a demonstration’s memory database with the real planner’s stored file.

03 | CONNECT, CREATE, INSERT AND READ

connect returns a connection representing access to the database. execute runs a SQL statement and returns a cursor. fetchall retrieves the remaining result rows as a Python list. By default each row is a tuple.

Read the CREATE statement as a table definition. Read INSERT as creating one record. The question marks are placeholders: the tuple supplies the title and page count as values, not extra SQL.

ORDER BY makes the requested display order explicit. Without it, you must not rely on the order rows happen to appear.

from contextlib import closing
import sqlite3

with closing(sqlite3.connect(":memory:", isolation_level="DEFERRED")) as con:
    with con:
        con.execute("CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT NOT NULL, pages INTEGER NOT NULL CHECK(pages > 0))")
        con.execute("INSERT INTO books (title, pages) VALUES (?, ?)", ("Python Adventures", 120))
    rows = con.execute("SELECT id, title, pages FROM books ORDER BY id").fetchall()
    print(rows)

[(1, 'Python Adventures', 120)]

04 | WHAT THE TWO WITH BLOCKS DO

The outer closing block closes the connection when the block exits. A sqlite3 connection’s own with block manages a transaction, but does not close the connection. A transaction groups changes that should succeed or fail together.

This example explicitly requests DEFERRED legacy transaction handling so its behaviour does not silently depend on changing defaults. The inner block commits a pending transaction on success and rolls it back when an exception escapes. Each database operation in the project follows a documented transaction policy.

DDL such as CREATE TABLE does not necessarily start an implicit transaction under this policy. To group schema statements atomically, the planner explicitly issues BEGIN first. The smaller snippet commits its inserted record; it does not claim the CREATE was automatically part of that insertion transaction.

05 | PARAMETERISE VALUES, NEVER BUILD THEM INTO SQL

Suppose a title contains an apostrophe, such as Noor’s Notes. Joining that text into SQL can break quotation structure; malicious text may change what an assembled statement means. Bound parameters keep each input a data value.

For a single parameter, write (title,) with the trailing comma to create a one-item tuple. Without the comma, parentheses alone do not turn a string into a tuple. Parameters do not add the surrounding SQL quotes yourself.

Placeholders stand for values, not table names, column names or ASC/DESC keywords. Choose such structure from a fixed allowlist when needed. Input validation enforces rules such as non-blank text; it does not replace SQL parameterisation.

PYTHON | VALUE PLACEHOLDER EXAMPLE

rows = con.execute(
    "SELECT id, title FROM books WHERE title = ? ORDER BY id",
    (title,),
).fetchall()

06 | FILTER, UPDATE AND DELETE WITH INTENTION

WHERE restricts affected rows. An UPDATE without a WHERE clause can modify every row. A DELETE without a WHERE clause can remove every row. First express exactly which primary key or rule selects the intended records.

Use the same bound-parameter technique for INSERT, SELECT, UPDATE and DELETE. rowcount can help check the number of affected rows, but its meaning belongs to the operation; do not use it as a reliable SELECT result count.

Compare the before and after report, and commit only the changes you intend. The sample works on a disposable memory database so you can experiment without deleting a real collection.

from contextlib import closing
import sqlite3

with closing(sqlite3.connect(":memory:", isolation_level="DEFERRED")) as con:
    with con:
        con.execute("CREATE TABLE tasks (id INTEGER PRIMARY KEY, title TEXT NOT NULL, done INTEGER NOT NULL CHECK(done IN (0, 1)))")
        con.executemany("INSERT INTO tasks (title, done) VALUES (?, ?)", [("Read", 0), ("Test", 0)])
        cursor = con.execute("UPDATE tasks SET done = ? WHERE id = ?", (1, 2))
        print("Updated:", cursor.rowcount)
    print(con.execute("SELECT title FROM tasks WHERE done = ? ORDER BY id", (0,)).fetchall())

Updated: 1
[('Read',)]

07 | RELATED TABLES AND FOREIGN KEYS

A project can have many tasks. Store the project’s name once in projects, then place project_id in each task row. A foreign key describes that relationship to the project primary key.

Enable PRAGMA foreign_keys = ON for each SQLite connection before starting its transaction. Do not assume a foreign-key declaration alone has enabled enforcement in every connection.

JOIN combines related rows for a report. SELECT t.title, p.name joins each task t to project p where t.project_id = p.id. The aliases shorten column names and remove ambiguity. A normal inner join includes matching records only; LEFT JOIN can retain projects with no tasks.

08 | TRY A TITLE LOOKUP

Try an apostrophe or SQL-looking text in this fixed lookup model. Matching remains exact and the query structure remains unchanged. The browser does not open a SQLite database; run the Python examples to test real parameter binding.

09 | WHEN AN OPERATION FAILS

A uniqueness or foreign-key violation raises sqlite3.IntegrityError. Catch it outside the transaction context when you want rejection to roll back the transaction. Catching an error inside and continuing needs careful explicit transaction decisions.

Do not respond to a corrupt or unavailable database by silently replacing it with an empty one. Report the problem and retain the original file for recovery. Parameterisation prevents values changing SQL structure; it does not encrypt records or provide user authentication.

An explicit multi-change operation should begin its transaction, perform every required step and either commit all or roll back. Test that rejection leaves no partial records behind.

10 | USE A DELIBERATE FILE LOCATION

A relative database name uses the current working directory. Running from another folder could accidentally create another database with the same name. The project uses Path(__file__).resolve().parent / "planner.sqlite3" to place its file beside app.py.

Back up important data separately from source history. For this small app, close it and copy the database file before destructive experiments. Larger live applications need a proper database-aware backup approach.

CREATE TABLE IF NOT EXISTS creates missing tables; it does not migrate an old table to a new schema. A changed schema needs a deliberate migration and backup plan, rather than a misleading promise that startup will update everything.

PUT YOUR SKILLS TO WORK

Use the Section 7 challenges to practise these ideas independently. Work in your Python editor, predict results and keep a short test record.