Python course ยท Module 4: Data, APIs and Databases

SQLAlchemy - ORM for Python

10 min read ยท
In this lesson9

Welcome! Last lesson you wrote raw SQL - cursor.execute("SELECT * FROM species"). It works, but every query is a string, and a typo in a column name shows up only at runtime. Now meet ORM (Object-Relational Mapping) - working with a database through Python classes instead of SQL!

Safari Analogy: Raw SQL is like filling out paper forms by hand. SQLAlchemy ORM is a digital system - Python objects automatically mapped to database tables!

What is ORM?

ORM maps objects to database tables: a class is a table, an object a row, an attribute a column. In raw SQL, saving a lion is a string with question marks for the values:

cursor.execute("INSERT INTO species (name, population) VALUES (?, ?)", ("Lion", 120))

With an ORM you create a regular object, and the session prepares the INSERT itself:

lion = Species(name="Lion", population=120)
session.add(lion)
session.commit()

Both add the same row, but in the second SQLAlchemy writes the SQL.

ORM advantages:

  • Python classes instead of SQL strings
  • Type hints and autocomplete
  • Less vulnerable to SQL injection
  • Easier relationships between tables
  • Migrations and schema evolution
  • Database independence (PostgreSQL, MySQL, SQLite...)

SQLAlchemy is a powerful ORM + tool for working with SQL databases. We use the current 2.0 series.

Installation

It is not part of standard Python, so install it once from the terminal:

pip install sqlalchemy

For SQLite that's all - the sqlite3 driver is built into Python.

SQLAlchemy Basics - Models

A model is a class describing a table: its name goes in __tablename__, its columns are Column attributes with types and constraints. Five steps lead to a ready session:

from sqlalchemy import create_engine, Column, Integer, String, Boolean
from sqlalchemy.orm import declarative_base, sessionmaker

# 1. Create Base - base class for models
Base = declarative_base()

# 2. Define model - Python class = SQL table
class Species(Base):
    __tablename__ = 'species'  # Table name

    # Columns
    id = Column(Integer, primary_key=True, autoincrement=True)
    scientific_name = Column(String, nullable=False, unique=True)
    common_name = Column(String, nullable=False)
    population = Column(Integer, default=0)
    habitat = Column(String)
    endangered = Column(Boolean, default=False)

    def __repr__(self):
        return f"<Species('{self.common_name}', pop={self.population})>"


# 3. Connect to database
engine = create_engine('sqlite:///safari_orm.db', echo=True)  # echo=True displays SQL

# 4. Create tables
Base.metadata.create_all(engine)

# 5. Create session (manages database operations)
Session = sessionmaker(bind=engine)
session = Session()

primary_key=True is the unique row number, nullable=False forbids empty values, unique=True duplicates. Use echo=True while learning to see the generated SQL. Note: Base.metadata.create_all() creates tables only if they don't exist (like IF NOT EXISTS).

Column Types

SQLAlchemySQLPython
IntegerINTEGERint
StringVARCHARstr
TextTEXTstr (longer)
FloatFLOATfloat
BooleanBOOLEANbool
DateTimeDATETIME / TIMESTAMPdatetime
DateDATEdate
JSONJSONdict/list (SQLite 3.9+)

MySQL requires a text length, e.g. String(100).

CRUD with SQLAlchemy

CREATE - adding records

A new record is a new model object. It goes into the session and reaches the database only on commit:

# Create object
lion = Species(
    scientific_name="Panthera leo",
    common_name="Lion",
    population=120,
    habitat="savanna",
    endangered=True
)

# Add to session
session.add(lion)

# Commit
session.commit()

print(f"Added lion ID: {lion.id}")  # ID automatically assigned!

# Add multiple at once
species_list = [
    Species(scientific_name="Loxodonta africana", common_name="Elephant", population=450, endangered=True),
    Species(scientific_name="Gorilla gorilla", common_name="Gorilla", population=230, endangered=True),
]

session.add_all(species_list)
session.commit()

Before commit() the lion lives only in the session, with id equal to None.

READ - retrieving data

