SQLite Basics: Reuse Queries with Views

A view gives a query a reusable name. Use one for a repeated filter or report, then select from it like a table. A normal view saves the query definition, not a frozen copy of the result.

Prepare a scratch directory

Tested on Linux with Python 3.12.14 and SQLite 3.53.1. You need Bash, Python 3 with SQLite support, a text editor, and mktemp, rm, and rmdir. No additional Python packages are needed. Keep the same terminal open and save files as UTF-8.

start_dir="$PWD"
work_dir="$(mktemp -d)" || exit 1
cd -- "$work_dir" || exit 1

The standalone SQLite CLI was unavailable on the test machine. Save this runner as run-sql.py. Each invocation uses a fresh in-memory database and prints pipe-separated rows, displaying missing values as NULL. These toy databases disappear when the connection closes. Put each statement’s ending semicolon at the end of a line, as shown; this is a teaching runner, not a general SQL shell or CSV exporter.

from pathlib import Path
import sqlite3
import sys

connection = sqlite3.connect(":memory:", isolation_level=None)
try:
    statement = ""
    for line in Path(sys.argv[1]).read_text(encoding="utf-8").splitlines(True):
        statement += line
        if sqlite3.complete_statement(statement):
            cursor = connection.execute(statement)
            for row in cursor:
                print("|".join("NULL" if value is None else str(value) for value in row))
            statement = ""
    if statement.strip():
        raise ValueError("Unfinished SQL statement")
finally:
    connection.close()

1. Name a frequently used filter

Save as active-projects.sql:

CREATE TABLE projects (id INTEGER PRIMARY KEY, name TEXT, status TEXT);
INSERT INTO projects VALUES (1, 'Docs', 'active'), (2, 'Demo', 'paused'), (3, 'API', 'active');
CREATE VIEW active_projects (id, name) AS
SELECT id, name FROM projects WHERE status = 'active';
SELECT id, name FROM active_projects WHERE id >= 2 ORDER BY id;

Run:

python3 run-sql.py active-projects.sql

Output:

3|API

The view contains the shared active-project filter; the outer query adds its own condition. Naming the view columns explicitly makes the interface clear.

2. Read updated totals through the same view

Save as sales-report.sql:

CREATE TABLE sales (team TEXT, amount INTEGER);
INSERT INTO sales VALUES ('East', 20), ('East', 30), ('West', 40);
CREATE VIEW team_totals (team, total) AS
SELECT team, SUM(amount) FROM sales GROUP BY team;
SELECT 'Before';
SELECT team, total FROM team_totals ORDER BY team;
INSERT INTO sales VALUES ('East', 10);
SELECT 'After';
SELECT team, total FROM team_totals ORDER BY team;

Run:

python3 run-sql.py sales-report.sql

Output:

Before
East|50
West|40
After
East|60
West|40

The second read reflects the new base-table row. The view does not need a refresh command. SQLite views are read-only unless you implement writes with INSTEAD OF triggers; these examples update the base table directly.

3. Create and remove a temporary helper

Save as temporary-view.sql:

CREATE TABLE jobs (id INTEGER PRIMARY KEY, status TEXT);
INSERT INTO jobs VALUES (1, 'queued'), (2, 'done'), (3, 'queued');
CREATE TEMP VIEW queued_jobs (id) AS
SELECT id FROM jobs WHERE status = 'queued';
SELECT id FROM queued_jobs ORDER BY id;
DROP VIEW queued_jobs;
SELECT COUNT(*) AS remaining_jobs FROM jobs;

Run:

python3 run-sql.py temporary-view.sql

Output:

1
3
3

The first two lines are queued job IDs; the last line counts all three base-table rows after DROP VIEW. Removing the view leaves those rows intact. A TEMP view is visible only on its creating connection and disappears when that connection closes.

All these examples use in-memory databases, so even their non-TEMP views disappear at the end of each run. In a file-backed database, a normal view definition persists. Keep ORDER BY in the query that displays the result. See SQLite’s CREATE VIEW and DROP VIEW references.

Clean up

In the same terminal, remove only these example files and the empty scratch directory:

rm -- run-sql.py active-projects.sql sales-report.sql temporary-view.sql
cd -- "$start_dir" || exit 1
rmdir -- "$work_dir"
unset start_dir work_dir

Acknowledgements

This post was created with assistance from AI.