42.1 Introduction to ORMs
ORM means Object-Relational Mapping. An ORM connects Python classes to database tables and Python objects to table rows. Instead of writing SQL for every operation, a programmer can create, read, update, and delete Python objects. The ORM then creates and executes suitable SQL statements behind the scenes.
Example: A Python object representing a database record
# A basic Python class representing a student.
class Student:
def __init__(self, student_id, name, grade):
self.student_id = student_id
self.name = name
self.grade = grade
# Create an object representing one student row.
student = Student(
student_id=1,
name="Michael",
grade=7
)
# Read values from the object.
print(student.student_id)
print(student.name)
print(student.grade)
Output:
1
Michael
7
Output Explanation:
The object contains the same kinds of values that could be stored in a students table. An ORM adds rules that connect these attributes to real database columns.
42.2 ORM vs Raw SQL
Raw SQL gives developers direct control over SQL statements. An ORM lets developers work mainly with Python classes and objects. Raw SQL can be useful for complex or highly optimized queries, while an ORM can reduce repeated code and improve application organization. Many projects use both approaches when each is appropriate.
Example: Raw SQL
# A raw SQL statement selects one student.
sql = """
SELECT id, name, grade
FROM students
WHERE id = 1
"""
print(sql)
Example: ORM-style operation
# ORM-style code works with a Python model.
student = session.get(Student, 1)
print(student.id)
print(student.name)
print(student.grade)
Possible Output:
1
Michael
7
Output Explanation:
Both approaches can retrieve the same record. Raw SQL describes the table operation directly, while the ORM returns a Student object with accessible attributes.
42.3 SQLAlchemy Introduction
SQLAlchemy is a Python database toolkit that provides both SQL expression tools and an Object-Relational Mapper. It supports database systems such as SQLite, PostgreSQL, and MySQL through suitable drivers. SQLAlchemy applications usually create an engine, define database structures, open sessions or connections, and execute database operations safely.
Example: Install SQLAlchemy
python -m pip install sqlalchemy
Example: Create an SQLite engine
# Import the engine-creation function.
from sqlalchemy import create_engine
# Create an engine connected to a local SQLite file.
engine = create_engine(
"sqlite:///school.db",
echo=False
)
print("SQLAlchemy engine created.")
print(engine.url)
Output:
SQLAlchemy engine created.
sqlite:///school.db
Output Explanation:
The engine stores the database connection configuration. SQLAlchemy will use the engine whenever the application needs to communicate with school.db.
42.4 SQLAlchemy Core
SQLAlchemy Core provides Python tools for describing tables and constructing SQL statements. It is closer to SQL than the ORM but still provides safe parameter handling and database independence. Core applications use objects such as MetaData, Table, Column, select, insert, update, and delete.
Example: Create and query a Core table
# Import SQLAlchemy Core tools.
from sqlalchemy import (
create_engine,
MetaData,
Table,
Column,
Integer,
String,
select
)
# Create the database engine.
engine = create_engine("sqlite:///core_example.db")
# Create a metadata container.
metadata = MetaData()
# Define a students table.
students = Table(
"students",
metadata,
Column("id", Integer, primary_key=True),
Column("name", String(100), nullable=False),
Column("grade", Integer, nullable=False)
)
# Create the table in the database.
metadata.create_all(engine)
# Open a transaction.
with engine.begin() as connection:
# Insert one row.
connection.execute(
students.insert().values(
name="Michael",
grade=7
)
)
# Open a connection for reading.
with engine.connect() as connection:
# Build a SELECT statement.
statement = select(students)
# Execute the statement.
rows = connection.execute(statement)
# Display each result.
for row in rows:
print(row.id, row.name, row.grade)
Output:
1 Michael 7
Output Explanation:
SQLAlchemy Core creates the table, inserts a row, and selects it. The returned row supports column names such as row.name and row.grade.
42.5 SQLAlchemy ORM
SQLAlchemy ORM maps Python classes to database tables. A mapped class describes both the Python object and its database columns. Objects can be added to a session, queried, changed, or deleted. SQLAlchemy examines the object changes and creates the necessary SQL statements when the session is flushed or committed.
Example: Create a simple ORM model
# Import SQLAlchemy ORM tools.
from sqlalchemy import create_engine, String
from sqlalchemy.orm import (
DeclarativeBase,
Mapped,
mapped_column,
Session
)
# Create a base class for all models.
class Base(DeclarativeBase):
pass
# Map this Python class to the students table.
class Student(Base):
__tablename__ = "students"
id: Mapped[int] = mapped_column(
primary_key=True
)
name: Mapped[str] = mapped_column(
String(100)
)
grade: Mapped[int]
# Create the database engine.
engine = create_engine("sqlite:///orm_example.db")
# Create all mapped tables.
Base.metadata.create_all(engine)
# Create and save one Student object.
with Session(engine) as session:
student = Student(
name="Sara",
grade=8
)
session.add(student)
session.commit()
print(student.id)
print(student.name)
print(student.grade)
Output:
1
Sara
8
Output Explanation:
SQLAlchemy converts the Student object into a new students-table row. After committing, the automatically created ID becomes available through student.id.
42.6 Creating Models
A model is a Python class mapped to a database table. The __tablename__ attribute supplies the table name, while mapped attributes describe its columns. Models can include primary keys, required values, unique constraints, default values, methods, and relationships. Clear model names and correct data types make database code easier to understand.
Example: Create a Product model
# Import required SQLAlchemy tools.
from decimal import Decimal
from sqlalchemy import String, Numeric
from sqlalchemy.orm import (
DeclarativeBase,
Mapped,
mapped_column
)
class Base(DeclarativeBase):
pass
class Product(Base):
__tablename__ = "products"
id: Mapped[int] = mapped_column(
primary_key=True
)
name: Mapped[str] = mapped_column(
String(150),
nullable=False
)
price: Mapped[Decimal] = mapped_column(
Numeric(10, 2),
nullable=False
)
quantity: Mapped[int] = mapped_column(
default=0
)
def display(self):
return (
f"{self.name}: "
f"${self.price:.2f}, "
f"Quantity: {self.quantity}"
)
# Create a Product object.
product = Product(
name="Keyboard",
price=Decimal("49.99"),
quantity=10
)
print(product.display())
Output:
Keyboard: $49.99, Quantity: 10
Output Explanation:
The model defines the products-table structure and also contains a regular Python method. ORM models can therefore combine stored data with useful application behavior.
42.7 Database Sessions
A SQLAlchemy Session manages ORM objects and database transactions. Objects added to the session are tracked until changes are committed, rolled back, or the session closes. A session is not the database itself. It is a workspace that communicates with the database through the engine and keeps track of loaded and modified objects.
Example: Add an object through a session
# Import the Session class.
from sqlalchemy.orm import Session
# Assume engine and Student were already created.
with Session(engine) as session:
# Create a new object.
student = Student(
name="Ali",
grade=6
)
# Place the object in the session.
session.add(student)
print("Before commit:", student.id)
# Save the transaction.
session.commit()
print("After commit:", student.id)
Output:
Before commit: None
After commit: 2
Output Explanation:
Before the insert occurs, the object has no generated ID. The commit saves the row, and the database supplies its primary-key value.
42.8 CRUD Operations
CRUD means Create, Read, Update, and Delete. ORM applications perform these actions by working with objects. Creating uses a new model object, reading uses a query or primary-key lookup, updating changes object attributes, and deleting uses session.delete(). A commit permanently saves the creation, update, or deletion.
Example: Complete ORM CRUD operations
# Import required tools.
from sqlalchemy import select
from sqlalchemy.orm import Session
with Session(engine) as session:
# CREATE
product = Product(
name="Mouse",
price=24.50,
quantity=20
)
session.add(product)
session.commit()
product_id = product.id
print("Created:", product.name)
# READ
saved_product = session.get(
Product,
product_id
)
print("Read:", saved_product.name)
# UPDATE
saved_product.quantity = 25
session.commit()
print("Updated quantity:", saved_product.quantity)
# DELETE
session.delete(saved_product)
session.commit()
deleted_product = session.get(
Product,
product_id
)
print("After deletion:", deleted_product)
Output:
Created: Mouse
Read: Mouse
Updated quantity: 25
After deletion: None
Output Explanation:
The object is created, retrieved, changed, and removed. After deletion, looking up the same primary key returns None.
42.9 Relationships
ORM relationships connect mapped objects that belong to related database tables. They are built on foreign keys but provide convenient Python attributes. For example, an Order object can have a customer attribute, while a Customer object can have an orders collection. Relationships can also control loading, cascading, and deletion behavior.
Example: Customer and Order relationship
# A customer can contain related orders.
customer = Customer(name="Sara")
# Create an order and connect it to the customer.
order = Order(total=45.00)
customer.orders.append(order)
# Access the relationship from both directions.
print(customer.name)
print(customer.orders[0].total)
print(order.customer.name)
Output:
Sara
45.0
Sara
Output Explanation:
Adding the order to customer.orders connects both Python objects. The order can then access its related customer through order.customer.
42.10 One-to-One Relationships
A one-to-one relationship connects one row to at most one row in another table. For example, one user may have one profile. A unique foreign key prevents several profiles from referring to the same user. In SQLAlchemy, uselist=False indicates that a relationship should return one object instead of a collection.
Example: User and Profile models
# Import SQLAlchemy tools.
from typing import Optional
from sqlalchemy import ForeignKey, String
from sqlalchemy.orm import (
Mapped,
mapped_column,
relationship
)
class User(Base):
__tablename__ = "users"
id: Mapped[int] = mapped_column(
primary_key=True
)
username: Mapped[str] = mapped_column(
String(100),
unique=True
)
profile: Mapped[Optional["Profile"]] = relationship(
back_populates="user",
cascade="all, delete-orphan",
uselist=False
)
class Profile(Base):
__tablename__ = "profiles"
id: Mapped[int] = mapped_column(
primary_key=True
)
biography: Mapped[str] = mapped_column(
String(500)
)
user_id: Mapped[int] = mapped_column(
ForeignKey("users.id"),
unique=True
)
user: Mapped["User"] = relationship(
back_populates="profile"
)
# Create related objects.
user = User(username="michael")
user.profile = Profile(
biography="Python student"
)
print(user.username)
print(user.profile.biography)
print(user.profile.user.username)
Output:
michael
Python student
michael
Output Explanation:
One User object is connected to one Profile object. The unique foreign key helps enforce the one-to-one relationship in the database.
42.11 One-to-Many Relationships
A one-to-many relationship connects one parent record to several child records. One customer can create many orders, but each order belongs to one customer. The foreign key is placed in the child table. SQLAlchemy normally represents the parent side as a list and the child side as one related object.
Example: Customer with many orders
# Import required tools.
from typing import List
from decimal import Decimal
from sqlalchemy import ForeignKey, Numeric, String
from sqlalchemy.orm import (
Mapped,
mapped_column,
relationship
)
class Customer(Base):
__tablename__ = "customers"
id: Mapped[int] = mapped_column(
primary_key=True
)
name: Mapped[str] = mapped_column(
String(100)
)
orders: Mapped[List["Order"]] = relationship(
back_populates="customer",
cascade="all, delete-orphan"
)
class Order(Base):
__tablename__ = "orders"
id: Mapped[int] = mapped_column(
primary_key=True
)
total: Mapped[Decimal] = mapped_column(
Numeric(10, 2)
)
customer_id: Mapped[int] = mapped_column(
ForeignKey("customers.id")
)
customer: Mapped["Customer"] = relationship(
back_populates="orders"
)
# Create one customer with two orders.
customer = Customer(name="Sara")
customer.orders = [
Order(total=Decimal("45.00")),
Order(total=Decimal("25.50"))
]
print(customer.name)
for order in customer.orders:
print(order.total)
Output:
Sara
45.00
25.50
Output Explanation:
The customer owns a list containing two Order objects. Each order will store the customer’s ID in its foreign-key column.
42.12 Many-to-Many Relationships
A many-to-many relationship connects many records from one table to many records from another table. Students can join many courses, and every course can contain many students. A separate association table stores pairs of foreign keys. SQLAlchemy uses the association table as the secondary table in the relationship.
Example: Students and courses
# Import SQLAlchemy tools.
from typing import List
from sqlalchemy import (
Table,
Column,
ForeignKey,
String
)
from sqlalchemy.orm import (
Mapped,
mapped_column,
relationship
)
# Create the association table.
student_courses = Table(
"student_courses",
Base.metadata,
Column(
"student_id",
ForeignKey("students.id"),
primary_key=True
),
Column(
"course_id",
ForeignKey("courses.id"),
primary_key=True
)
)
class Student(Base):
__tablename__ = "students"
id: Mapped[int] = mapped_column(
primary_key=True
)
name: Mapped[str] = mapped_column(
String(100)
)
courses: Mapped[List["Course"]] = relationship(
secondary=student_courses,
back_populates="students"
)
class Course(Base):
__tablename__ = "courses"
id: Mapped[int] = mapped_column(
primary_key=True
)
title: Mapped[str] = mapped_column(
String(150)
)
students: Mapped[List["Student"]] = relationship(
secondary=student_courses,
back_populates="courses"
)
# Create students and courses.
python_course = Course(title="Python")
database_course = Course(title="Databases")
student = Student(
name="Michael",
courses=[
python_course,
database_course
]
)
print(student.name)
for course in student.courses:
print(course.title)
Output:
Michael
Python
Databases
Output Explanation:
The student is connected to two courses. The association table will contain one row for each student-and-course connection.
42.13 Querying Data
SQLAlchemy ORM queries are commonly created with the select() function. The statement is passed to session.execute() or session.scalars(). The scalars() method is useful when selecting complete model objects because it returns the mapped objects directly instead of database row wrappers.
Example: Query all products
# Import required tools.
from sqlalchemy import select
from sqlalchemy.orm import Session
with Session(engine) as session:
# Build a query for Product objects.
statement = select(Product)
# Execute the query and return model objects.
products = session.scalars(statement).all()
# Display every product.
for product in products:
print(
product.id,
product.name,
product.price,
product.quantity
)
Example Output:
1 Keyboard 49.99 10
2 Mouse 24.50 25
3 Monitor 189.00 8
Output Explanation:
Each item returned by session.scalars() is a Product object. Its database values can be accessed through normal Python attributes.
42.14 Filtering and Sorting
Filtering limits query results by adding conditions with where(). Sorting uses order_by(). Conditions can compare numbers, text, dates, and Boolean values. Several conditions may be combined. The database performs filtering and sorting before returning records, which is normally more efficient than loading every row and filtering it in Python.
Example: Filter and sort products
# Import select.
from sqlalchemy import select
from sqlalchemy.orm import Session
with Session(engine) as session:
# Find products below $100 that have inventory.
statement = (
select(Product)
.where(Product.price < 100)
.where(Product.quantity > 0)
.order_by(Product.price.desc())
)
products = session.scalars(statement).all()
for product in products:
print(
f"{product.name}: "
f"${product.price:.2f}"
)
Example Output:
Keyboard: $49.99
Mouse: $24.50
Output Explanation:
Products costing $100 or more are excluded. The remaining products are sorted from the highest price to the lowest price.
42.15 Transactions
A transaction groups several database changes so they succeed or fail together. SQLAlchemy sessions begin transactions automatically when database work starts. Developers can call commit() after successful work and rollback() after errors. The session.begin() context manager provides a convenient transaction block with automatic commit or rollback behavior.
Example: Transfer product inventory
# Import Session.
from sqlalchemy.orm import Session
try:
with Session(engine) as session:
# Start a managed transaction.
with session.begin():
first_product = session.get(
Product,
1
)
second_product = session.get(
Product,
2
)
transfer_amount = 3
# Validate the available quantity.
if first_product.quantity < transfer_amount:
raise ValueError(
"Not enough inventory."
)
# Update both objects.
first_product.quantity -= transfer_amount
second_product.quantity += transfer_amount
print("Transaction completed.")
except ValueError as error:
print("Transaction cancelled:", error)
Possible Output:
Transaction completed.
Output Explanation:
Both quantity changes are committed together. If validation or a database operation fails, the transaction is rolled back and neither partial change is saved.
42.16 Alembic Migrations
Alembic is a migration tool commonly used with SQLAlchemy. A migration records a controlled database-structure change, such as creating a table or adding a column. Instead of deleting and rebuilding a production database, developers create ordered migration files. Alembic can also compare SQLAlchemy metadata with the current database and generate suggested changes.
Example: Install and initialize Alembic
# Install Alembic.
python -m pip install alembic
# Create Alembic configuration files.
alembic init migrations
Possible Output:
Creating directory migrations
Creating directory migrations/versions
Generating alembic.ini
Generating migrations/env.py
Please edit configuration before proceeding.
Output Explanation:
Alembic creates a migrations folder, an environment file, a versions folder, and a main configuration file.
Example: Create and apply a migration
# Create a migration by comparing model metadata.
alembic revision --autogenerate -m "create products table"
# Apply every available migration.
alembic upgrade head
Possible Output:
Generating migrations/versions/abc123_create_products_table.py
Running upgrade -> abc123, create products table
Output Explanation:
The first command creates a migration file. The second command runs the migration and updates the database to the latest revision.
Example: Migration file
# Import Alembic and SQLAlchemy.
from alembic import op
import sqlalchemy as sa
# Apply the migration.
def upgrade():
op.add_column(
"products",
sa.Column(
"description",
sa.String(length=500),
nullable=True
)
)
# Reverse the migration.
def downgrade():
op.drop_column(
"products",
"description"
)
42.17 Django ORM Introduction
Django includes its own ORM as part of the Django web framework. Django models inherit from models.Model, and model fields describe database columns. After defining or changing models, developers normally create and apply migrations. Django provides a manager named objects for creating, retrieving, filtering, updating, and deleting records.
Example: Django Product model
# models.py
# Import Django's model tools.
from django.db import models
class Product(models.Model):
# Store the product name.
name = models.CharField(
max_length=150
)
# Store a two-decimal price.
price = models.DecimalField(
max_digits=10,
decimal_places=2
)
# Store the available quantity.
quantity = models.PositiveIntegerField(
default=0
)
# Define readable object text.
def __str__(self):
return self.name
Example: Create migrations
python manage.py makemigrations
python manage.py migrate
Example: Create and query a product
# Create a database record.
product = Product.objects.create(
name="Keyboard",
price="49.99",
quantity=10
)
# Retrieve products with available inventory.
products = Product.objects.filter(
quantity__gt=0
).order_by("name")
for product in products:
print(product.name, product.price)
Example Output:
Keyboard 49.99
Output Explanation:
Django creates a Product record and then filters for products whose quantity is greater than zero. The results are sorted by product name.
42.18 ORM Performance
ORMs make development easier, but inefficient ORM usage can create unnecessary queries or load too much information. Applications should select only needed records, use pagination for large result sets, add suitable database indexes, perform filtering in the database, use bulk operations when appropriate, and inspect the SQL generated for important queries.
Example: Limit returned records
# Import select.
from sqlalchemy import select
from sqlalchemy.orm import Session
with Session(engine) as session:
# Retrieve only the first ten products.
statement = (
select(Product)
.order_by(Product.name)
.limit(10)
)
products = session.scalars(statement).all()
print("Products returned:", len(products))
for product in products:
print(product.name)
Possible Output:
Products returned: 3
Keyboard
Monitor
Mouse
Output Explanation:
The database returns no more than ten rows. Limiting results can reduce memory use and response time when a table contains many records.
Example: Select only required columns
with Session(engine) as session:
# Select only names and prices.
statement = select(
Product.name,
Product.price
)
rows = session.execute(statement).all()
for name, price in rows:
print(name, price)
Example Output:
Keyboard 49.99
Mouse 24.50
Monitor 189.00
Output Explanation:
This query retrieves only two columns instead of creating full Product objects with every mapped column.
42.19 Avoiding N+1 Queries
The N+1 problem occurs when one query loads parent records and an additional query runs for every parent to load related records. For example, loading ten customers and then separately loading each customer’s orders may create eleven queries. Eager-loading techniques can retrieve related information using fewer database operations.
Example: Code that may cause N+1 queries
# Load customers first.
customers = session.scalars(
select(Customer)
).all()
# Accessing orders may run another query per customer.
for customer in customers:
print(customer.name)
for order in customer.orders:
print(order.total)
Possible Query Count:
1 query for customers
10 additional queries for 10 customers
11 total queries
Output Explanation:
Lazy relationship loading can run one extra query whenever each customer’s orders collection is first accessed.
Example: Use selectinload()
# Import eager-loading support.
from sqlalchemy import select
from sqlalchemy.orm import selectinload
# Load customers and their orders efficiently.
statement = (
select(Customer)
.options(
selectinload(Customer.orders)
)
)
customers = session.scalars(statement).all()
for customer in customers:
print(customer.name)
for order in customer.orders:
print(order.total)
Possible Query Count:
1 query for customers
1 query for all related orders
2 total queries
Output Explanation:
selectinload() collects the parent IDs and loads all related orders in another combined query instead of one query per customer.
Example: Use joinedload()
# Import joined eager loading.
from sqlalchemy.orm import joinedload
statement = (
select(Customer)
.options(
joinedload(Customer.orders)
)
)
# unique() removes repeated parent rows
# produced by the SQL join.
customers = (
session.scalars(statement)
.unique()
.all()
)
for customer in customers:
print(
customer.name,
len(customer.orders)
)
Example Output:
Sara 2
Ali 1
Output Explanation:
joinedload() can load customers and orders through a joined query. The unique() method prevents duplicate Customer objects in the final result.
42.20 Chapter Project
In this chapter project, you will build a course-enrollment application with SQLAlchemy ORM. It will store students, courses, and enrollments. The enrollment model connects students and courses while also storing an enrollment date and final grade. The project demonstrates models, sessions, CRUD operations, relationships, transactions, filtering, sorting, and eager loading.
Step 1: Create the project structure
course_manager/
│
├── app.py
├── database.py
├── models.py
├── services.py
└── course_manager.db
Step 2: Create database.py
# database.py
# Import SQLAlchemy tools.
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
# Create the SQLite engine.
engine = create_engine(
"sqlite:///course_manager.db",
echo=False
)
# Create a reusable session factory.
SessionLocal = sessionmaker(
bind=engine,
expire_on_commit=False
)
Step 3: Create models.py
# models.py
# Import date and typing support.
from datetime import date
from typing import List, Optional
# Import SQLAlchemy column tools.
from sqlalchemy import (
Date,
ForeignKey,
String,
UniqueConstraint
)
# Import SQLAlchemy ORM tools.
from sqlalchemy.orm import (
DeclarativeBase,
Mapped,
mapped_column,
relationship
)
# Create the model base.
class Base(DeclarativeBase):
pass
# Create the Student model.
class Student(Base):
__tablename__ = "students"
id: Mapped[int] = mapped_column(
primary_key=True
)
name: Mapped[str] = mapped_column(
String(100),
nullable=False
)
email: Mapped[str] = mapped_column(
String(255),
nullable=False,
unique=True,
index=True
)
enrollments: Mapped[List["Enrollment"]] = relationship(
back_populates="student",
cascade="all, delete-orphan"
)
def __repr__(self):
return (
f"Student(id={self.id}, "
f"name={self.name!r})"
)
# Create the Course model.
class Course(Base):
__tablename__ = "courses"
id: Mapped[int] = mapped_column(
primary_key=True
)
title: Mapped[str] = mapped_column(
String(150),
nullable=False
)
code: Mapped[str] = mapped_column(
String(30),
nullable=False,
unique=True,
index=True
)
enrollments: Mapped[List["Enrollment"]] = relationship(
back_populates="course",
cascade="all, delete-orphan"
)
def __repr__(self):
return (
f"Course(id={self.id}, "
f"code={self.code!r})"
)
# Create an association model.
class Enrollment(Base):
__tablename__ = "enrollments"
# Prevent duplicate student-course enrollment.
__table_args__ = (
UniqueConstraint(
"student_id",
"course_id",
name="uq_student_course"
),
)
id: Mapped[int] = mapped_column(
primary_key=True
)
student_id: Mapped[int] = mapped_column(
ForeignKey(
"students.id",
ondelete="CASCADE"
)
)
course_id: Mapped[int] = mapped_column(
ForeignKey(
"courses.id",
ondelete="CASCADE"
)
)
enrollment_date: Mapped[date] = mapped_column(
Date,
default=date.today
)
final_grade: Mapped[Optional[str]] = mapped_column(
String(5),
nullable=True
)
student: Mapped["Student"] = relationship(
back_populates="enrollments"
)
course: Mapped["Course"] = relationship(
back_populates="enrollments"
)
Step 4: Create services.py
# services.py
# Import SQLAlchemy query and loading tools.
from sqlalchemy import select
from sqlalchemy.orm import selectinload
# Import the session factory.
from database import SessionLocal
# Import project models.
from models import (
Student,
Course,
Enrollment
)
# Add a new student.
def add_student(name, email):
# Validate the supplied values.
name = name.strip()
email = email.strip().lower()
if not name:
raise ValueError(
"Student name cannot be empty."
)
if not email:
raise ValueError(
"Student email cannot be empty."
)
# Open a session.
with SessionLocal() as session:
# Start a transaction.
with session.begin():
student = Student(
name=name,
email=email
)
session.add(student)
# Return the generated ID.
return student.id
# Add a new course.
def add_course(title, code):
title = title.strip()
code = code.strip().upper()
if not title:
raise ValueError(
"Course title cannot be empty."
)
if not code:
raise ValueError(
"Course code cannot be empty."
)
with SessionLocal() as session:
with session.begin():
course = Course(
title=title,
code=code
)
session.add(course)
return course.id
# Enroll a student in a course.
def enroll_student(student_id, course_id):
with SessionLocal() as session:
with session.begin():
# Confirm that the student exists.
student = session.get(
Student,
student_id
)
if student is None:
raise ValueError(
"Student not found."
)
# Confirm that the course exists.
course = session.get(
Course,
course_id
)
if course is None:
raise ValueError(
"Course not found."
)
# Create the enrollment.
enrollment = Enrollment(
student=student,
course=course
)
session.add(enrollment)
return enrollment.id
# Update an enrollment grade.
def update_grade(enrollment_id, grade):
grade = grade.strip().upper()
with SessionLocal() as session:
with session.begin():
enrollment = session.get(
Enrollment,
enrollment_id
)
if enrollment is None:
raise ValueError(
"Enrollment not found."
)
enrollment.final_grade = grade
# Return all students.
def get_students():
with SessionLocal() as session:
statement = (
select(Student)
.order_by(Student.name)
)
return session.scalars(statement).all()
# Return all courses.
def get_courses():
with SessionLocal() as session:
statement = (
select(Course)
.order_by(Course.code)
)
return session.scalars(statement).all()
# Return a student with all related courses.
def get_student_details(student_id):
with SessionLocal() as session:
statement = (
select(Student)
.where(Student.id == student_id)
.options(
selectinload(
Student.enrollments
).selectinload(
Enrollment.course
)
)
)
return session.scalar(statement)
# Search courses by title or code.
def search_courses(search_text):
search_pattern = (
f"%{search_text.strip()}%"
)
with SessionLocal() as session:
statement = (
select(Course)
.where(
Course.title.ilike(
search_pattern
)
|
Course.code.ilike(
search_pattern
)
)
.order_by(Course.code)
)
return session.scalars(statement).all()
# Delete an enrollment.
def delete_enrollment(enrollment_id):
with SessionLocal() as session:
with session.begin():
enrollment = session.get(
Enrollment,
enrollment_id
)
if enrollment is None:
raise ValueError(
"Enrollment not found."
)
session.delete(enrollment)
Step 5: Create app.py
# app.py
# Import SQLAlchemy database errors.
from sqlalchemy.exc import IntegrityError
# Import the engine.
from database import engine
# Import model metadata.
from models import Base
# Import service functions.
from services import (
add_student,
add_course,
enroll_student,
update_grade,
get_students,
get_courses,
get_student_details,
search_courses,
delete_enrollment
)
# Display the application menu.
def show_menu():
print("\nCourse Enrollment Manager")
print("1. Add student")
print("2. Add course")
print("3. List students")
print("4. List courses")
print("5. Enroll student")
print("6. View student details")
print("7. Search courses")
print("8. Update final grade")
print("9. Delete enrollment")
print("10. Exit")
# Display students.
def display_students():
students = get_students()
if not students:
print("No students were found.")
return
for student in students:
print(
f"{student.id}: "
f"{student.name} | "
f"{student.email}"
)
# Display courses.
def display_courses(courses=None):
if courses is None:
courses = get_courses()
if not courses:
print("No courses were found.")
return
for course in courses:
print(
f"{course.id}: "
f"{course.code} | "
f"{course.title}"
)
# Display one student's full information.
def display_student_details(student_id):
student = get_student_details(
student_id
)
if student is None:
print("Student not found.")
return
print("\nStudent Information")
print("ID:", student.id)
print("Name:", student.name)
print("Email:", student.email)
print("\nEnrollments")
if not student.enrollments:
print("The student has no enrollments.")
return
for enrollment in student.enrollments:
grade = (
enrollment.final_grade
if enrollment.final_grade
else "Not assigned"
)
print(
f"Enrollment {enrollment.id}: "
f"{enrollment.course.code} - "
f"{enrollment.course.title} | "
f"Date: {enrollment.enrollment_date} | "
f"Grade: {grade}"
)
# Run the main application.
def main():
# Create all database tables.
Base.metadata.create_all(engine)
while True:
show_menu()
choice = input(
"Choose an option: "
).strip()
try:
if choice == "1":
name = input(
"Student name: "
)
email = input(
"Student email: "
)
student_id = add_student(
name,
email
)
print(
f"Student created with "
f"ID {student_id}."
)
elif choice == "2":
title = input(
"Course title: "
)
code = input(
"Course code: "
)
course_id = add_course(
title,
code
)
print(
f"Course created with "
f"ID {course_id}."
)
elif choice == "3":
display_students()
elif choice == "4":
display_courses()
elif choice == "5":
student_id = int(
input("Student ID: ")
)
course_id = int(
input("Course ID: ")
)
enrollment_id = enroll_student(
student_id,
course_id
)
print(
f"Enrollment created with "
f"ID {enrollment_id}."
)
elif choice == "6":
student_id = int(
input("Student ID: ")
)
display_student_details(
student_id
)
elif choice == "7":
search_text = input(
"Search course: "
)
courses = search_courses(
search_text
)
display_courses(courses)
elif choice == "8":
enrollment_id = int(
input("Enrollment ID: ")
)
grade = input(
"Final grade: "
)
update_grade(
enrollment_id,
grade
)
print("Grade updated.")
elif choice == "9":
enrollment_id = int(
input("Enrollment ID: ")
)
delete_enrollment(
enrollment_id
)
print("Enrollment deleted.")
elif choice == "10":
print("Goodbye.")
break
else:
print("Invalid option.")
except ValueError as error:
print("Input error:", error)
except IntegrityError:
print(
"The operation created a duplicate "
"or invalid database relationship."
)
# Start the program.
if __name__ == "__main__":
main()
Step 6: Run the project
python app.py
Example Output:
Course Enrollment Manager
1. Add student
2. Add course
3. List students
4. List courses
5. Enroll student
6. View student details
7. Search courses
8. Update final grade
9. Delete enrollment
10. Exit
Choose an option: 1
Student name: Michael
Student email: michael@example.com
Student created with ID 1.
Choose an option: 2
Course title: Python Programming
Course code: PY101
Course created with ID 1.
Choose an option: 2
Course title: Database Fundamentals
Course code: DB101
Course created with ID 2.
Choose an option: 5
Student ID: 1
Course ID: 1
Enrollment created with ID 1.
Choose an option: 5
Student ID: 1
Course ID: 2
Enrollment created with ID 2.
Choose an option: 8
Enrollment ID: 1
Final grade: A
Grade updated.
Choose an option: 6
Student ID: 1
Student Information
ID: 1
Name: Michael
Email: michael@example.com
Enrollments
Enrollment 1: PY101 - Python Programming | Date: 2026-07-19 | Grade: A
Enrollment 2: DB101 - Database Fundamentals | Date: 2026-07-19 | Grade: Not assigned
Output Explanation:
The application creates one student, two courses, and two enrollment records. The association model stores extra information, including the enrollment date and final grade. Eager loading retrieves the student, enrollments, and courses efficiently.
Project Summary
This project demonstrates SQLAlchemy models, typed mapped columns, primary keys, unique constraints, one-to-many relationships, an association model, database sessions, transactions, CRUD operations, filtering, sorting, validation, error handling, and eager loading with selectinload().
