Recommended Free Tools
To resume a Python pipeline safely, save each unit’s durable output and the marker that says it is complete in the same SQLite transaction. After a crash, read the last committed marker and start with the next unit. If the output write and marker update are separate, a crash can leave the database claiming work is done when its result is missing—or leave a result that the pipeline will repeat.
The example below targets Python 3.12 or later and uses explicit SQL transaction control. It keeps computation outside the write transaction, uses stable unit identifiers, and commits one unit at a time. This handles work stored in SQLite; it does not make an email, API call, or other external side effect atomic with the database.
How do I save Python pipeline progress with SQLite?
Use one progress row per pipeline run or partition, and give each processed unit a stable identifier. In one transaction, write the unit’s result and advance the progress marker. Commit only when both writes succeed. If either fails before commit, roll back both.
Here is a compact schema. The last_position field is an ordinal in a fixed, repeatable input sequence; -1 means no unit has committed yet. The result table’s composite primary key prevents duplicate rows for the same pipeline and unit.
#1 Best Overall
CREATE TABLE IF NOT EXISTS pipeline_progress (
pipeline_id TEXT PRIMARY KEY,
last_position INTEGER NOT NULL DEFAULT -1
);
CREATE TABLE IF NOT EXISTS unit_results (
pipeline_id TEXT NOT NULL,
unit_id TEXT NOT NULL,
result_json TEXT NOT NULL,
PRIMARY KEY (pipeline_id, unit_id)
);A position is safe only if the sequence and its ordering remain stable across restarts. If inputs can be inserted, removed, or reordered, use a durable cursor or sequence version rather than assuming that position 12 still identifies the same work. The marker should identify the last contiguous completed unit, not the highest unit that happened to finish when work runs concurrently.
How do I resume after a crash?
The example requires Python 3.12 or later because it uses the autocommit connection argument. It sets autocommit=True and issues explicit BEGIN, COMMIT, and ROLLBACK statements. With this setting, the connection’s commit() and rollback() methods have no effect; explicit SQL makes the transaction boundary visible in the code. Python recommends controlling transactions with the autocommit attribute; the older isolation_level controls are documented as legacy behavior in the Python 3.14 sqlite3 documentation.
Rank #2
import json
import sqlite3
DB_PATH = "pipeline.db"
PIPELINE_ID = "daily-import-v1"
units = [
("record-001", {"value": "alpha"}),
("record-002", {"value": "beta"}),
("record-003", {"value": "gamma"}),
]
def transform(payload):
# Do the unit's computation here, outside the write transaction.
return {"normalized": payload["value"].upper()}
con = sqlite3.connect(DB_PATH, autocommit=True)
try:
con.execute("""CREATE TABLE IF NOT EXISTS pipeline_progress (
pipeline_id TEXT PRIMARY KEY,
last_position INTEGER NOT NULL DEFAULT -1
)""")
con.execute("""CREATE TABLE IF NOT EXISTS unit_results (
pipeline_id TEXT NOT NULL,
unit_id TEXT NOT NULL,
result_json TEXT NOT NULL,
PRIMARY KEY (pipeline_id, unit_id)
)""")
con.execute(
"INSERT INTO pipeline_progress (pipeline_id, last_position) "
"VALUES (?, -1) ON CONFLICT (pipeline_id) DO NOTHING",
(PIPELINE_ID,),
)
last_position = con.execute(
"SELECT last_position FROM pipeline_progress WHERE pipeline_id = ?",
(PIPELINE_ID,),
).fetchone()[0]
for position, (unit_id, payload) in enumerate(units):
if position <= last_position:
continue
result = transform(payload)
result_json = json.dumps(result, sort_keys=True)
con.execute("BEGIN IMMEDIATE")
try:
con.execute(
"INSERT INTO unit_results (pipeline_id, unit_id, result_json) "
"VALUES (?, ?, ?) "
"ON CONFLICT (pipeline_id, unit_id) DO UPDATE "
"SET result_json = excluded.result_json",
(PIPELINE_ID, unit_id, result_json),
)
con.execute(
"UPDATE pipeline_progress SET last_position = ? "
"WHERE pipeline_id = ?",
(position, PIPELINE_ID),
)
con.execute("COMMIT")
except BaseException:
con.execute("ROLLBACK")
raise
finally:
con.close()The primary key and upsert make a repeated write to the same unit replace its stored result instead of adding a duplicate row. That helps with retries, but does not make a nondeterministic computation deterministic: if the same unit is recomputed differently, the upsert stores the newer result. Choose retry semantics that fit the job, such as deterministic output, a guarded insert, or an explicit version for revised results.
BEGIN IMMEDIATE starts the write transaction before the two related writes. The potentially slow transformation runs before that transaction, so the database is not held in a write transaction while computation runs. If another process can run the same pipeline concurrently, add a coordination strategy; a progress read followed by processing is not, by itself, a claim or lock on a unit.
Rank #3
What happens if the process stops at different points?
- Before the unit transaction: neither its result nor its progress update has been written. A restart attempts that unit again.
- After the result write but before the marker update: both statements are still in one uncommitted transaction. A crash or rollback leaves neither change committed.
- After the marker update but before commit: the result and marker remain uncommitted together; a crash does not make the marker durable ahead of the result.
- After commit: both writes are committed. The next run reads the marker and skips through that position.
SQLite describes its transactions as serializable and ACID, including durability across program crashes, operating-system crashes, or power failures. Its detailed transactional overview explains the guarantee; the atomic commit article describes the mechanism specifically for rollback mode, while WAL uses a different mechanism. These guarantees apply to the SQLite transaction, not to unrelated actions performed by the pipeline.
How should I handle retries and external side effects?
A database transaction cannot roll back a message already sent, an API request already accepted, or a write made to another system. For example, if a unit sends an email and the process crashes before committing its SQLite marker, a restart may send the email again.
- Use an idempotency key: pass a stable key derived from the pipeline and unit to a remote service that supports deduplication.
- Use an outbox: store the intent to perform an external action in SQLite in the same transaction as the unit result, then have a separate dispatcher deliver pending intents and record delivery status. The dispatcher still needs retry-safe delivery or deduplication.
- Reconcile: compare the database’s recorded state with the external system and repair discrepancies when the external service cannot provide suitable idempotency.
Keep identifiers stable across attempts. A new random identifier per retry defeats deduplication. For parallel work, track completion per unit or partition rather than advancing a single high-water mark past gaps: if unit 8 finishes before unit 7, a marker of 8 could cause a restart to skip unfinished work.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.What does WAL checkpointing mean?
An application progress checkpoint is the pipeline’s record of completed work. A SQLite WAL checkpoint is a different operation: it transfers committed changes from the write-ahead log back into the main database file. SQLite’s isolation documentation explains that readers and a writer can coexist under documented WAL conditions and describes this separate checkpoint behavior.
Free tools Windows power users keep installed
One-click scans. No signup required.
Best Value
WAL is a journal mode, not a requirement for maintaining application progress. Choose it based on the workload’s reader/writer pattern rather than assuming it makes a pipeline more reliable or faster. WAL also means database state may include changes in a separate WAL file; copying only the live main database file can omit those changes. Use SQLite’s backup mechanism or another documented, coordinated backup approach rather than treating a casual file copy as a complete backup.
Quick Recap
Transaction details that can break a checkpoint
- Do not split the writes across commits. If the result commits first and progress does not, a retry must safely recognize or overwrite that result. If progress commits first and the result does not, the restart may skip missing output.
- Do not keep one write transaction open for the whole pipeline. Commit at a deliberate unit or batch boundary so a crash loses at most the uncommitted work and the database is not held in a write transaction through slow computation or network calls.
- Be careful with
executescript(). Python’s sqlite3 documentation says it implicitly commits pending work before running the script. Do not use it inside a transaction when you expect earlier pending changes to remain uncommitted. - Choose a batch boundary deliberately. A per-unit commit gives a precise restart point; a batch commit reduces transaction frequency but means the whole uncommitted batch may need to be retried. In either case, write all outputs for the chosen boundary and its corresponding marker in the same transaction.
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
