Advanced · Go deeper · 45 min · Estimated time
Prevent duplicate tasks with Python and SQLite
Build and test a local event ledger with a unique key and a transaction. Repeat the execution and verify that only two tasks are stored.
By Eric Muriel
Before you start
You will build: A local demonstration of idempotency: processing the same event again does not create another task.
- Complete the CSV tutorial and know how to run a Python script.
- Python 3 with sqlite3 available. A new exercise folder without real databases.
Example executed with Python 3.9.6. Repetition, persistence after reopening, content conflicts and rollback after task creation failure checked.
01Prepare an isolated workspace
Download idempotency.py into a new folder. Open your terminal there and verify sqlite3 is available. The first run creates exercise.db in that folder; do not use a database filename from another project.
python3 -c "import sqlite3; print(sqlite3.sqlite_version)"Check the result
A SQLite version number without an import error.
If it does not work
If sqlite3 is missing, check your Python distribution. On Windows try py instead of python3.
02Follow the transaction in the code
Open the file and find record. It first looks up the ID. If the ID and content already exist, it returns duplicate. Changed text under the same ID fails. A new event and task are inserted inside with connection: both changes commit or roll back together.
Check the result
You can identify the primary key, SQL parameters and both inserts in the same transaction.
If it does not work
Do not move task insertion outside the with block: separating the changes breaks the rollback guarantee being demonstrated.
03Process the sample batch
Run the script once against a new database. The batch contains E-1 twice and E-2 once. Three messages arrive, but only two tasks should be stored.
python3 idempotency.py exercise.dbCheck the result
E-1 created E-1 duplicate E-2 created tasks: 2
If it does not work
If every event is duplicate, the example already ran. Use a new name such as exercise-test.db to start fresh without deleting files.
04Repeat and test a conflict
Run the same command again: all three events should be duplicate and tasks should remain 2. Next, temporarily change the text for E-1 in a copy of the script and run it against the same database. It must fail with Conflict rather than overwrite the previous task.
python3 idempotency.py exercise.dbCheck the result
E-1 duplicate E-1 duplicate E-2 duplicate tasks: 2
If it does not work
If new tasks appear, check you are using the same .db file and IDs. A new ID represents another event even when its text matches.
Sources, verification and limitations
A sequential demonstration using one local database. It does not implement public webhooks, authentication, external delivery, distributed concurrency or lock retries. A local transaction cannot guarantee a remote API call happens exactly once.
How this content is prepared