sqlite3.Connection.iterdump() exports the structure and data of a SQLite database as a sequence of SQL statements. In recent Python versions, the filter parameter makes this tool more focused by limiting the dump to database objects whose names match a pattern. This is useful for partial migrations, test fixtures, audits, selective backups, and troubleshooting.
This guide explains how filtered dumps work, how to validate them, and how to avoid incomplete restores. The main goal is to use the feature predictably and safely in real projects.
What iterdump returns
Calling conn.iterdump() produces an iterator of strings. The output commonly contains CREATE TABLE, INSERT, CREATE INDEX, and transaction statements that can rebuild the database.
import sqlite3
with sqlite3.connect("app.db") as conn:
for line in conn.iterdump():
print(line)
Because the result is an iterator, you can stream the export to a file instead of keeping the entire dump in memory. For broader context, read the guide to SQLite databases in Python and the article about Python context managers.
Using the filter parameter
The filter argument applies a SQLite LIKE-style pattern to object names. The percent sign matches any sequence of characters, while the underscore matches one character.
with sqlite3.connect("app.db") as conn:
lines = conn.iterdump(filter="customer%")
for line in lines:
print(line)
This can include tables such as customers, customer_addresses, and related indexes whose names use the same prefix.
Practical example
import sqlite3
conn = sqlite3.connect(":memory:")
conn.executescript("""
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE customer_tags (
customer_id INTEGER,
tag TEXT
);
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER,
total REAL
);
INSERT INTO customers(name) VALUES ('Ana'), ('Carlos');
INSERT INTO orders(customer_id, total) VALUES (1, 120.50);
""")
selected = list(conn.iterdump(filter="customer%"))
print("\n".join(selected))
The output is limited to matching object names. To save it:
with sqlite3.connect("app.db") as conn, open(
"customers.sql", "w", encoding="utf-8"
) as output:
for line in conn.iterdump(filter="customer%"):
output.write(line + "\n")
Useful pattern examples
user%matches objects starting with user.%log%matches objects containing log.tmp_%matches a conventional temporary-object prefix.report_202_matches one variable character at the end.
Consistent naming conventions make selective exports much easier. Prefixes also help organize migrations and maintenance scripts.
Validate before restoring
Never assume that a filtered dump contains everything required. Restore it into a temporary database first.
def validate_dump(lines: list[str]) -> None:
test_db = sqlite3.connect(":memory:")
test_db.executescript("\n".join(lines))
test_db.close()
You should also inspect the recreated catalog:
with sqlite3.connect(":memory:") as test:
test.executescript(script)
objects = test.execute(
"SELECT type, name FROM sqlite_master ORDER BY type, name"
).fetchall()
print(objects)
Watch for dependencies
A matching table may depend on objects that do not match the filter. Foreign keys, views, triggers, and application-level assumptions can make a partial dump incomplete. Check foreign keys with PRAGMA foreign_key_list, inspect triggers, and document cross-module relationships.
Error handling is important when running maintenance scripts. See the Academify guides to Python exception handling and Python logging.
Security considerations
A dump file is executable SQL. Do not restore files from untrusted sources. Review the statements, limit process permissions, and perform the first restore in an isolated environment.
You can calculate a SHA-256 checksum to detect unexpected changes:
from hashlib import sha256
from pathlib import Path
content = Path("customers.sql").read_bytes()
print(sha256(content).hexdigest())
For authoritative details, consult the official Python sqlite3 documentation and the SQLite SQL language reference.
Version compatibility
Check whether the deployed Python version supports the parameter. Projects that run on older interpreters need a fallback or a clearly documented minimum version.
import inspect
import sqlite3
signature = inspect.signature(sqlite3.Connection.iterdump)
if "filter" in signature.parameters:
lines = conn.iterdump(filter="customer%")
else:
lines = conn.iterdump()
Filtering a complete textual dump afterward is risky because SQL statements can span multiple lines and dependencies may be difficult to detect. Prefer a supported Python version whenever selective dumping is a requirement.
Consistency and concurrent writes
A dump should represent a consistent database state. If the application continues writing during export, coordinate the operation with transactions or an appropriate maintenance window. For large or highly active databases, consider SQLite’s backup API or operational snapshot tools.
Record the export time, application release, schema version, Python version, and SQLite version. This metadata makes future troubleshooting much easier.
Testing a complete workflow
A reliable automated test can create a source database, generate a filtered dump, restore it into a new database, and compare expected rows and schema objects.
def restore_and_check(lines):
target = sqlite3.connect(":memory:")
target.executescript("\n".join(lines))
count = target.execute(
"SELECT COUNT(*) FROM customers"
).fetchone()[0]
assert count == 2
target.close()
Tests should cover empty patterns, patterns with no matches, names containing underscores, views, indexes, and foreign-key dependencies.
Best practices
- Use specific patterns instead of very broad filters.
- Restore into a temporary database before production use.
- Inspect foreign keys, views, indexes, and triggers.
- Store version and schema metadata with the dump.
- Treat the dump as executable code.
- Keep a full backup strategy in addition to partial exports.
- Automate integrity checks after restoration.
When filtered dumps are a good fit
Filtered dumps work well for development fixtures, module-specific debugging, controlled data migration, demonstrations, and test environments. They are less suitable as the only backup mechanism for a complex production database.
Conclusion
The filter parameter of sqlite3.Connection.iterdump() provides a convenient way to export selected SQLite objects from Python. It reduces noise and makes focused maintenance tasks easier.
However, filtering by object name does not automatically include every dependency. Validate the generated SQL, restore it in isolation, inspect relationships, and retain complete backups. With these safeguards, filtered dumps become a practical and powerful addition to a Python SQLite workflow.
