Sqlite FAQ & Answers

Recovery limitations: 1) Some content may be permanently deleted and unrecoverable if a rogue process overwrote part of the file. 2) The recovered database may be defective - think of recovery as salvage, not restoration. 3) .recover creates a 'lost_and_found' table for orphaned rows it cannot attribute to any table. 4) The best protection is to maintain regular backups - recovery is a last resort when backups are unavailable.

Use the .recover command in SQLite CLI: sqlite3 corrupted.db '.recover' | sqlite3 recovered.db. Unlike .dump which requires a valid database structure, .recover attempts to reassemble the database based on data extracted directly from as many database pages as possible. The .recover command is usually able to recover data from all uncorrupted parts of the database, whereas .dump stops when the first sign of corruption is encountered.

Try these steps in order: 1) Run .recover: sqlite3 corrupted.db '.recover' | sqlite3 new.db. 2) If .recover fails, try .dump: sqlite3 corrupted.db .dump | sqlite3 new.db. 3) Try .clone which can result in a DB that appears entirely functional. 4) Check integrity first with PRAGMA integrity_check to identify specific issues. Note: It is sometimes possible to perfectly restore a database, but that is the exception. Think of recovery as salvage - extract as much usable data as possible.

Use: sqlite3 newdatabase.db < dump.sql. Or from within sqlite3: sqlite3 newdatabase.db then .read dump.sql. Note that executescript() is used for restoration in Python as the dump file contains multiple SQL statements separated by semicolons. Ensure the target database doesn't exist or is empty to avoid conflicts with existing tables.

The .dump command reads data using the normal SQL database interface and stops when corruption is encountered. The .recover command instead directly scans database pages to extract data, making it able to recover data from all uncorrupted parts even when the database structure is damaged. Use .recover for corrupted databases; use .dump for healthy databases. If .recover fails, try .dump or .clone as alternatives which may give a mostly complete file.

An ungraceful restart can leave the sqlite3 database corrupted. Rather than deleting and starting over, run: sqlite3 home-assistant_v2.db '.recover' | sqlite3 home-assistant_v2.db.fixed. This preserves history of entity states and long-term statistics like energy readings. After recovery, rename the fixed database to replace the original. Some content may be permanently lost if that part of the file was overwritten.

Run: sqlite3 corruptdb '.recover --ignore-freelist' > data.sql. The --ignore-freelist option ignores pages that appear to be part of the freelist (deleted but not yet overwritten pages). Normally the freelist is scanned, and if it contains pages that look like they have content, that content is output. Use this option if the freelist itself may be corrupt or if you want to avoid recovering previously deleted data.

First recover the truncated database: sqlite3 truncated.db '.recover' | sqlite3 recovered.db. Then export to JSON as a flat array with Python: import sqlite3, json; conn = sqlite3.connect('recovered.db'); conn.row_factory = sqlite3.Row; cursor = conn.cursor(); tables = [t[0] for t in cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%'").fetchall()]; cursor.execute(f'SELECT * FROM {tables[0]}'); rows = [dict(r) for r in cursor.fetchall()]; json.dump(rows, open('output.json','w')). This outputs a flat JSON array like [{"col1": val1, "col2": val2}, ...] suitable for data recovery tasks.

Use with statement: import sqlite3; with sqlite3.connect('database.db') as conn: cursor = conn.cursor(); cursor.execute('SELECT * FROM table'). The context manager automatically commits on success. For explicit transaction control, wrap operations in try/except with conn.rollback() on failure. The connection is NOT closed when exiting the with block - call conn.close() explicitly if needed.

Set row_factory to sqlite3.Row: conn.row_factory = sqlite3.Row; cursor = conn.cursor(); cursor.execute('SELECT * FROM users'); row = cursor.fetchone(); print(row['name']). The Row object supports both index and key-based access. For pure dict: conn.row_factory = lambda c, r: dict(zip([col[0] for col in c.description], r)). The description attribute contains column names after executing a query.

Use executescript(): cursor.executescript('CREATE TABLE t1 (c1); CREATE TABLE t2 (c2); INSERT INTO t1 VALUES (1);'). Note: executescript() issues COMMIT first, then executes the script. For reading SQL from file: with open('schema.sql') as f: cursor.executescript(f.read()). Semicolons separate statements. This is required for restoring from iterdump() output.

Install: pip install sqlite-dump. Usage: from sqlite_dump import iterdump; import sqlite3; conn = sqlite3.connect(db_path); for line in iterdump(conn): print(line). The sqlite-dump library provides an improved alternative to Python's built-in .iterdump() method. The built-in iterdump() breaks with constraint errors on FTS (full-text search) tables, while sqlite-dump handles them correctly.

Use the iterdump() method: import sqlite3; conn = sqlite3.connect('database.db'); with open('backup.sql', 'w') as f: for line in conn.iterdump(): f.write(f'{line}\n'). The output includes CREATE TABLE statements, INSERT commands, and other SQL needed to rebuild the database. Note: iterdump() does not correctly handle FTS (full-text search) tables. For FTS support, use the sqlite-dump library: pip install sqlite-dump.

Query the data and convert to JSON: import sqlite3, json; conn = sqlite3.connect('db.db'); conn.row_factory = sqlite3.Row; cursor = conn.cursor(); cursor.execute('SELECT * FROM table_name'); rows = [dict(row) for row in cursor.fetchall()]; with open('output.json', 'w') as f: json.dump(rows, f, indent=2). The Row factory allows accessing columns by name. For large tables, use fetchmany() or iterate the cursor to avoid memory issues.

PRAGMA quick_check does most of the checking of PRAGMA integrity_check but runs much faster. It runs in O(N) time whereas integrity_check requires O(NlogN) time where N is the total number of rows. quick_check skips UNIQUE constraint verification and does not verify that index content matches table content. For a 4.2 GB database, integrity_check can take 20+ minutes while quick_check is about 2x faster.

Use PRAGMA integrity_check: sqlite3 database.db 'PRAGMA integrity_check;'. If the database is healthy, it returns 'ok'. If corruptions are found, SQLite reports the errors. Note: integrity_check does not find FOREIGN KEY errors - use PRAGMA foreign_key_check for that. For faster checking, use PRAGMA quick_check which runs in O(N) time vs O(NlogN) for integrity_check, but skips UNIQUE and index consistency checks.

Query sqlite_master: cursor.execute("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name"); tables = cursor.fetchall(). This returns all table names. For table schema: cursor.execute("SELECT sql FROM sqlite_master WHERE name='tablename'"). In CLI use: .tables for list, .schema tablename for structure. Note: sqlite_master also contains 'index', 'trigger', and 'view' types.

Use the backup() method (Python 3.7+): import sqlite3; src = sqlite3.connect('database.db'); dst = sqlite3.connect('backup.db'); src.backup(dst); dst.close(); src.close(). This creates an atomic copy safe for concurrent access. Alternative: use iterdump() to create SQL text backup. For file copy approach, ensure WAL checkpoint first: conn.execute('PRAGMA wal_checkpoint(FULL)').