Python ORM Basics

Introduction

When building applications that store data in relational databases, developers often find themselves constantly switching between two very different paradigms: object-oriented programming in Python and relational data in SQL. This context-switching can be cumbersome and error-prone. This is where Object-Relational Mapping (ORM) comes to the rescue!

An ORM is a programming technique that converts data between incompatible type systems in object-oriented programming languages (like Python) and relational databases. In simpler terms, an ORM allows you to interact with your database, like you would with SQL, except you use the object-oriented paradigm of your preferred programming language.

In this tutorial, you'll learn:

• What is an ORM and why use it
• Popular Python ORMs
• Basic ORM operations with SQLAlchemy
• How ORMs compare to raw SQL

Why Use an ORM?

Before diving into specific ORM implementations, let's understand why you might want to use one:

1. Write Python, not SQL: Work with familiar Python objects rather than writing SQL strings
2. Database agnosticism: Easily switch between different database systems with minimal code changes
3. Security: Reduced risk of SQL injection attacks
4. DRY code: Avoids repetitive SQL statements for common operations
5. Object-oriented: Work with data as objects with methods and properties

Popular Python ORMs

Python has several excellent ORM libraries. The most popular ones include:

• SQLAlchemy: A powerful and flexible ORM that offers both high-level ORM functionality and lower-level database access
• Django ORM: Part of the Django web framework, tightly integrated with Django's models
• Peewee: A lightweight alternative to SQLAlchemy with a simpler API
• Pony ORM: Provides an intuitive generator-based query syntax
• SQLObject: One of the oldest Python ORMs, though less actively maintained now

For this tutorial, we'll focus on SQLAlchemy as it's widely used and provides a good balance of power and usability.

Getting Started with SQLAlchemy

Let's begin by installing SQLAlchemy:

bash

pip install sqlalchemy

Setting Up a Database Connection

First, we need to establish a connection to our database:

python

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

# Create a connection to the database
# Here we're using SQLite as it doesn't require additional setup
engine = create_engine('sqlite:///example.db', echo=True)

# Create a base class for our models
Base = declarative_base()

# Create a session factory
Session = sessionmaker(bind=engine)

The echo=True parameter tells SQLAlchemy to log all the SQL it executes, which is useful for learning and debugging.

Defining Models

With SQLAlchemy, we define database tables as Python classes:

python

from sqlalchemy import Column, Integer, String, Float, ForeignKey
from sqlalchemy.orm import relationship

class Product(Base):
    __tablename__ = 'products'
    
    id = Column(Integer, primary_key=True)
    name = Column(String(100), nullable=False)
    price = Column(Float, nullable=False)
    description = Column(String(500))
    
    def __repr__(self):
        return f"<Product(name='{self.name}', price={self.price})>"

class Category(Base):
    __tablename__ = 'categories'
    
    id = Column(Integer, primary_key=True)
    name = Column(String(100), nullable=False)
    
    # Define relationship with Product
    products = relationship("Product", secondary="product_category")
    
    def __repr__(self):
        return f"<Category(name='{self.name}')>"

class ProductCategory(Base):
    __tablename__ = 'product_category'
    
    product_id = Column(Integer, ForeignKey('products.id'), primary_key=True)
    category_id = Column(Integer, ForeignKey('categories.id'), primary_key=True)

After defining our models, we need to create the tables in the database:

python

Base.metadata.create_all(engine)

Basic CRUD Operations

Let's look at how to perform Create, Read, Update, and Delete (CRUD) operations with our ORM.

Create

To add new records to the database:

python

# Create a session
session = Session()

# Create new products
laptop = Product(name='Laptop', price=999.99, description='Powerful laptop for developers')
phone = Product(name='Smartphone', price=499.99, description='Latest smartphone model')

# Create new categories
electronics = Category(name='Electronics')
gadgets = Category(name='Gadgets')

# Add objects to session
session.add(laptop)
session.add(phone)
session.add(electronics)
session.add(gadgets)

# Commit the transaction
session.commit()

print("Products and categories created!")

Output:

Products and categories created!

Establish Relationships

Let's associate our products with categories:

python

# Add products to categories
electronics.products.append(laptop)
electronics.products.append(phone)
gadgets.products.append(phone)

# Commit the changes
session.commit()

print("Relationships established!")

Output:

Relationships established!

Read

Querying data is straightforward:

