Advanced Lesson

Using SQLite & ORMs (SQLAlchemy)

Master database operations with SQLite and SQLAlchemy ORM for building data-driven applications

📦 Real-World Analogy: Think of a database like a filing cabinet. SQLite is like having a single filing cabinet in your office (one file). PostgreSQL/MySQL is like a warehouse with filing cabinets that multiple people can access. An ORM (Object-Relational Mapper) is like having a personal assistant who fetches and organizes files for you — you just describe what you want, and they handle the filing system details.

This lesson covers database fundamentals, from raw SQLite operations to advanced ORM patterns with SQLAlchemy. You'll learn to build production-ready database layers for web apps, APIs, and data tools.

What Is SQLite?

SQLite is a file-based, zero-configuration database that's perfect for learning, prototyping, and small-to-medium applications.

✓ When to Use SQLite

Desktop applications
Prototypes & learning
Command-line tools
Mobile apps
Local caching
Small-to-medium web apps

✗ When Not to Use SQLite

High-concurrency writes
Multi-server setups
Complex sharding needs
Very large datasets (TB+)

Key benefits: No server setup, cross-platform, single file database, used by Chrome, VS Code, and countless apps.

Using SQLite Directly (sqlite3 Module)

Python includes sqlite3 built-in for direct database operations.

SQLite Basic Operations

Try it Yourself »

Python

import sqlite3

# Connect to in-memory database for demo
conn = sqlite3.connect(":memory:")
cursor = conn.cursor()

# Create a table
cursor.execute("""
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL UNIQUE,
    email TEXT NOT NULL
)
""")

# Insert data (use ? placeholders to prevent SQL injection!)
cursor.execute(
    "INSERT INTO users (username, email) VALUES (?, ?)",
    ("alice", "alice@example.com")
)

cursor.execute(
    "INSERT INTO users (userna
...

What Is an ORM?

🔄 Real-World Analogy: Imagine you're a business owner who speaks English, but your warehouse staff only speaks Spanish (SQL). An ORM is like a translator who converts your English instructions ("give me all customers from London") into Spanish (SQL queries). You work with familiar concepts; the translator handles the language barrier.

ORM (Object-Relational Mapper) maps database tables to Python classes and rows to objects.

Database Concept	ORM Equivalent	Example
Table	Python Class	`class User`
Row	Object Instance	`user = User(name="Alice")`
Column	Class Attribute	`name = Column(String)`
Foreign Key	Relationship	`posts = relationship("Post")`

Without ORM (Raw SQL):

cursor.execute("INSERT INTO users (username, email) VALUES (?, ?)", ("alice", "alice@example.com"))

With ORM (SQLAlchemy):

user = User(username="alice", email="alice@example.com")
session.add(user)
session.commit()

Benefits of ORMs:

Safer: Prevents SQL injection when used properly
Refactor-friendly: Change schema in one place
Database-agnostic: Switch from SQLite to PostgreSQL easily
More Pythonic: Use Python expressions instead of SQL strings
Relationships: Handle foreign keys and joins automatically

Setting Up SQLAlchemy

Install SQLAlchemy and create the basic infrastructure for database operations.

SQLAlchemy Setup

Try it Yourself »

Python

# Note: Install with: pip install sqlalchemy

# This example shows the structure - run locally with SQLAlchemy installed

print("SQLAlchemy Setup Example")
print("=" * 40)
print("""
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import sessionmaker, DeclarativeBase

# 1. Define base class for all models
class Base(DeclarativeBase):
    pass

# 2. Create engine (connects to SQLite file)
engine = create_engine(
    "sqlite:///app.db",
    echo=True  # Print SQL q
...

Basic CRUD Operations

Create, Read, Update, and Delete operations using SQLAlchemy ORM.

CRUD Operations

Try it Yourself »

Python

# Simulated CRUD operations (run locally with SQLAlchemy)

print("=== CREATE ===")
print("with get_session() as session:")
print("    user1 = User(username='alice', email='alice@example.com')")
print("    user2 = User(username='bob', email='bob@example.com')")
print("    session.add_all([user1, user2])")
print("Users created!")

print("\n=== READ ===")
print("with get_session() as session:")
print("    all_users = session.query(User).all()")
print("    alice = session.query(User).filter_by(usern
...

Defining Relationships (One-to-Many)

Model real-world connections between data with foreign keys and relationships.

One-to-Many Relationships

Try it Yourself »

Python

# Relationship example structure

print("One-to-Many Relationship: User -> Posts")
print("=" * 45)

print("""
class User(Base):
    __tablename__ = "users"
    
    id = Column(Integer, primary_key=True)
    username = Column(String(50), unique=True)
    
    # Relationship: one user has many posts
    posts = relationship("Post", back_populates="author")

class Post(Base):
    __tablename__ = "posts"
    
    id = Column(Integer, primary_key=True)
    title = Column(String(120))
    user_id = C
...

Advanced Query Patterns

Filtering, ordering, pagination, and complex queries with SQLAlchemy.

Query Patterns

Try it Yourself »

Python

print("=== FILTERING ===")
print("# Case-insensitive search")
print("session.query(User).filter(User.email.ilike('%example%')).all()")

print("\n# Multiple conditions (AND)")
print("session.query(User).filter(")
print("    and_(User.username == 'alice', User.email.like('%example%'))")
print(").first()")

print("\n=== ORDERING ===")
print("# Alphabetical")
print("session.query(User).order_by(User.username).all()")

print("\n# Reverse by ID")
print("session.query(User).order_by(User.id.desc()).all
...

Many-to-Many Relationships

Handle complex relationships like posts with multiple tags using association tables.

Many-to-Many Relationships

Try it Yourself »

Python

print("Many-to-Many: Posts <-> Tags")
print("=" * 35)

print("""
# Association table
post_tags = Table(
    "post_tags", Base.metadata,
    Column("post_id", Integer, ForeignKey("posts.id")),
    Column("tag_id", Integer, ForeignKey("tags.id"))
)

class Tag(Base):
    __tablename__ = "tags"
    id = Column(Integer, primary_key=True)
    name = Column(String(50), unique=True)
    posts = relationship("Post", secondary=post_tags, back_populates="tags")

class Post(Base):
    __tablename__ = "posts
...

Performance Optimization

Eager loading, indexing, and query optimization to build fast applications.

Performance Tips

Try it Yourself »

Python

print("=== N+1 PROBLEM (SLOW) ===")
print("users = session.query(User).all()  # 1 query")
print("for user in users:")
print("    print(len(user.posts))  # N queries!")
print("")

print("=== EAGER LOADING (FAST) ===")
print("from sqlalchemy.orm import joinedload")
print("")
print("users = session.query(User).options(")
print("    joinedload(User.posts)")
print(").all()  # Single query with JOIN!")
print("")

print("=== INDEXING ===")
print("class User(Base):")
print("    username = Column(String(
...

Real-World Example: Notes Application

A complete CRUD application demonstrating practical ORM usage.

Notes App Demo

Try it Yourself »

Python

# Simulated Notes App Demo

class Note:
    def __init__(self, id, title, body):
        self.id = id
        self.title = title
        self.body = body

# Simulated database
notes_db = {}
next_id = 1

def create_note(title, body):
    global next_id
    note = Note(next_id, title, body)
    notes_db[next_id] = note
    next_id += 1
    return note.id

def get_all_notes():
    return list(notes_db.values())

def search_notes(query):
    query = query.lower()
    return [n for n in notes_db.valu
...

Production Project Structure

Organize your database code for maintainability and scalability.

Project Structure

Try it Yourself »

Python

print("""
Recommended project structure:
==============================

project/
├── app.db                  # SQLite database file
├── database.py             # Engine & session setup
├── models/
│   ├── __init__.py        # Export all models
│   ├── base.py            # Base class
│   ├── user.py            # User model
│   └── post.py            # Post model
├── repositories/
│   ├── user_repo.py       # User CRUD operations
│   └── post_repo.py       # Post CRUD operations
├── services/
│  
...

Summary

You've learned comprehensive database development with SQLite and SQLAlchemy:

When to use SQLite vs other databases
Raw sqlite3 operations
Why ORMs exist and their benefits
Setting up SQLAlchemy with proper structure
Defining models and creating tables
CRUD operations with context managers
One-to-many and many-to-many relationships
Advanced query patterns (filtering, ordering, pagination)
Performance optimization (eager loading, indexing)
Real-world application structure
Production best practices

SQLAlchemy is the industry-standard ORM used in FastAPI, Flask, and countless Python applications. These patterns apply to any SQL database - simply change the connection string to switch from SQLite to PostgreSQL, MySQL, or others.

📋 Quick Reference — SQLite & ORMs

Syntax	What it does
sqlite3.connect('db.sqlite3')	Open/create a SQLite database
cursor.execute(sql, params)	Run parameterised SQL query
Base = declarative_base()	SQLAlchemy ORM base class
session.query(Model).filter()	Query records with ORM
session.commit()	Save pending changes to DB

🎉 Great work! You've completed this lesson.

You can now interact with databases using raw sqlite3 and SQLAlchemy ORM — the foundation of any data-driven Python application.

Up next: REST API Clients — build robust HTTP clients that consume real-world APIs.

Using SQLite & ORMs (SQLAlchemy)

What Is SQLite?

✓ When to Use SQLite

✗ When Not to Use SQLite

Using SQLite Directly (sqlite3 Module)

SQLite Basic Operations

What Is an ORM?

Without ORM (Raw SQL):

With ORM (SQLAlchemy):

Benefits of ORMs:

Setting Up SQLAlchemy

SQLAlchemy Setup

Basic CRUD Operations

CRUD Operations

Defining Relationships (One-to-Many)

One-to-Many Relationships

Advanced Query Patterns

Query Patterns

Many-to-Many Relationships

Many-to-Many Relationships

Performance Optimization

Performance Tips

Real-World Example: Notes Application

Notes App Demo

Production Project Structure

Project Structure

Summary

📋 Quick Reference — SQLite & ORMs

Cookie & Privacy Settings