A query starts with session.query(Species), and you chain methods like links, ending with the one that fetches results:

# All species
all_species = session.query(Species).all()
for species in all_species:
    print(species.common_name, species.population)

# First result
first = session.query(Species).first()

# Get by ID
lion = session.query(Species).get(1)  # ID = 1 (legacy style - warning in 2.0)
# or (SQLAlchemy 1.4+, recommended)
lion = session.get(Species, 1)

# Filtering - WHERE
endangered = session.query(Species).filter(Species.endangered == True).all()
savanna = session.query(Species).filter(Species.habitat == "savanna").all()

# Multiple conditions (AND)
results = session.query(Species).filter(
    Species.endangered == True,
    Species.population > 100
).all()

# OR
from sqlalchemy import or_
results = session.query(Species).filter(
    or_(Species.habitat == "savanna", Species.habitat == "forest")
).all()

# LIKE
results = session.query(Species).filter(Species.scientific_name.like("Panthera%")).all()

# ORDER BY
sorted_species = session.query(Species).order_by(Species.population.desc()).all()

# LIMIT and OFFSET
top_5 = session.query(Species).limit(5).all()
page_2 = session.query(Species).limit(10).offset(10).all()

# COUNT
count = session.query(Species).count()
endangered_count = session.query(Species).filter(Species.endangered == True).count()

filter() takes Python operators and SQLAlchemy turns them into WHERE. The session.query() style is "legacy" in 2.0 - the newer select() comes in the FastAPI module.

UPDATE - updating

An ORM update is just an attribute change. The session notices it and sends an UPDATE statement on commit():

# Method 1: Fetch object, modify, commit
lion = session.query(Species).filter(Species.common_name == "Lion").first()
lion.population = 125
session.commit()

# Method 2: Bulk update
session.query(Species).filter(Species.habitat == "savanna").update({
    "endangered": True
})
session.commit()

Method 1 loads the object, method 2 changes many rows in one statement without loading them.

DELETE - deleting

You pass a single object to session.delete(), and many rows are removed by a filter ending with delete():

# Method 1: Fetch object, delete
species_to_delete = session.get(Species, 10)
if species_to_delete:
    session.delete(species_to_delete)
    session.commit()

# Method 2: Bulk delete
session.query(Species).filter(Species.population == 0).delete()
session.commit()

session.get() returns None when the record doesn't exist, hence the if. Without commit() the change is lost.

Relationships - One-to-Many

One species has many field observations: a foreign key links them in the database, relationship() in Python (a separate, simplified example):

from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship

class Species(Base):
    __tablename__ = 'species'

    id = Column(Integer, primary_key=True)
    common_name = Column(String, nullable=False)
    population = Column(Integer, default=0)

    # Relationship: one species -> many observations
    observations = relationship("Observation", back_populates="species", cascade="all, delete-orphan")


class Observation(Base):
    __tablename__ = 'observations'

    id = Column(Integer, primary_key=True)
    species_id = Column(Integer, ForeignKey('species.id'), nullable=False)
    observation_date = Column(String, nullable=False)
    location = Column(String, nullable=False)
    count = Column(Integer, default=0)

    # Relationship: many observations -> one species
    species = relationship("Species", back_populates="observations")

ForeignKey('species.id') points to the species column, back_populates connects both ends, and the cascade setting deletes observations with their species.

Now we append the lion's observations to its list and save everything to the database with one commit():

# Usage
lion = Species(common_name="Lion", population=120)

# Add observations to the lion
lion.observations.append(Observation(observation_date="2024-01-15", location="Serengeti", count=12))
lion.observations.append(Observation(observation_date="2024-01-20", location="Masai Mara", count=8))

session.add(lion)
session.commit()

# Fetch observations
lion = session.query(Species).filter(Species.common_name == "Lion").first()
for obs in lion.observations:
    print(f"{obs.observation_date}: {obs.count}x in {obs.location}")

We never set species_id - SQLAlchemy filled it in thanks to the relationship.

Safari example - complete ORM system

Let's gather everything into a service class that hides the session. First, the models with timestamps:

