SQLAlchemy 2.0 In Practice introduces Python developers to the latest version of the SQLAlchemy relational database framework, by building a non-trivial database project from the start and in small incremental steps. Following this book's guidance, you will build a database for a made-up online business that sells retro home computers of the 1980s and 1990s. As part of this project you will learn how to build:
- a product catalog
- an ordering system with customers, orders and order details
- a product review solution with star ratings and user comments
- a page analytics solution that does not rely on third-party services
All chapters include abundant examples of database queries, along with exercises for the reader to work on (solutions to the exercises are provided).
For developers interested in using SQLAlchemy in larger applications, information and examples on integrating databases into web applications written with the Flask and FastAPI frameworks is provided. A chapter dedicated to the use of SQLAlchemy 2 in asynchronous applications based on the asyncio package is included as well.
Below you can find the table of contents:
- Preface
- What You Will Build
- Prerequisites
- How To Work With The Example Code
- Conventions Used In This Book
- Acknowledgements
- 1. Database Setup
- 1.1 Project Directory
- 1.2 Python and SQLAlchemy Installation
- 1.3 Database Choices
- 1.4 SQLite Database Installation
- 1.5 MySQL Database Set Up
- 1.6 PostgreSQL Database Set Up
- 1.7 Database Connection URLs
- 2. Database Tables
- 2.1 SQLAlchemy Core and SQLAlchemy ORM
- 2.2 The Database Engine
- 2.3 Models
- 2.4 Database Metadata
- 2.5 Sessions
- 2.6 A First SQLALchemy Application
- 2.7 Queries
- 2.8 Indexes
- 2.9 Constraints
- 2.10 Deletions
- 2.11 Exercises
- 3. One-To-Many Relationships
- 3.1 When to Use a Relationship
- 3.2 One-To-Many Relationship Implementation
- 3.3 A Revised Importer Application
- 3.4 One-To-Many Relationship Queries
- 3.5 Lazy vs. Eager Relationships
- 3.6 Deletion of Related Objects with a Cascade
- 3.7 Exercises
- 4. Many-To-Many Relationships
- 4.1 What Is a Many-To-Many Relationship?
- 4.2 How Many-To-Many Relationships Work
- 4.3 A Simple Many-To-Many Relationship Implementation
- 4.4 Product Importer Script Updates
- 4.5 Many-To-Many Relationship Queries
- 4.6 Deleting From Many-To-Many Relationships
- 4.7 Database Migrations
- 4.8 Exercises
- 5. Advanced Many-To-Many Relationships
- 5.1 Many-To-Many Relationships with Additional Data
- 5.2 More Aggregation Techniques
- 5.3 Exercises
- 6. A Page Analytics Solution
- 6.1 Part 1: Blog Articles and Authors
- 6.2 Part 2: Blog Sessions and Views
- 6.3 Importer Scripts
- 6.4 Page Analytics Queries
- 6.5 Part 3: Multi-Language Blog Articles
- 6.6 Language Queries
- 6.7 Exercises
- 7. Asynchronous SQLAlchemy
- 7.1 How is Async Different?
- 7.2 Asynchronous Database Drivers
- 7.3 Engines, Metadata and Sessions
- 7.4 Relationships Loaders
- 7.5 Alembic Configuration
- 7.6 Implicit I/O After a Session is Flushed
- 7.7 Import Scripts
- 7.8 Queries
- 8. SQLAlchemy and the Web
- 8.1 General Integration Approach
- 8.2 SQLAlchemy Integration Techniques
- 8.3 An Example Web Application
- 8.4 A Last Word
- 9. Solutions to the Exercises
- 9.1 Chapter 2
- 9.2 Chapter 3
- 9.3 Chapter 4
- 9.4 Chapter 5
- 9.5 Chapter 6