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=Trueenables 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