from sqlalchemy import create_engine, Column, Integer, String, Boolean, ForeignKey, DateTime
from sqlalchemy.orm import declarative_base, sessionmaker, relationship
from datetime import datetime, timezone
from typing import List, Optional

Base = declarative_base()


def utc_now() -> datetime:
    return datetime.now(timezone.utc)


class Species(Base):
    """Species model"""
    __tablename__ = 'species'

    id = Column(Integer, primary_key=True, autoincrement=True)
    scientific_name = Column(String, nullable=False, unique=True)
    common_name = Column(String, nullable=False)
    population = Column(Integer, default=0)
    habitat = Column(String)
    endangered = Column(Boolean, default=False)
    created_at = Column(DateTime, default=utc_now)
    updated_at = Column(DateTime, default=utc_now, onupdate=utc_now)

    # Relationships
    observations = relationship("Observation", back_populates="species", cascade="all, delete-orphan")

    def __repr__(self):
        return f"<Species('{self.common_name}', pop={self.population})>"


class Observation(Base):
    """Observation model"""
    __tablename__ = 'observations'

    id = Column(Integer, primary_key=True, autoincrement=True)
    species_id = Column(Integer, ForeignKey('species.id'), nullable=False)
    observation_date = Column(String, nullable=False)
    location = Column(String, nullable=False)
    count = Column(Integer, default=0)
    notes = Column(String)
    created_at = Column(DateTime, default=utc_now)

    # Relationships
    species = relationship("Species", back_populates="observations")

    def __repr__(self):
        return f"<Observation({self.species.common_name if self.species else 'N/A'}, {self.count}x @ {self.location})>"

default and onupdate get the utc_now function, not its result, so the time is computed on every write. datetime.utcnow() is deprecated since Python 3.12.

The SafariORM class creates the engine and session in its constructor, and each of its methods performs one CRUD operation:

class SafariORM:
    """Safari database management via ORM"""

    def __init__(self, db_url: str = "sqlite:///safari_orm.db"):
        self.engine = create_engine(db_url, echo=False)
        Base.metadata.create_all(self.engine)
        Session = sessionmaker(bind=self.engine)
        self.session = Session()

    # === SPECIES ===

    def create_species(self, scientific_name: str, common_name: str,
                      population: int = 0, habitat: str = "",
                      endangered: bool = False) -> Species:
        """Add a species"""
        species = Species(
            scientific_name=scientific_name,
            common_name=common_name,
            population=population,
            habitat=habitat,
            endangered=endangered
        )
        self.session.add(species)
        self.session.commit()
        return species

    def get_species(self, species_id: int) -> Optional[Species]:
        """Get a species"""
        return self.session.get(Species, species_id)

    def list_species(self, endangered: Optional[bool] = None,
                    habitat: Optional[str] = None) -> List[Species]:
        """List species"""
        query = self.session.query(Species)

        if endangered is not None:
            query = query.filter(Species.endangered == endangered)
        if habitat:
            query = query.filter(Species.habitat == habitat)

        return query.order_by(Species.common_name).all()

    def update_species(self, species_id: int, **kwargs) -> Optional[Species]:
        """Update a species"""
        species = self.session.get(Species, species_id)
        if not species:
            return None

        for key, value in kwargs.items():
            if hasattr(species, key):
                setattr(species, key, value)

        species.updated_at = utc_now()
        self.session.commit()
        return species

    def delete_species(self, species_id: int) -> bool:
        """Delete a species"""
        species = self.session.get(Species, species_id)
        if not species:
            return False

        self.session.delete(species)
        self.session.commit()
        return True

    # === OBSERVATIONS ===

    def create_observation(self, species_id: int, observation_date: str,
                          location: str, count: int, notes: str = "") -> Optional[Observation]:
        """Add an observation"""
        species = self.session.get(Species, species_id)
        if not species:
            return None

        observation = Observation(
            species_id=species_id,
            observation_date=observation_date,
            location=location,
            count=count,
            notes=notes
        )
        self.session.add(observation)
        self.session.commit()
        return observation

    def get_observations_for_species(self, species_id: int) -> List[Observation]:
        """Get observations for a species"""
        return self.session.query(Observation).filter(
            Observation.species_id == species_id
        ).order_by(Observation.observation_date.desc()).all()

    def close(self):
        """Close session"""
        self.session.close()

