SQLAlchemy ORM Basics in Python | Compillo Python Course

In previous sessions, you executed SQL statements directly using database cursors. While effective, hand-writing SQL queries for complex enterprise applications can become tedious and error-prone. Object-Relational Mapping (ORM) solves this by allowing developers to model relational database tables as Python classes, mapping table rows directly to object instances.

1. SQLAlchemy Architecture & Installation

SQLAlchemy is Python's premier enterprise-grade ORM toolkit. It is divided into two core layers:

SQLAlchemy Core: Low-level SQL abstraction, connection pooling, and schema definition.
SQLAlchemy ORM: High-level declarative mapper that translates Python objects directly into database transactions.

Install SQLAlchemy using pip:

pip install sqlalchemy

Component	Description	Example Usage
Engine	Manages low-level connection pools and dialect conversion.	`create_engine('sqlite:///app.db')`
Declarative Base	Base class for creating Python classes that represent DB tables.	`class Base(DeclarativeBase): pass`
Session	Manages the active workspace and object persistence lifecycle.	`Session(engine)`
Column & Types	Defines table schema properties and constraints.	`Mapped[int] = mapped_column(primary_key=True)`

2. Defining Declarative Models

To map a database table using SQLAlchemy modern 2.0 syntax, subclass DeclarativeBase and define table columns as type-annotated mapped attributes.

from sqlalchemy import create_engine, String from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column # 1. Define Declarative Base Class class Base(DeclarativeBase): pass # 2. Define Model Class mapping to 'users' table class User(Base): __tablename__ = 'users' id: Mapped[int] = mapped_column(primary_key=True) username: Mapped[str] = mapped_column(String(50), unique=True, nullable=False) email: Mapped[str] = mapped_column(String(120), nullable=False) def __repr__(self): return f"" # 3. Create Engine and Generate Tables engine = create_engine('sqlite:///:memory:', echo=False) Base.metadata.create_all(engine)

3. Session Management and CRUD Operations

The Session object acts as a staging area for all database interactions. Objects added to a session are automatically converted into SQL queries upon calling commit().

from sqlalchemy.orm import Session from sqlalchemy import select # Create interactive session context with Session(engine) as session: # CREATE user_1 = User(username="alex_dev", email="alex@example.com") user_2 = User(username="sam_coder", email="sam@example.com") session.add_all([user_1, user_2]) session.commit() # READ statement = select(User).where(User.username == "alex_dev") retrieved_user = session.scalars(statement).first() print("Fetched User:", retrieved_user) # UPDATE retrieved_user.email = "alex_updated@example.com" session.commit() # DELETE session.delete(user_2) session.commit()

4. Universal Database Switchability

One of SQLAlchemy's major strengths is database driver abstraction. You can transition your backend from SQLite to PostgreSQL or MySQL simply by updating the database connection string:

# SQLite Connection String engine = create_engine("sqlite:///local_data.db") # PostgreSQL Connection String (requires psycopg2) engine = create_engine("postgresql+psycopg2://user:password@localhost:5432/production_db") # MySQL Connection String (requires mysql-connector-python) engine = create_engine("mysql+mysqlconnector://user:password@localhost:3306/production_db")

Pro-Tip: Modern SQLAlchemy 2.0 uses explicit type annotations with Mapped[] and mapped_column() to ensure static type checkers like mypy fully understand your database schemas.

🏋️ Try It Yourself: Practice Challenges

Challenge 1: Product Inventory Model

Define a SQLAlchemy model class Product with fields for id, name (String), and price (Float). Instantiate an engine and create the corresponding table in memory.

Challenge 2: Query Filter Helper

Write a Python function get_products_above_price(session, min_price) that uses select() to filter and return all products with a price greater than min_price.

Challenge 3: Transaction Rollback Handler

Implement a session block that attempts to insert a record with duplicate unique fields, catching the exception and invoking session.rollback() cleanly.

Interactive ORM Sandbox (Emulated Session Interface)

# Emulating SQLAlchemy Declarative ORM pattern in Python
class Column:
    def __init__(self, data_type, primary_key=False, unique=False):
        self.data_type = data_type
        self.primary_key = primary_key
        self.unique = unique

class ModelMeta(type):
    def __new__(cls, name, bases, attrs):
        attrs['_columns'] = {k: v for k, v in attrs.items() if isinstance(v, Column)}
        return super().__new__(cls, name, bases, attrs)

class BaseModel(metaclass=ModelMeta):
    def __init__(self, **kwargs):
        for k, v in kwargs.items():
            setattr(self, k, v)

    def __repr__(self):
        fields = ", ".join(f"{k}={getattr(self, k, None)!r}" for k in self._columns)
        return f"<{self.__class__.__name__}({fields})>"

# User Defined ORM Class
class Employee(BaseModel):
    id = Column(int, primary_key=True)
    name = Column(str)
    role = Column(str)

# Interactive ORM Operations Demonstration
emp1 = Employee(id=1, name="Sarah Connor", role="Lead Engineer")
emp2 = Employee(id=2, name="John Doe", role="Data Analyst")

print("Created Object 1:", emp1)
print("Created Object 2:", emp2)
print("\nInspecting Schema Attributes:")
for col_name, col_obj in Employee._columns.items():
    print(f" -> Field: {col_name:<6} | Type: {col_obj.data_type.__name__:<5} | PK: {col_obj.primary_key}")

Console Output:

Click "Run Code" above to execute ORM emulation script...

📝 Knowledge Check Quiz

1. What primary role does an ORM like SQLAlchemy play in application development?

It executes raw SQL faster than native C drivers It maps relational database tables directly to Python classes and objects It compiles Python code directly into C++ binary code

2. Which SQLAlchemy object handles the active staging area and transactional lifecycle for DB operations?

Engine ConnectionPool Session

3. How do you change the underlying backend database when using SQLAlchemy ORM?

Rewrite all model classes from scratch Change the database connection string passed to create_engine() Reinstall Python into a new virtual environment

Horizontal Ad Banner Slot (728x90 / Responsive)