python

# Get all products
all_products = session.query(Product).all()
print("All products:")
for product in all_products:
    print(f"- {product.name}: ${product.price}")

# Get a specific product
laptop = session.query(Product).filter_by(name='Laptop').first()
print(f"\nFound laptop: {laptop.name}, ${laptop.price}")

# More complex queries
expensive_products = session.query(Product).filter(Product.price > 500).all()
print("\nExpensive products:")
for product in expensive_products:
    print(f"- {product.name}: ${product.price}")

Output:

All products:
- Laptop: $999.99
- Smartphone: $499.99

Found laptop: Laptop, $999.99

Expensive products:
- Laptop: $999.99

Update

Updating records is simple - just modify the object attributes and commit:

python

# Update a product's price
laptop = session.query(Product).filter_by(name='Laptop').first()
laptop.price = 899.99
session.commit()

# Verify the update
updated_laptop = session.query(Product).filter_by(name='Laptop').first()
print(f"Updated laptop price: ${updated_laptop.price}")

Output:

Updated laptop price: $899.99

Delete

To remove records:

python

# Find the product to delete
phone = session.query(Product).filter_by(name='Smartphone').first()

# Remove it
session.delete(phone)
session.commit()

# Verify it's gone
remaining_products = session.query(Product).all()
print("Remaining products:")
for product in remaining_products:
    print(f"- {product.name}")

Output:

Remaining products:
- Laptop

More Advanced Queries

SQLAlchemy provides powerful query capabilities:

python

from sqlalchemy import desc, and_, or_, not_

# Order products by price (descending)
ordered_products = session.query(Product).order_by(desc(Product.price)).all()

# Using logical operators
filtered_products = session.query(Product).filter(
    and_(
        Product.price < 1000,
        Product.price > 100
    )
).all()

# Joins
products_with_categories = session.query(Product, Category)\
    .join(ProductCategory, Product.id == ProductCategory.product_id)\
    .join(Category, Category.id == ProductCategory.category_id)\
    .all()

# Count results
product_count = session.query(Product).count()
print(f"Total products: {product_count}")

Real-world Application: Product Inventory System

Let's put it all together in a more complete example of a product inventory system:

python

from sqlalchemy import create_engine, Column, Integer, String, Float, DateTime, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, sessionmaker
import datetime

# Setup
engine = create_engine('sqlite:///inventory.db', echo=False)
Base = declarative_base()
Session = sessionmaker(bind=engine)

# Define models
class Product(Base):
    __tablename__ = 'products'
    
    id = Column(Integer, primary_key=True)
    name = Column(String(100), nullable=False)
    price = Column(Float, nullable=False)
    stock = Column(Integer, default=0)
    created_at = Column(DateTime, default=datetime.datetime.utcnow)
    
    # Relationship with OrderItem
    order_items = relationship("OrderItem", back_populates="product")
    
    def __repr__(self):
        return f"<Product(name='{self.name}', price=${self.price}, stock={self.stock})>"

class Customer(Base):
    __tablename__ = 'customers'
    
    id = Column(Integer, primary_key=True)
    name = Column(String(100), nullable=False)
    email = Column(String(100), unique=True)
    
    # Relationship with Order
    orders = relationship("Order", back_populates="customer")
    
    def __repr__(self):
        return f"<Customer(name='{self.name}', email='{self.email}')>"

class Order(Base):
    __tablename__ = 'orders'
    
    id = Column(Integer, primary_key=True)
    customer_id = Column(Integer, ForeignKey('customers.id'), nullable=False)
    order_date = Column(DateTime, default=datetime.datetime.utcnow)
    
    # Relationships
    customer = relationship("Customer", back_populates="orders")
    items = relationship("OrderItem", back_populates="order")
    
    def __repr__(self):
        return f"<Order(id={self.id}, customer='{self.customer.name}', date={self.order_date})>"
        
    @property
    def total(self):
        return sum(item.product.price * item.quantity for item in self.items)

class OrderItem(Base):
    __tablename__ = 'order_items'
    
    id = Column(Integer, primary_key=True)
    order_id = Column(Integer, ForeignKey('orders.id'), nullable=False)
    product_id = Column(Integer, ForeignKey('products.id'), nullable=False)
    quantity = Column(Integer, default=1)
    
    # Relationships
    order = relationship("Order", back_populates="items")
    product = relationship("Product", back_populates="order_items")
    
    def __repr__(self):
        return f"<OrderItem(product='{self.product.name}', quantity={self.quantity})>"

