Python · Advanced level

Advanced level · Module 3: Python and SQL Databases · Lesson 4 of 4

Transactions, Integrity and Backups

Keep data consistent when several changes must succeed or fail together, and protect it with backups.

In this lesson you will

  • Use transactions with commit and rollback
  • Automate database backups

Key concepts

transactioncommit/rollbackconstraintbackup

The lesson

A transaction groups several changes so they succeed or fail together. Recording a sale must insert the sale and reduce the stock; if the second step fails, the first must be undone.

with connection: commits the transaction if the block succeeds and rolls it back if an exception is raised. Constraints such as NOT NULL and CHECK (stock >= 0) let the database itself refuse impossible data.

Back up regularly. connection.backup() copies a live SQLite database safely; keep dated copies in a different place from the original.

Step by step

  1. Add a CHECK constraint so stock cannot go negative.
  2. Rewrite record_sale to run inside a with connection block.
  3. Force an error and confirm nothing was saved.
  4. Write a backup script that saves a dated copy.

Practice activity

Make record_sale reduce stock and insert the sale in one transaction, and write a script that backs up the database daily with a dated filename.

Expected result: Atomic sales updates and dated backup files.

Check your understanding

  1. What is the point of a transaction?

    Show answer

    To make a group of changes all succeed or all fail, keeping data consistent.

Taught live by an instructor — online or in person. Free course lessons are for self-study and do not lead to a certificate — see registration options.