Using ORM with SQLAlchemy: Complete Beginner to Advanced Guide

Table of Contents

• Introduction
• What is an ORM and Why Use It?
• Introduction to SQLAlchemy
• Installing SQLAlchemy
• Setting Up a Database with SQLAlchemy
• Core Concepts of SQLAlchemy ORM
  • Declarative Base
  • Mapping Classes to Tables
• Creating Tables and Models
• Performing CRUD Operations Using ORM
  • Inserting Records
  • Querying Records
  • Updating Records
  • Deleting Records
• Relationships: One-to-Many, Many-to-One, Many-to-Many
• Advanced Query Techniques
• SQLAlchemy Sessions Explained
• Best Practices for Using SQLAlchemy ORM
• Conclusion

Introduction

Managing relational databases in Python traditionally involved writing raw SQL queries.
While effective, it often led to tedious, error-prone, and hard-to-maintain code.

An Object Relational Mapper (ORM) bridges the gap by allowing developers to interact with databases using Python classes and objects instead of raw SQL statements.
SQLAlchemy is one of the most powerful and flexible ORM libraries available for Python.

In this guide, we will cover everything you need to know about using SQLAlchemy’s ORM features, from basics to advanced techniques.

What is an ORM and Why Use It?

An ORM (Object Relational Mapper) allows you to:

• Represent database tables as Python classes.
• Represent rows as instances of classes.
• Query and manipulate database records using Python syntax.
• Abstract away raw SQL for most common operations.

Advantages of Using an ORM:

• Productivity: Write less code and avoid repetitive SQL.
• Maintainability: Code is easier to read and modify.
• Security: Reduces the risk of SQL injection attacks.
• Portability: Easily switch databases without major rewrites.

Introduction to SQLAlchemy

SQLAlchemy is a comprehensive Python library for database interaction.
It offers two primary layers:

• Core: Direct SQL expression language.
• ORM: Higher-level abstraction over Core to map tables to Python classes.

In this module, our focus is on SQLAlchemy ORM.

Installing SQLAlchemy

You can install SQLAlchemy using pip:

pip install SQLAlchemy

To install optional drivers (like for PostgreSQL, MySQL):

pip install psycopg2-binary  # for PostgreSQL
pip install pymysql          # for MySQL

Setting Up a Database with SQLAlchemy

Start by creating an engine, which is the starting point for any database interaction:

from sqlalchemy import create_engine

# Create an engine for an SQLite database
engine = create_engine('sqlite:///mydatabase.db', echo=True)

• 'sqlite:///mydatabase.db' means using a SQLite database file.
• echo=True enables SQL query logging for debugging.

For other databases, connection strings differ, for example:

# PostgreSQL
engine = create_engine('postgresql+psycopg2://username:password@localhost/mydatabase')

# MySQL
engine = create_engine('mysql+pymysql://username:password@localhost/mydatabase')

Core Concepts of SQLAlchemy ORM

Declarative Base

The declarative base class is used to declare models that map to database tables.

from sqlalchemy.orm import declarative_base

Base = declarative_base()

Mapping Classes to Tables

Each class represents a database table.
Each attribute represents a column.

Example:

from sqlalchemy import Column, Integer, String

class User(Base):
    __tablename__ = 'users'
    
    id = Column(Integer, primary_key=True)
    name = Column(String)
    email = Column(String, unique=True)

Creating Tables and Models

After defining your models, create the tables:

Base.metadata.create_all(engine)

This will create all tables based on the classes inheriting from Base.

Performing CRUD Operations Using ORM

Before performing any operations, you need to create a Session:

from sqlalchemy.orm import sessionmaker

Session = sessionmaker(bind=engine)
session = Session()

Inserting Records

new_user = User(name="Alice", email="[email protected]")
session.add(new_user)
session.commit()

Adding multiple records:

users = [
    User(name="Bob", email="[email protected]"),
    User(name="Charlie", email="[email protected]")
]
session.add_all(users)
session.commit()

Querying Records

Query all users:

users = session.query(User).all()
for user in users:
    print(user.name, user.email)

Query with filters:

user = session.query(User).filter_by(name="Alice").first()
print(user.email)

Advanced filter:

from sqlalchemy import or_

users = session.query(User).filter(or_(User.name == 'Alice', User.name == 'Bob')).all()

Updating Records

user = session.query(User).filter_by(name="Alice").first()
user.email = "[email protected]"
session.commit()

Deleting Records

user = session.query(User).filter_by(name="Charlie").first()
session.delete(user)
session.commit()

Relationships: One-to-Many, Many-to-One, Many-to-Many

SQLAlchemy handles relationships elegantly using relationship() and ForeignKey.

One-to-Many Example

from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship

class Department(Base):
    __tablename__ = 'departments'
    
    id = Column(Integer, primary_key=True)
    name = Column(String)
    employees = relationship("Employee", back_populates="department")

class Employee(Base):
    __tablename__ = 'employees'
    
    id = Column(Integer, primary_key=True)
    name = Column(String)
    department_id = Column(Integer, ForeignKey('departments.id'))
    department = relationship("Department", back_populates="employees")

This allows easy navigation:

# Access employees of a department
dept = session.query(Department).first()
for emp in dept.employees:
    print(emp.name)

# Access department of an employee
emp = session.query(Employee).first()
print(emp.department.name)

Advanced Query Techniques

• Sorting:

from sqlalchemy import desc

users = session.query(User).order_by(desc(User.name)).all()

• Pagination:

users = session.query(User).limit(10).offset(20).all()

• Joining Tables:

session.query(Employee).join(Department).filter(Department.name == "IT").all()

SQLAlchemy Sessions Explained

• A Session is a workspace for your objects.
• It tracks changes and manages transactions.
• You must always commit() changes or rollback() if an error occurs.

Typical pattern:

session = Session()
try:
    # Perform operations
    session.commit()
except:
    session.rollback()
    raise
finally:
    session.close()

Best Practices for Using SQLAlchemy ORM

• Always close sessions properly to avoid connection leaks.
• Use session context managers or scoped sessions for multithreading.
• Use indexes on frequently queried columns.
• Use lazy loading cautiously with relationships to optimize performance.
• Validate data before committing to avoid database integrity errors.

Conclusion

SQLAlchemy ORM offers a powerful and elegant way to interact with relational databases through Python classes and objects.
You have now learned:

• How to define models
• How to create tables
• How to perform CRUD operations
• How to model relationships
• How to execute advanced queries