Database Access
SQLAlchemy
SQL toolkit and Object Relational Mapper (ORM) for Python.
Interview: Industry standard for SQL databases in Python. Crucial for understanding ORM patterns, session lifecycles, and N+1 query problems.
SQLAlchemy is the premier SQL toolkit and Object-Relational Mapper (ORM) for Python. It provides a full suite of enterprise-level persistence patterns designed for efficient and high-performing database access, separating database schema creation from application logic.
Core vs. ORM
SQLAlchemy is split into two main components:
- SQLAlchemy Core: The foundational SQL abstraction layer. It provides schema definition tools, SQL expression language (programmatic query building), and database engines for connection management.
- SQLAlchemy ORM: Built on top of Core, it maps Python classes directly to database tables. It uses the Data Mapper pattern, allowing objects to be queried and persisted in an object-oriented style.
The Declarative Base and Mapping
Modern SQLAlchemy uses declarative_base() to map classes. A class inheriting from the base class specifies its table name via __tablename__ and attributes using Column constructs.
Working with Sessions
The Session object manages the lifecycle of database operations. It serves as a Unit of Work, tracking changes to objects and writing them to the database in a single transaction during session.commit().
Interview Insight
Be prepared to discuss the difference between session.flush() and session.commit(). flush() sends pending SQL statements to the database transaction buffer (generating auto-increment IDs), but doesn't persist them permanently. commit() commits the transaction to the database, finalizing the persistence.
Use Cases
Enterprise Application Backends — Mapping complex relational schemas cleanly to business logic models.
Database Portability — Writing code that can switch backend databases (e.g. from development SQLite to production PostgreSQL) with a single config change.
Automated Schema Migrations — Pairing SQLAlchemy with Alembic to track database migrations programmatically.
Common Mistakes
Not closing sessions — Leaving database connection sessions open, causing database connection pools to exhaust.
Forgetting transactions rollback — Not rolling back the transaction in the event of an error, leaving the session in an inconsistent state.
N+1 Query Problem — Querying children of relationship fields in a loop without utilizing eager loading (like joinedload).