Methods return models or None, so the rest of the program never touches SQL or the session.

The demo uses an in-memory database, so repeated runs never clash on unique names:

# === DEMONSTRATION ===

print("=== SAFARI ORM SYSTEM ===\n")

db = SafariORM("sqlite:///:memory:")

# 1. Add species
print("1. Adding species (ORM)...")
lion = db.create_species("Panthera leo", "Lion", 120, "savanna", True)
elephant = db.create_species("Loxodonta africana", "Elephant", 450, "savanna", True)
gorilla = db.create_species("Gorilla gorilla", "Gorilla", 230, "forest", True)

print(f"   Added: {lion}, {elephant}, {gorilla}")

# 2. Fetch species
print("\n2. Fetching species...")
retrieved_lion = db.get_species(lion.id)
print(f"   {retrieved_lion.common_name}: {retrieved_lion.population} individuals")

# 3. List endangered
print("\n3. List of endangered species...")
endangered = db.list_species(endangered=True)
for species in endangered:
    print(f"   - {species.common_name}: {species.population}")

# 4. Update
print("\n4. Updating population...")
db.update_species(lion.id, population=125)
lion = db.get_species(lion.id)
print(f"   New lion population: {lion.population}")

# 5. Add observations
print("\n5. Adding observations...")
db.create_observation(lion.id, "2024-01-15", "Serengeti", 12, "Pride with cubs")
db.create_observation(lion.id, "2024-01-20", "Masai Mara", 8, "Male coalition")

# 6. Fetch observations
print("\n6. Lion observations...")
observations = db.get_observations_for_species(lion.id)
for obs in observations:
    print(f"   - {obs.observation_date}: {obs.count}x @ {obs.location}")

# 7. Relationships
print("\n7. Navigating through relationships...")
lion = db.get_species(lion.id)
print(f"   Lion has {len(lion.observations)} observations:")
for obs in lion.observations:
    print(f"     {obs.location}: {obs.count}x")

db.close()
print("\nDemonstration complete")

A database address is always a URL such as sqlite:///file.db - a bare file name raises an ArgumentError.

A Look at the Next Camp

Next, Darwin shows you NoSQL MongoDB - a document database without a rigid schema. The same steps look like this (a MongoDB server is required):

from pymongo import MongoClient

client = MongoClient()      # connection to the MongoDB server
db = client["safari"]       # choose a database
db.animals.insert_one({"name": "Lion", "population": 120})
results = db.animals.find() # cursor with documents

There is no model and no create_all() - the animals collection appears on the first write.

Summary

In this lesson you learned:

  • What ORM is and what it's for
  • SQLAlchemy: Base, models, columns
  • CRUD with ORM: add, query, update, delete
  • Filtering: filter(), order_by(), limit()
  • Relationships: One-to-Many, relationship, ForeignKey
  • A complete Safari system with ORM

Remember: an ORM is a digital catalog - you hand over Python objects, and the SQL forms get filled in for you.

Docs entries used in this lesson

Spotted a mistake in this lesson?

Check yourself

Answer the questions from this lesson. Pick an answer to see right away whether it is correct.

  1. 1. ORM (Object-Relational Mapping) allows you to:

  2. 2. In SQLAlchemy, a model is:

Hands-on tasks in the game

  • Code editor

    Create an Animal model with fields: id, name, species, weight

  • Code editor

    Retrieve all animals of species 'Tygrys' using session.query()

  • Horizontal ordering

    Arrange the elements in the correct order:

  • Click in order

    Click the elements in the correct order:

  • Vertical ordering

    Arrange MongoDB operations:

  1. QuestionORM
  2. QuestionSQLAlchemy Model
  3. Coding taskSQLAlchemy Model
  4. Coding taskQuery in SQLAlchemy
  5. Put in orderSQL INSERT
  6. Click in orderBuilding response.json()
  7. Put in orderMongoDB CRUD

Useful articles