ORM with SQLAlchemy

What is an ORM?

An ORM (Object-Relational Mapping) is like a translator between your Python objects and database tables. Instead of writing raw SQL, you work with Python classes and objects.

Think of it as a bridge that converts your Python code into database operations automatically.

Why Use SQLAlchemy?

Productivity

Write less code - focus on your application logic instead of SQL syntax.

Database Agnostic

The same code can work with different database systems (SQLite, PostgreSQL, MySQL, etc.).

Type Safety

Python's type system helps catch errors before they reach the database.

Complex Queries Made Easy

Handle complex relationships and queries with simple Python code.

Basic SQLAlchemy Setup

Installation

pip install sqlalchemy

Creating an Engine

from sqlalchemy import create_engine

# For SQLite
engine = create_engine('sqlite:///example.db')

# For PostgreSQL
# engine = create_engine('postgresql://user:password@localhost/dbname')

Defining Models

from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

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

Creating Tables

Base.metadata.create_all(engine)

Basic CRUD Operations

Creating a Session

from sqlalchemy.orm import sessionmaker

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

Adding Records

new_user = User(name='John Doe', email='john@example.com', age=30)
session.add(new_user)
session.commit()

Querying Records

# Get all users
users = session.query(User).all()

# Get user by id
user = session.query(User).get(1)

# Filter users
adults = session.query(User).filter(User.age >= 18).all()

Updating Records

user = session.query(User).get(1)
user.age = 31
session.commit()

Deleting Records

user = session.query(User).get(1)
session.delete(user)
session.commit()

Relationships

One-to-Many

class Author(Base):
    __tablename__ = 'authors'
    id = Column(Integer, primary_key=True)
    name = Column(String(100))
    books = relationship('Book', back_populates='author')

class Book(Base):
    __tablename__ = 'books'
    id = Column(Integer, primary_key=True)
    title = Column(String(200))
    author_id = Column(Integer, ForeignKey('authors.id'))
    author = relationship('Author', back_populates='books')

Best Practices

Always Close Sessions

session.close()

Use Context Managers

with Session() as session:
    # database operations

Handle Transactions Properly

SQLAlchemy manages transactions automatically, but be aware of when commits happen.

Building a User System Example

Building a blog system with SQLAlchemy:

• User model for authors
• Post model for blog entries
• Comment model for user comments
• Relationships between posts and comments

The ORM handles all the complex SQL joins and relationships for you.

When to Use SQLAlchemy

• Complex applications with many database operations
• Applications that might switch database systems
• Projects requiring complex relationships
• When you want to write Pythonic database code

For simple applications, raw SQL or even SQLite might be sufficient. Choose the right tool for your project's complexity.