Backups, restoration, and integrity evidence

import sqlite3
from contextlib import closing

with closing(sqlite3.connect(":memory:", isolation_level=None)) as source, \
     closing(sqlite3.connect(":memory:", isolation_level=None)) as copy:
    source.executescript("CREATE TABLE item(value INTEGER); INSERT INTO item VALUES(2);")
    source.backup(copy)
    source.execute("UPDATE item SET value=99")
    print("source", source.execute("SELECT value FROM item").fetchone()[0])
    print("copy", copy.execute("SELECT value FROM item").fetchone()[0])

source 99
copy 2

The completed backup retains2 even after the source changes to99. Both databases are private and closed.

import sqlite3
from pathlib import Path
from tempfile import TemporaryDirectory
from contextlib import closing

with TemporaryDirectory(prefix="sqlite-backup-training-") as temporary:
    folder = Path(temporary)
    with closing(sqlite3.connect(folder / "source.db", isolation_level=None)) as source, \
         closing(sqlite3.connect(folder / "backup.db", isolation_level=None)) as backup, \
         closing(sqlite3.connect(":memory:", isolation_level=None)) as restored:
        source.executescript("CREATE TABLE account(id INTEGER PRIMARY KEY,amount INTEGER); INSERT INTO account VALUES(1,42);")
        source.backup(backup)
        print("backup_check", backup.execute("PRAGMA integrity_check").fetchone()[0])
        backup.backup(restored)
        print("restored", restored.execute("SELECT amount FROM account WHERE id=1").fetchone()[0])
        print("foreign_key_issues", len(restored.execute("PRAGMA foreign_key_check").fetchall()))

backup_check ok
restored 42
foreign_key_issues 0

The backup passes its synthetic integrity check; the restored database contains42 and reports no foreign-key violations.