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.
python3 08_professional_workflow/63_sqlite/lesson.pyimport 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
- The table restricts done to 0 or 1.
- Two parameterized inserts occur within one transaction.
- The query selects open tasks using a parameter and reads rows in ID order.
1 Learn PythonLine-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.