SQLite Basics: Open an Existing Database Read-Only with a File URI

A reporting tool should fail if its database path is wrong and should not modify its input. Opening an existing file with mode=ro provides those connection-level properties. Construct the URI from a path so spaces, question marks, and other special filename characters are escaped correctly.

Prepare a scratch directory

Tested on Linux with CPython 3.12.14 and SQLite 3.53.1. You need Python 3.11 or newer with SQLite support, Bash, a text editor, and standard mktemp, rm, and rmdir utilities. No third-party packages are required. Run Python without -O so the checks remain enabled.

Keep the same terminal open. Stop if setup fails, before saving the source:

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

Run the example

Save this as open-read-only-report.py:

from pathlib import Path
import sqlite3
from tempfile import TemporaryDirectory

with TemporaryDirectory() as directory:
    database = Path(directory) / "report ? data.db"
    writer = sqlite3.connect(database)
    try:
        writer.execute("CREATE TABLE stock (name TEXT, quantity INTEGER)")
        writer.execute("INSERT INTO stock VALUES (?, ?)", ("notebook", 4))
        writer.commit()
    finally:
        writer.close()

    uri = database.resolve().as_uri() + "?mode=ro"
    reader = sqlite3.connect(uri, uri=True)
    try:
        print("Report:", reader.execute("SELECT name, quantity FROM stock").fetchone())
        try:
            reader.execute("UPDATE stock SET quantity=0")
        except sqlite3.OperationalError as error:
            assert error.sqlite_errorcode == sqlite3.SQLITE_READONLY
            print("Write rejected:", error.sqlite_errorname)
        else:
            raise AssertionError("Read-only connection allowed an update")
    finally:
        reader.close()

    missing = Path(directory) / "missing.db"
    try:
        unexpected = sqlite3.connect(missing.resolve().as_uri() + "?mode=ro", uri=True)
    except sqlite3.OperationalError as error:
        assert error.sqlite_errorcode == sqlite3.SQLITE_CANTOPEN
        print("Missing input rejected:", error.sqlite_errorname)
    else:
        unexpected.close()
        raise AssertionError("Missing database unexpectedly opened")
    assert not missing.exists()
    print("Missing file created:", missing.exists())

Run it:

python3 -B open-read-only-report.py

Observed output:

Report: ('notebook', 4)
Write rejected: SQLITE_READONLY
Missing input rejected: SQLITE_CANTOPEN
Missing file created: False

Apply the result

The query reads the fixture normally, while the attempted update is rejected. The second connection attempt fails instead of creating an empty file. Path.as_uri() escapes the question mark in the real filename, and uri=True tells Python to interpret the URI options.

mode=ro protects this connection’s main database from writes. It is not a sandbox for arbitrary SQL, nor does it stop another process from changing the underlying file. Use filesystem permissions and appropriate SQL restrictions where those boundaries matter.

Do not add immutable=1 merely to mean read-only: it promises that the file cannot change and changes SQLite’s locking behavior. This fixture uses a closed, ordinary rollback-journal database. Reading a live WAL database has additional sidecar and directory-access considerations. All handles close before the temporary directory is removed.

See the official reference for the API and its constraints. The output above comes from this controlled fixture.

Clean up

Remove the saved source, return to the starting directory, then remove the empty practice directory:

rm -- open-read-only-report.py
cd -- "$start_dir" || exit 1
rmdir -- "$work_dir"
unset start_dir work_dir

If the directory is not empty, inspect its contents before removing anything else.

Acknowledgements

This post was created with assistance from AI.