Topic 12. Working with relational databases: SQL basics, the sqlite3 module, and the SQLAlchemy ORM

Goal: learn to design tables and constraints, execute parameterized queries and atomic changes through sqlite3, use SQLAlchemy models and sessions, and check data access with isolated pytest tests.

Lecture contents

1. The relational model and SQL — The relational model and relationships · SQLite and table schemas · Data changes and SQL queries
2. The sqlite3 module and query parameters — The sqlite3 API and resource management · Parameters and SQL injection
3. Reports and transactions — JOIN, aggregates, and reports · Transactions and recovery after errors
4. The SQLAlchemy ORM — Moving from SQL to SQLAlchemy · Declarative models and sessions · ORM relationships and queries
5. Repositories, tests, and common mistakes — Repositories, tests, and schema changes · Common mistakes

Practice and review

• Practice — 3 worked examples with complete code
• Tasks — 30 variants, each with tasks at three difficulty levels
• Summary — conclusions, self-check questions, and useful links