Databases with Python
Connect, query, model and migrate data from Python code.
SQL on its own is only half the job: real services reach a database from application code, and that is where the expensive mistakes live. You will drive sqlite3 through the DB-API that every Python driver shares, parameterize every query, make multi-step work atomic with transactions and constraints, stop two requests from losing each other's updates, map rows onto dataclasses behind a repository, version a schema with a migration runner you write yourself, and see honestly what an ORM does for you and what it costs. Then you meet production: PostgreSQL and psycopg 3, connection pools sized against your workers, and a database URL from the environment. The course ends with a small inventory system built from schema to report to test suite, finished with a reservation two pickers cannot oversell.
What you will learn
- sqlite3
- DB-API
- Parameterized queries
- Transactions
- Concurrency and isolation
- Repository pattern
- Migrations
- ORMs
- PostgreSQL and connection pools
Lessons
- 1
The DB-API with sqlite3
Connect to a database from Python, run parameterized queries, and read results with cursors and row factories.
11 exercises
- 2
Transactions and Integrity
Commit, roll back and keep multi-step work atomic, and let constraints refuse data your code should never store.
11 exercises
- 3
Concurrency and Isolation
Two requests, one row: reproduce a lost update, fix it three ways, and learn what each isolation level promises and how to retry when it refuses.
12 exercises
- 4
Repositories and Data Mapping
Turn rows into objects, keep every SQL statement behind a repository, and page through results without N+1 queries.
11 exercises
- 5
Schema Migrations
Version a schema with ordered, recorded, transactional steps, and make changes that do not take the service down.
9 exercises
- 6
ORMs and Data Mappers
What an ORM actually does, built in miniature over sqlite3, and an honest account of when to reach for one.
11 exercises
- 7
Production Databases: PostgreSQL, Connections and Pools
What changes when the database is PostgreSQL on another machine: psycopg 3 placeholders, RETURNING, connection pools sized against your workers, connections that always come back, and a URL from the environment.
10 exercises
- 8
Putting It Together
Build a stockroom service in five stages: schema and migrations, a repository with transactions, a report query, the test suite that keeps it honest, and a reservation two pickers cannot oversell.
7 exercises
How you practice
You practice in the browser and every exercise gives you feedback right away. This course uses these formats:
- Code exercise: 31
- Predict the output: 13
- Fix the bug: 13
- Fill in the blank: 8
- Multiple choice: 5
- Type the answer: 4
- Match the pairs: 3
- Select all that apply: 3
- Reorder lines: 2
Aligned to
- Data Management
- DM-CoreCore Database System Concepts
- DM-QueryingQuery Construction
- DM-InternalsDBMS Internals
- Parallel and Distributed Computing
- PDC-CoordinationCoordination
Proto Node Labs is not affiliated with or endorsed by ACM / IEEE-CS / AAAI. Exam names are trademarks of their owners.