Database programming with Python: sqlite3 and SQLAlchemy
You connect to databases safely from Python and build solid data layers with parameterised queries, transactions, an ORM and schema migrations.
Level 301 · 18 lessons · 15 practice labs
Lessons
DB-API and sqlite3
- The DB-API: connections, cursors and results
- Parameterised queries and SQL injection
- Transactions: commit, rollback and with
- Row factories and type conversion
- Concurrent writes in SQLite and WAL
- Short task: move an asset inventory into SQLite
SQLAlchemy
- Building queries with SQLAlchemy Core
- ORM models and relationships
- Sessions, queries and the N+1 problem
- Schema migrations with alembic
- Separating data access: the repository pattern
- Short task: port raw SQL code to the ORM
Robustness
- Connection pools and timeouts
- Testing database code
- Keeping connection details out of the code
- Backing up and restoring with Python
- Short task: an audit trail that records every change
Capstone
- Capstone: an asset registry with migrations and tests
Practice labs
- Getting query results as dictionaries: Use the sqlite3 row_factory to read rows by column name and return a stock report as a list of dictionaries.
- Making a search query parameterised: Turn a customer search query built with an f-string into one with placeholders so names containing quotes also work.
- Building an IN list and sort column safely: Write a query function that builds a variable-length IN list with placeholders and the sort column from an allow-list.
- Storing dates and Decimals correctly: Fix code that stores dates as text and amounts as floats with adapters and converters so the cent-loss and time-zone tests pass.
- Rolling back a half-done transfer: Put transfer code that loses money when one of two updates fails into a single transaction so balances stay unchanged on error.
- Getting rid of the database-locked error: Fix code that fails with "database is locked" when two writers run together using WAL mode and a busy timeout.
- Skipping a bad row in a bulk import: Make an import of a thousand rows that rolls everything back for one bad row recover per row with savepoints.
- Writing ORM models for books and authors: Define author and book models with a one-to-many relationship for Çınaraltı Library and query an author's books.
- Building a grouped report with Core: Write a monthly sales total query with SQLAlchemy Core select, join and group_by without raw SQL.
- Removing the N+1 query in an order list: Fix a list that fires a customer query per order with selectinload and cap the query count in a test.
- Separating data access into a repository: Move session calls buried in business logic into a repository class and run the service tests without a database using a fake.
- Adding a column with a migration: Write an alembic migration that adds a column with a default to the product table, with a working downgrade.
- Wrapping each test in a rolled-back transaction: Fix a setup that leaks data between tests with a fixture that runs each test in a transaction and rolls it back.
- Taking connection details out of the code: Move a hard-coded connection URL to an environment variable, fail clearly when it is missing and mask the password in logs.
- Backing up a live database and verifying it: Take a consistent backup while writes continue with the sqlite3 backup API and verify it with integrity_check and row counts.
About this course
You connect to databases safely from Python and build solid data layers with parameterised queries, transactions, an ORM and schema migrations.
What you will learn
- DB-API and sqlite3
- SQLAlchemy
- Robustness
- Capstone
Prerequisites
You should be comfortable with the level 101 and 201 topics of the same path.
Lab environment
Lessons run in your own Kali Linux terminal that opens in the browser. Nothing to install; the lab starts clean in every lesson.
Frequently asked questions
- Is this course right for my level?
- The course is at level 301; it assumes you know the topics of the lower-level courses.
- Is there anything to install?
- No. Every lesson runs in your own lab environment that opens in the browser.
- How do lessons work?
- Each lesson has 8 to 10 steps and a short quiz. Early steps show the command to type, later steps give less help; if two attempts fail, the solution opens.
- Can I try it for free?
- Yes. The first three lessons of every course are free.