# Create tables
Base.metadata.create_all(engine)

# Helper functions for our inventory system
def add_product(session, name, price, stock=0):
    product = Product(name=name, price=price, stock=stock)
    session.add(product)
    session.commit()
    return product

def add_customer(session, name, email):
    customer = Customer(name=name, email=email)
    session.add(customer)
    session.commit()
    return customer

def create_order(session, customer_id, product_quantities):
    # Check if customer exists
    customer = session.query(Customer).get(customer_id)
    if not customer:
        raise ValueError(f"No customer with ID {customer_id}")
    
    # Create new order
    order = Order(customer_id=customer_id)
    session.add(order)
    
    # Add items to order
    for product_id, quantity in product_quantities.items():
        product = session.query(Product).get(product_id)
        if not product:
            raise ValueError(f"No product with ID {product_id}")
            
        if product.stock < quantity:
            raise ValueError(f"Insufficient stock for {product.name}")
            
        # Reduce stock
        product.stock -= quantity
        
        # Add item to order
        order_item = OrderItem(order_id=order.id, product_id=product_id, quantity=quantity)
        session.add(order_item)
    
    session.commit()
    return order

# Demo of the inventory system
if __name__ == "__main__":
    session = Session()
    
    # Add products
    laptop = add_product(session, "Laptop Pro", 1299.99, 10)
    phone = add_product(session, "Smartphone X", 699.99, 20)
    headphones = add_product(session, "Wireless Headphones", 149.99, 30)
    
    print("Products added to inventory:")
    for product in session.query(Product).all():
        print(f"- {product}")
    
    # Add customers
    alice = add_customer(session, "Alice Smith", "[email protected]")
    bob = add_customer(session, "Bob Jones", "[email protected]")
    
    print("\nCustomers added:")
    for customer in session.query(Customer).all():
        print(f"- {customer}")
    
    # Create orders
    try:
        order1 = create_order(session, alice.id, {laptop.id: 1, headphones.id: 1})
        print(f"\nOrder created for {order1.customer.name}")
        print(f"Total: ${order1.total}")
        print("Items:")
        for item in order1.items:
            print(f"- {item.quantity}x {item.product.name} (${item.product.price} each)")
        
        # Check updated stock
        print("\nUpdated inventory:")
        for product in session.query(Product).all():
            print(f"- {product}")
    except ValueError as e:
        print(f"Error: {e}")
    
    session.close()

This example demonstrates how ORMs help you model complex relationships and business logic in your applications.

ORMs vs. Raw SQL

While ORMs offer many advantages, it's important to understand their trade-offs compared to raw SQL:

ORM	Raw SQL
✅ Object-oriented interface	⚠️ String manipulation for queries
✅ Database agnostic	⚠️ Database-specific syntax
✅ Protection from SQL injection	⚠️ Requires careful parameter handling
✅ Automates repetitive tasks	⚠️ Must write all SQL manually
⚠️ Can be slower for complex queries	✅ Maximum performance control
⚠️ Learning curve for ORM concepts	✅ Direct use of SQL knowledge

In practice, many projects use a combination of ORM for standard CRUD operations and raw SQL for complex or performance-critical queries.

Summary

In this tutorial, you've learned:

• What Object-Relational Mapping (ORM) is and why it's useful
• How to set up SQLAlchemy for database operations
• How to define models using Python classes
• How to perform CRUD operations using an ORM
• A real-world application with multiple related models
• How ORMs compare to raw SQL

ORMs provide a more Pythonic way to interact with databases, saving you from writing repetitive SQL and allowing you to focus on your application's business logic rather than database intricacies.

Additional Resources

To deepen your understanding of Python ORMs:

• SQLAlchemy Documentation
• Django ORM Documentation
• Peewee Documentation

Exercises

1. Add a "Category" model to the inventory system and establish a many-to-many relationship with Products
2. Create a function to generate a sales report showing the best-selling products
3. Implement a shopping cart system where items can be added before creating an order
4. Add validation to ensure that email addresses follow a valid format
5. Extend the inventory system to track product returns

Remember that ORMs are meant to make your life easier, but understanding the SQL they generate will help you write more efficient code as your applications grow.