To resume a Python pipeline safely, store each unit’s durable results and the marker that records its completion in the same SQLite transaction. After an interruption, read the last committed marker and start with the next unit. If the transaction did not commit, SQLite rolls back both changes, so the marker cannot claim that database work finished when it did not.
How do I save progress with SQLite?
Give each pipeline or partition a stable key, and store its last completed unit in a progress table. When processing a unit, write its output and advance the progress row together. Commit only after both writes succeed.
The key invariant is that the marker describes durable output—not work that was merely attempted. SQLite’s official documentation describes its transactions as atomic, consistent, isolated, and durable, including when interrupted by a program crash, operating-system crash, or power failure. See SQLite’s transactional overview and its atomic-commit explanation. The latter explains the detailed commit mechanism for rollback mode; WAL uses a different mechanism.
Example schema
This example keeps one progress row per pipeline and partition, and one result row per unit. Use identifiers that remain stable across restarts.
Do these 3 things before closing this tab:
1Fix the driver behind crashes, sound loss and screen glitches2Clear out junk files and repair common Windows errors3Scan for outdated or missing drivers - takes under a minute#1 Best Overall
- Read Before You Buy — No Video Output: These adapters support charging and USB 2.0 data transfer, but cannot transmit video signals. Except for standard USB webcams (which use USB data only), they are not compatible with HDMI/DisplayPort cables, video-capable USB-C hubs, or docking stations with video output.
- Convert USB-A Ports to USB-C: Designed to connect USB-C earphones, cables, flash drives, card readers, and other USB-C accessories to standard USB-A ports. Plug-and-play with no drivers or software required.
- Aluminum Alloy Housing: Built with a sturdy aluminum alloy shell that aids in heat dissipation and protects against daily wear and scratches. Designed to maintain a stable and secure connection.
- Compact & Travel-Friendly: The ultra-compact design allows the adapter to stay plugged into your device without blocking adjacent ports or adding bulk, reducing wear and tear on your original USB ports.
- 12-Month Warranty: Backed by a 12-month manufacturer warranty for peace of mind. Designed to meet strict quality control standards for reliable everyday performance.
CREATE TABLE IF NOT EXISTS pipeline_progress (
pipeline_key TEXT NOT NULL,
partition_key TEXT NOT NULL,
last_completed_unit INTEGER,
PRIMARY KEY (pipeline_key, partition_key)
);
CREATE TABLE IF NOT EXISTS unit_results (
pipeline_key TEXT NOT NULL,
partition_key TEXT NOT NULL,
unit_id INTEGER NOT NULL,
result_json TEXT NOT NULL,
PRIMARY KEY (pipeline_key, partition_key, unit_id)
);The result table’s unique key makes a repeated database write for the same unit address the same row. Choose a suitable result format and key for your workload; a monotonically increasing integer is only an example.
Commit each unit’s output and marker together
Here is a transaction pattern for Python 3.12 and later, where the autocommit parameter is available. With autocommit=False, Python’s sqlite3 keeps a transaction open and commit() or rollback() ends it before opening another. The example therefore explicitly commits each unit, and rolls back if either write fails.
import json
import sqlite3
def save_unit(con, pipeline_key, partition_key, unit_id, result):
try:
con.execute(
"""INSERT INTO unit_results
(pipeline_key, partition_key, unit_id, result_json)
VALUES (?, ?, ?, ?)
ON CONFLICT (pipeline_key, partition_key, unit_id)
DO UPDATE SET result_json = excluded.result_json""",
(pipeline_key, partition_key, unit_id, json.dumps(result)),
)
con.execute(
"""INSERT INTO pipeline_progress
(pipeline_key, partition_key, last_completed_unit)
VALUES (?, ?, ?)
ON CONFLICT (pipeline_key, partition_key)
DO UPDATE SET last_completed_unit = excluded.last_completed_unit""",
(pipeline_key, partition_key, unit_id),
)
con.commit()
except Exception:
con.rollback()
raise
con = sqlite3.connect("pipeline.db", autocommit=False)Compute the unit’s result before entering this short write transaction when feasible. This avoids holding a write transaction open during slow computation or network activity. Then call save_unit() to persist the result and progress marker as one unit of database work.
Rank #2
- 5-in-1 USB-C Hub: Experience comprehensive connectivity featuring a Power Delivery input, two USB-A 2.0 ports, a USB-A 3.0 port, and an HDMI port. (Note: The USB-C power delivery input port is only for connecting an external wall charger to power your laptop and cannot power peripheral devices.)
- 90W Pass-Through Charging: Achieve optimal charging with 90W pass-through power to your laptop, supported by a total input of 100W, with the hub reserving 10W for operational efficiency. (Note: Wall charger not included.)
- Quick Data Transfers: Accelerate your productivity with rapid data transfers using a high-speed 5Gbps USB 3.0 port and two 480Mbps USB 2.0 ports.
- 4K HDMI Display: Enhance your visual experience with a hub capable of delivering 4K resolution at 30Hz in both mirror and extend modes. Please note that this hub is compatible with MacBook (macOS 12 and newer), Windows 10 and 11, ChromeOS, and laptops equipped with DP Alt Mode and Power Delivery. Note: This device is not compatible with Linux.
- What You Get: Anker USB-C Hub (5-in-1, 4K HDMI), welcome guide, 18-month warranty, and our friendly customer service.
For Python versions before 3.12, sqlite3.connect() does not accept the autocommit argument. Transaction behavior in those versions is controlled through isolation_level, which current Python documentation describes as the legacy transaction-control interface. Make the transaction mode explicit for the Python version you support rather than relying on defaults that may vary. See the Python 3.14 sqlite3 documentation.
How do I resume a Python pipeline after it crashes?
On startup, read the stored marker for the same pipeline and partition, then process the next uncommitted unit. If no marker exists, begin at the first unit. For sequential integer IDs:
row = con.execute(
"""SELECT last_completed_unit
FROM pipeline_progress
WHERE pipeline_key = ? AND partition_key = ?""",
(pipeline_key, partition_key),
).fetchone()
last_completed = row[0] if row is not None else None
start_unit = 0 if last_completed is None else last_completed + 1
for unit_id in range(start_unit, total_units):
result = compute_unit(unit_id)
save_unit(con, pipeline_key, partition_key, unit_id, result)This assumes unit IDs are contiguous and ordered. If they are not, store or query a stable sequence key, or track completion per unit instead of calculating the next ID with + 1. The marker must correspond to the same pipeline version and partitioning scheme; if those change, use a new key or define a migration so old progress is not misapplied.
Rank #3
- Sleek 7-in-1 USB-C Hub: Features an HDMI port, two USB-A 3.0 ports, and a USB-C data port, each providing 5Gbps transfer speeds. It also includes a USB-C PD input port for charging up to 100W and dual SD and TF card slots, all in a compact design.
- Flawless 4K@60Hz Video with HDMI: Delivers exceptional clarity and smoothness with its 4K@60Hz HDMI port, making it ideal for high-definition presentations and entertainment. (Note: Only the HDMI port supports video projection; the USB-C port is for data transfer only.)
- Double Up on Efficiency: The two USB-A 3.0 ports and a USB-C port support a fast 5Gbps data rate, significantly boosting your transfer speeds and improving productivity.
- Fast and Reliable 85W Charging: Offers high-capacity, speedy charging for laptops up to 85W, so you spend less time tethered to an outlet and more time being productive.
- What You Get: Anker USB-C Hub (7-in-1), welcome guide, 18-month warranty, and our friendly customer service.
What happens at each interruption point?
- Before the transaction: no output or marker change has been committed, so the unit is attempted on restart.
- During the transaction, before commit: if the transaction is rolled back or interrupted, the output and marker changes do not become a partially committed pair; restart uses the prior marker.
- After commit: both database changes are durable, so restart begins after that unit.
SQLite’s transaction guarantees cover changes in the database transaction. They do not mean that arbitrary work performed before the commit is automatically undone.
How should retries and external side effects work?
A unit may run more than once: for example, computation may finish and the process may stop before the database transaction commits. Make repeated attempts safe. Stable unit IDs, unique constraints, deterministic computation, and an upsert can prevent duplicate database rows or make a retry replace the same result.
SQLite cannot atomically commit a database transaction and an action in another system. If a unit sends an email, calls an API, or writes to another database, a crash can occur between that action and the local commit. Depending on ordering, a retry could repeat the external action or the pipeline could record progress without the external system having completed its work.
Rank #4
- Dual Converters, Infinite Potential：Includes 2× USB C male to USB A female adapters and 2× USB A male to USB C female adapters. Perfect for a wide range of uses—tablets with Bluetooth keyboards, expand USB ports on macbook, and more. Two different converters for all your daily needs
- Next-Level 10Gbps & 3A Charging: No more slow 480Mbps, this usb to usb c adapter has a transfer speed of up to 10Gbps, allowing you to do more transferring in less time. This usb adapter fits both USB A and USB C charger, supporting up to 3A fast charging
- Upgraded Exquisite Craftsmanship: With an aluminum alloy housing and metal connector, the usbc to usb adapter is extremely durable and sturdy. Rigorously tested to withstand more than 10,000 times of plugging and unplugging, ensuring long-lasting performance
- Broad Compatible: The usb c to usb adapter widely supports all USB C/ USB A devices like laptops, tablets, cellphones, car chargers, and phone chargers. Such as compatible with MacBook Pro/Air 2023/2022, Thunderbolt 4/3 Devices,Apple MagSafe Watch 9/8/7/SE/Ultra, iPad Pro 2022/2021, Samsung Galaxy S23/S20/S10, and iPhone 17/16/15 Pro. Plug and play
- Please Note: To reach 10Gbps speed, keep the cable under 3.3 ft. For USB A Male to USB C adapters, try flipping the USB C connector. USB C Male to USB A adapters support bidirectional 10Gbps transfer within 3.3 ft
- Use an idempotency key accepted by the external service when available, so a retry can be recognized as the same operation.
- For reliable delivery, consider a transactional outbox: write an intent to a database table in the same transaction as the unit result and progress marker, then have a separate sender deliver pending intents and record delivery status.
- Where neither approach fits, reconcile the database and external system after interruptions, and make the retry policy explicit.
An outbox does not make a remote action part of SQLite’s transaction; it makes the intent durable and gives the sender a recoverable delivery record. The receiving service or reconciliation process still needs to handle duplicate or uncertain outcomes.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.How large should a checkpointed unit be?
Choose a boundary that makes progress useful without keeping a write transaction open across the whole pipeline. Committing each unit gives a fine-grained restart point but requires a transaction for each unit. Committing a batch reduces how often progress is recorded, but an interruption can require redoing the uncommitted portion of that batch. In either case, write the batch’s outputs and its corresponding marker in the same transaction.
Keep expensive computation and external calls outside the write transaction where possible. A transaction should cover the database changes whose consistency depends on one another—not hours of processing between checkpoints.
Recommended Free Tools
Best Value
- 5-in-1 Connectivity: Equipped with a 4K HDMI port, a 5 Gbps USB-C data port, two 5 Gbps USB-A ports, and a USB C 100W PD-IN port. Note: The USB C 100W PD-IN port supports only charging and does not support data transfer devices such as headphones or speakers.
- Powerful Pass-Through Charging: Supports up to 85W pass-through charging so you can power up your laptop while you use the hub. Note: Pass-through charging requires a charger (not included). Note: To achieve full power for iPad, we recommend using a 45W wall charger.
- Transfer Files in Seconds: Move files to and from your laptop at speeds of up to 5 Gbps via the USB-C and USB-A data ports. Note: The USB C 5Gbps Data port does not support video output.
- HD Display: Connect to the HDMI port to stream or mirror content to an external monitor in resolutions of up to 4K@30Hz. Note: The USB-C ports do not support video output.
- What You Get: Anker 332 USB-C Hub (5-in-1), welcome guide, our worry-free 18-month warranty, and friendly customer service.
Is SQLite WAL checkpointing the same as pipeline progress?
No. An application progress checkpoint is your pipeline’s record of completed work. A WAL checkpoint is a SQLite operation that transfers committed changes from the write-ahead log back into the main database file. SQLite documents WAL’s reader/writer behavior and checkpointing in Isolation In SQLite.
WAL can allow readers and a writer to coexist under SQLite’s documented conditions, but it introduces a separate WAL file and checkpoint behavior. It is a journal mode, not a mechanism for deciding which pipeline unit should run next. Do not enable it simply because the application uses progress checkpoints; choose it based on the workload’s reader/writer needs.
When backing up a live database in WAL mode, do not assume copying only the main database file captures all current state: committed changes may still be represented in the WAL. Use SQLite’s backup mechanism or another documented, coordinated approach.
Transaction details that can break a checkpoint design
Know what autocommit means
In current Python, transaction control is configurable through the connection’s autocommit attribute, which the documentation recommends. With autocommit=True, commit() and rollback() have no effect; do not use the example’s commit-and-rollback pattern unchanged in that mode. With autocommit=False, those methods close the current transaction and sqlite3 opens another. Consult the documentation for the Python version you deploy and configure the connection deliberately.
Crashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minuteWindows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstallDo not use executescript() as an in-transaction shortcut
Python’s sqlite3.Connection.executescript() implicitly commits pending work before it runs the script. If you expect earlier changes to remain part of an uncommitted transaction, that behavior can break the intended boundary. Use individual execute() calls for the unit’s writes, and consult the sqlite3 documentation for the exact transaction behavior of your Python release.
Quick Recap
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.
