SQLAlchemy ORM (Models, Relationships, Aggregates) - Python Tutorial for Beginners #36

SQLAlchemy ORM: Models, Relationships, and Aggregates in Python

Discover how to use SQLAlchemy's Object Relational Mapper (ORM) to map Python classes to database tables, define relationships between models, and perform aggregate queries. This guide covers creating engines, sessions, and building a project tracker application with one-to-many relationships and data aggregation.

Code

from sqlalchemy import create_engine, Column, Integer, String, ForeignKey, func
from sqlalchemy.orm import declarative_base, relationship, sessionmaker

# Define the base class for models
Base = declarative_base()

# Define Project model
class Project(Base):
  __tablename__ = 'projects'
  id = Column(Integer, primary_key=True)
  name = Column(String, nullable=False)
  tasks = relationship('Task', back_populates='project')  # One-to-many relationship

# Define Task model
class Task(Base):
  __tablename__ = 'tasks'
  id = Column(Integer, primary_key=True)
  description = Column(String, nullable=False)
  project_id = Column(Integer, ForeignKey('projects.id'))
  project = relationship('Project', back_populates='tasks')

# Create SQLite engine and session
engine = create_engine('sqlite:///tracker.db', echo=True)
Session = sessionmaker(bind=engine)
session = Session()

# Create tables
Base.metadata.create_all(engine)

# Add a project and tasks
new_project = Project(name='Build Website')
new_project.tasks = [
  Task(description='Design homepage'),
  Task(description='Implement backend'),
  Task(description='Write tests')
]
session.add(new_project)
session.commit()

# Query projects and their tasks
project = session.query(Project).filter_by(name='Build Website').first()
for task in project.tasks:
  print(f'Task: {task.description}')

# Aggregate: count tasks per project
task_counts = (
  session.query(Project.name, func.count(Task.id))
  .join(Task)
  .group_by(Project.id)
  .all()
)
for project_name, count in task_counts:
  print(f'Project "{project_name}" has {count} tasks')

Key Points

• Use declarative_base() to define Python classes that map to database tables.
• Establish one-to-many relationships with ForeignKey and relationship(), using back_populates for bidirectional access.
• Create a session with sessionmaker to add, query, update, and delete records.
• Perform aggregate queries using func.count(), func.sum(), and join tables with .join() and .group_by().
• Organize ORM operations into reusable functions for cleaner, maintainable code.