A useful first database exercise should teach more than how to insert a row. It should also show what happens when a value contains an apostrophe, when a write fails, and when the connection closes.

Here is a small, self-contained notes example. It runs in memory, so it does not create a database file or alter an existing one.

import sqlite3 from contextlib import closing with closing(sqlite3.connect(":memory:")) as db: db.execute(""" CREATE TABLE notes ( id INTEGER PRIMARY KEY, title TEXT NOT NULL, done INTEGER NOT NULL DEFAULT 0 CHECK (done IN (0, 1)) ) """) with db: db.executemany( "INSERT INTO notes (title) VALUES (?)", [("Read about SELECT",), ("Review O'Reilly example",)], ) db.execute( "UPDATE notes SET done = ? WHERE id = ?", (1, 1) ) pending = db.execute( "SELECT id, title FROM notes WHERE done = ? ORDER BY id", (0,), ).fetchall() print(pending) try: with db: db.execute( "INSERT INTO notes (title) VALUES (?)", ("Temporary note",), ) db.execute( "INSERT INTO notes (title, done) VALUES (?, ?)", ("Invalid note", 2), ) except sqlite3.IntegrityError: print("Rolled back both inserts") print(db.execute("SELECT COUNT(*) FROM notes").fetchone()[0]) 

The example was executed before publication and produced:

[(2, "Review O'Reilly example")] Rolled back both inserts 2 

What to notice

Values are separate from SQL. The question marks bind values instead of building SQL with string concatenation. The apostrophe in the second title is ordinary data. The comma in (0,) makes it a one-item tuple. Placeholders are for values, not arbitrary table or column names.

A failed batch should not leave half the work behind. The second transaction inserts a temporary note, then violates the done constraint. The exception leaves the with db block, triggering rollback. The final count is still two. Catching the exception outside that block matters.

Transaction handling and connection cleanup are different jobs. Here, with db manages commit/rollback; closing(...) closes the connection. This example uses sqlite3's default legacy transaction mode; changing connection transaction settings can change those semantics.

Try it yourself

  1. Change the invalid done value from 2 to 1. What count do you expect?
  2. Add a query that returns completed notes only.
  3. Add a DELETE ... WHERE id = ? operation inside a transaction, then inspect the remaining rows.

Keep the in-memory database while experimenting. If you later switch to a file, plan how the table is created and how repeated runs should behave rather than blindly replaying inserts.

Reference: Python's sqlite3 documentation.

Disclosure: I run Howcodex, an English-language IT tutorial site. Its Python SQLite tutorial collection is related reading. This post is a standalone example, prepared with AI assistance; the code and output above were checked by execution.