Pylerno
English

SQLite

Store related records and keep SQL separate from user data.

On this page

The concept

SQLite provides an embedded database without a separate database server. CREATE TABLE defines fields and constraints, INSERT stores rows, and SELECT retrieves them. Use ? placeholders and a separate parameter sequence so data values are not interpreted as SQL syntax.

The connection context manager commits on successful completion and rolls back an exceptional transaction; it does not itself close the connection. contextlib.closing handles that separate lifetime. The example uses :memory:, so it leaves no permanent database. ORDER BY makes result order explicit.

Worked example

Extract the example files. From the python_from_zero directory, run the following command in a terminal. On Windows, use py in place of python3.

sh
python3 08_professional_workflow/63_sqlite/lesson.py
python
import sqlite3
from contextlib import closing

with closing(sqlite3.connect(":memory:")) as connection:
    with connection:
        connection.execute(
            "CREATE TABLE tasks (id INTEGER PRIMARY KEY, title TEXT NOT NULL, done INTEGER NOT NULL CHECK(done IN (0, 1)))"
        )
        connection.execute("INSERT INTO tasks (title, done) VALUES (?, ?)", ("Learn Python", 0))
        connection.execute("INSERT INTO tasks (title, done) VALUES (?, ?)", ("Read docs", 1))
    rows = connection.execute(
        "SELECT id, title FROM tasks WHERE done = ? ORDER BY id", (0,)
    ).fetchall()
    for task_id, title in rows:
        print(task_id, title)

Trace the result

  1. The table restricts done to 0 or 1.
  2. Two parameterized inserts occur within one transaction.
  3. The query selects open tasks using a parameter and reads rows in ID order.
text
1 Learn Python
Line-by-line walkthrough

Line 1: import sqlite3

Load sqlite3 (SQLite connections and parameterized SQL). Importing makes these names available; it is not a call to every function in the module.

Line 2: from contextlib import closing

Import closing from contextlib. This supplies resource-lifetime helpers. The caller can now use the imported names directly.

Lines 4–15: with closing(sqlite3.connect(":memory:")) as connection:

Enter the listed context manager(s), run the indented block, and leave each context afterward. The manager defines the cleanup/commit behavior; a database transaction and a file lifetime are not identical contracts.

Lines 5–10: with connection:

Enter the listed context manager(s), run the indented block, and leave each context afterward. The manager defines the cleanup/commit behavior; a database transaction and a file lifetime are not identical contracts.

Lines 6–8: connection.execute(

Call connection.execute to execute one SQL statement with separate parameters when supplied. This call may have an effect even when its return value is not assigned.

Line 9: connection.execute("INSERT INTO tasks (title, done) VALUES (?, ?)", ("Learn Python", 0))

Call connection.execute to execute one SQL statement with separate parameters when supplied. This call may have an effect even when its return value is not assigned.

Line 10: connection.execute("INSERT INTO tasks (title, done) VALUES (?, ?)", ("Read docs", 1))

Call connection.execute to execute one SQL statement with separate parameters when supplied. This call may have an effect even when its return value is not assigned.

Lines 11–13: rows = connection.execute(

Evaluate connection.execute('SELECT id, title FROM tasks WHERE done = ? ORDER BY id', (0,)).fetchall(), then bind or update rows with the result. The call finishes before its return value is bound; a failed call does not produce a successful assignment. Assignment itself does not imply a deep copy.

Lines 14–15: for task_id, title in rows:

Obtain each item from rows and bind it to (task_id, title) before executing the body. A tuple-shaped target unpacks that item. A loop else, if present, runs only when completion does not use break.

Line 15: print(task_id, title)

Evaluate the print arguments from the current state and display them. sep controls separators and end controls the ending when those options are provided. Displaying a value does not return that value from the surrounding function.

Common mistake

Never interpolate untrusted values into SQL with f-strings. Table/column identifiers also need a controlled design rather than unrestricted input.

Check your understanding

A table has rows with quantities 2, 2, 5. What does COUNT(*) return?

Choose one answer

Search documentation

Share this page