Python course ยท Module 4: Data, APIs and Databases
SQLAlchemy - ORM for Python
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 - the most popular ORM in Python
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 sqlalchemyFor 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
| SQLAlchemy | SQL | Python |
|---|---|---|
Integer | INTEGER | int |
String | VARCHAR | str |
Text | TEXT | str (longer) |
Float | FLOAT | float |
Boolean | BOOLEAN | bool |
DateTime | DATETIME / TIMESTAMP | datetime |
Date | DATE | date |
JSON | JSON | dict/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 documentsThere 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. ORM (Object-Relational Mapping) allows you to:
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: