SQLAlchemy 2 In Practice - Chapter 2 - Database Tables
a day ago
- SQLAlchemy is divided into Core and ORM modules; Core handles database integration and SQL generation, while ORM provides an abstraction layer for Python objects.
- Database tables are defined as Python classes inheriting from a declarative base class (e.g., Model), with columns typed using Mapped and mapped_column.
- The MetaData object manages table definitions; its create_all() and drop_all() methods create or drop tables, but migrations require Alembic for changes.
- Sessions manage model instances and database transactions; they can be committed or rolled back, and best practices use context managers with sessionmaker.
- Queries use select() with model classes, executed via session.execute() or session.scalars(); filters use where() with Python operators, and results are tuples or scalars.
- Common query features include filters (e.g., LIKE, BETWEEN), ordering (order_by), aggregation (count, min, max), grouping (group_by with having), and pagination (limit/offset or cursor-based).
- Indexes improve query performance on columns used in where/order_by/group_by; columns can be indexed via index=True in mapped_column.
- Constraints like PRIMARY KEY, UNIQUE, and NOT NULL enforce data integrity; NOT NULL is default for Mapped columns; optional columns use Mapped[Optional[t]].
- Objects are added to a session with add() and deleted with delete(); changes are permanent after commit().
- Example exercises include querying by year, CPU type, manufacturer prefixes, and aggregating counts per year or country.