SQLite & ORMs

Reviewed & published by Brayan K

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

Part of the free Python course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

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
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.

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", "[email protected]")
)

cursor.execute(
    "INSERT INTO users (username, email) VALUES (?, ?)",
    ("bob", "[email protected]")
)

# Query data
cursor.execute("SELECT id, username, email FROM users")
rows = cursor.fetchall()

print("Users in database:")
for row in rows:
    print(f"  ID: {row[0]}, Username: {row[1]}, Email: {row[2]}")

conn.commit()
conn.close()

print("\nDatabase operations complete!")

# ✅ Expected output:
# Users in database:
#   ID: 1, Username: alice, Email: [email protected]
#   ID: 2, Username: bob, Email: [email protected]
#
# Database operations complete!

What Is an ORM?

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")

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.

# 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 queries (great for learning!)
)

# 3. Create session factory
SessionLocal = sessionmaker(bind=engine)

# 4. Define a model (maps to a table)
class User(Base):
    __tablename__ = "users"
    
    id = Column(Integer, primary_key=True, index=True)
    username = Column(String(50), unique=True, nullable=False)
    email = Column(String(120), unique=True, nullable=False)
    
    def __repr__(self):
        return f"<User(username='{self.username}')>"

# 5. Create tables
Base.metadata.create_all(bind=engine)
""")

print("\nThis creates:")
print("  - Base: parent class for all models")
print("  - engine: manages DB connection")
print("  - SessionLocal: factory for creating sessions")
print("  - User: model mapped to 'users' table")

# ✅ Expected output:
# SQLAlchemy Setup Example
# ========================================
#
# 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 queries (great for learning!)
# )
#
# # 3. Create session factory
# SessionLocal = sessionmaker(bind=engine)
#
# # 4. Define a model (maps to a table)
# class User(Base):
#     __tablename__ = "users"
#     
#     id = Column(Integer, primary_key=True, index=True)
#     username = Column(String(50), unique=True, nullable=False)
#     email = Column(String(120), unique=True, nullable=False)
#     
#     def __repr__(self):
#         return f"<User(username='{self.username}')>"
#
# # 5. Create tables
# Base.metadata.create_all(bind=engine)
#
#
# This creates:
#   - Base: parent class for all models
#   - engine: manages DB connection
#   - SessionLocal: factory for creating sessions
#   - User: model mapped to 'users' table

Basic CRUD Operations

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

# Simulated CRUD operations (run locally with SQLAlchemy)

print("=== CREATE ===")
print("with get_session() as session:")
print("    user1 = User(username='alice', email='[email protected]')")
print("    user2 = User(username='bob', email='[email protected]')")
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(username='alice').first()")
print("Found user: <User(username='alice')>")

print("\n=== UPDATE ===")
print("with get_session() as session:")
print("    user = session.query(User).filter_by(username='alice').first()")
print("    user.email = '[email protected]'")
print("Updated email for alice")

print("\n=== DELETE ===")
print("with get_session() as session:")
print("    user = session.query(User).filter_by(username='bob').first()")
print("    session.delete(user)")
print("Deleted user: bob")

# ✅ Expected output:
# === CREATE ===
# with get_session() as session:
#     user1 = User(username='alice', email='[email protected]')
#     user2 = User(username='bob', email='[email protected]')
#     session.add_all([user1, user2])
# Users created!
#
# === READ ===
# with get_session() as session:
#     all_users = session.query(User).all()
#     alice = session.query(User).filter_by(username='alice').first()
# Found user: <User(username='alice')>
#
# === UPDATE ===
# with get_session() as session:
#     user = session.query(User).filter_by(username='alice').first()
#     user.email = '[email protected]'
# Updated email for alice
#
# === DELETE ===
# with get_session() as session:
#     user = session.query(User).filter_by(username='bob').first()
#     session.delete(user)
# Deleted user: bob

Defining Relationships (One-to-Many)

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

# 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 = Column(Integer, ForeignKey("users.id"))
    
    # Relationship: each post belongs to one user
    author = relationship("User", back_populates="posts")
""")

print("\nUsage Example:")
print("-" * 30)
print("user = User(username='john')")
print("post1 = Post(title='Hello World', author=user)")
print("post2 = Post(title='Python Tips', author=user)")
print("")
print("# Access related data:")
print("for post in user.posts:")
print("    print(post.title)")
print("")
print("Output:")
print("  Hello World")
print("  Python Tips")

# ✅ Expected output:
# One-to-Many Relationship: User -> Posts
# =============================================
#
# 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 = Column(Integer, ForeignKey("users.id"))
#     
#     # Relationship: each post belongs to one user
#     author = relationship("User", back_populates="posts")
#
#
# Usage Example:
# ------------------------------
# user = User(username='john')
# post1 = Post(title='Hello World', author=user)
# post2 = Post(title='Python Tips', author=user)
#
# # Access related data:
# for post in user.posts:
#     print(post.title)
#
# Output:
#   Hello World
#   Python Tips

Advanced Query Patterns

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

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()")

print("\n=== PAGINATION ===")
print("page = 1")
print("per_page = 10")
print("session.query(User).offset((page - 1) * per_page).limit(per_page).all()")

print("\n=== AGGREGATION ===")
print("# Count users")
print("session.query(func.count(User.id)).scalar()")

print("\n# Check if exists")
print("session.query(session.query(User).exists()).scalar()")

# ✅ Expected output:
# === FILTERING ===
# # Case-insensitive search
# session.query(User).filter(User.email.ilike('%example%')).all()
#
# # Multiple conditions (AND)
# session.query(User).filter(
#     and_(User.username == 'alice', User.email.like('%example%'))
# ).first()
#
# === ORDERING ===
# # Alphabetical
# session.query(User).order_by(User.username).all()
#
# # Reverse by ID
# session.query(User).order_by(User.id.desc()).all()
#
# === PAGINATION ===
# page = 1
# per_page = 10
# session.query(User).offset((page - 1) * per_page).limit(per_page).all()
#
# === AGGREGATION ===
# # Count users
# session.query(func.count(User.id)).scalar()
#
# # Check if exists
# session.query(session.query(User).exists()).scalar()

Many-to-Many Relationships

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

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"
    id = Column(Integer, primary_key=True)
    title = Column(String(120))
    tags = relationship("Tag", secondary=post_tags, back_populates="posts")
""")

print("Usage:")
print("-" * 20)
print("python_tag = Tag(name='python')")
print("tutorial_tag = Tag(name='tutorial')")
print("")
print("post = Post(title='Python Basics', tags=[python_tag, tutorial_tag])")
print("")
print("# Query by tag:")
print("for post in python_tag.posts:")
print("    print(post.title, [t.name for t in post.tags])")
print("")
print("Output: Python Basics ['python', 'tutorial']")

# ✅ Expected output:
# Many-to-Many: Posts <-> Tags
# ===================================
#
# # 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"
#     id = Column(Integer, primary_key=True)
#     title = Column(String(120))
#     tags = relationship("Tag", secondary=post_tags, back_populates="posts")
#
# Usage:
# --------------------
# python_tag = Tag(name='python')
# tutorial_tag = Tag(name='tutorial')
#
# post = Post(title='Python Basics', tags=[python_tag, tutorial_tag])
#
# # Query by tag:
# for post in python_tag.posts:
#     print(post.title, [t.name for t in post.tags])
#
# Output: Python Basics ['python', 'tutorial']

Performance Optimization

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

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(50), index=True)  # Indexed!")
print("    email = Column(String(120), index=True)")
print("")

print("=== OPTIMIZATION TIPS ===")
print("✓ Use joinedload() to avoid N+1 queries")
print("✓ Add indexes on frequently queried columns")
print("✓ Select only needed columns")
print("✓ Use exists() instead of count() for checks")
print("✓ Use pagination for large result sets")

# ✅ Expected output:
# === N+1 PROBLEM (SLOW) ===
# users = session.query(User).all()  # 1 query
# for user in users:
#     print(len(user.posts))  # N queries!
#
# === EAGER LOADING (FAST) ===
# from sqlalchemy.orm import joinedload
#
# users = session.query(User).options(
#     joinedload(User.posts)
# ).all()  # Single query with JOIN!
#
# === INDEXING ===
# class User(Base):
#     username = Column(String(50), index=True)  # Indexed!
#     email = Column(String(120), index=True)
#
# === OPTIMIZATION TIPS ===
# ✓ Use joinedload() to avoid N+1 queries
# ✓ Add indexes on frequently queried columns
# ✓ Select only needed columns
# ✓ Use exists() instead of count() for checks
# ✓ Use pagination for large result sets

Real-World Example: Notes Application

A complete CRUD application demonstrating practical ORM usage.

# 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.values() 
            if query in n.title.lower() or query in n.body.lower()]

def delete_note(note_id):
    if note_id in notes_db:
        del notes_db[note_id]
        return True
    return False

# Demo
print("=== NOTES APP DEMO ===\n")

create_note("Shopping List", "Milk, eggs, bread")
create_note("Python Tips", "Always use ORM for database access")
create_note("Meeting Notes", "Discussed project roadmap")
print("Created 3 notes")

notes = get_all_notes()
print(f"\nAll notes ({len(notes)}):")
for note in notes:
    print(f"  {note.id}. {note.title}")

results = search_notes("python")
print(f"\nSearch 'python':")
for note in results:
    print(f"  {note.title}: {note.body}")

delete_note(1)
print(f"\nDeleted note 1")
print(f"Remaining: {[n.title for n in get_all_notes()]}")

# ✅ Expected output:
# === NOTES APP DEMO ===
#
# Created 3 notes
#
# All notes (3):
#   1. Shopping List
#   2. Python Tips
#   3. Meeting Notes
#
# Search 'python':
#   Python Tips: Always use ORM for database access
#
# Deleted note 1
# Remaining: ['Python Tips', 'Meeting Notes']

Production Project Structure

Organize your database code for maintainability and scalability.

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/
│   ├── auth_service.py    # Business logic
│   └── post_service.py
└── main.py                # Application entry point


Why this structure?
-------------------
✓ Separation of concerns
✓ Easy to test
✓ Models isolated from business logic
✓ Database layer is swappable
✓ Scales to large applications

This is the pattern used by:
- FastAPI applications
- Flask applications
- Django-style architectures
- Microservices
""")

# ✅ Expected output:
#
# 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/
# │   ├── auth_service.py    # Business logic
# │   └── post_service.py
# └── main.py                # Application entry point
#
#
# Why this structure?
# -------------------
# ✓ Separation of concerns
# ✓ Easy to test
# ✓ Models isolated from business logic
# ✓ Database layer is swappable
# ✓ Scales to large applications
#
# This is the pattern used by:
# - FastAPI applications
# - Flask applications
# - Django-style architectures
# - Microservices

# 🎯 YOUR TURN — replace each ___ using the hint beside it.

import sqlite3

# 1) A whole database that lives in RAM and disappears at the end —
#    perfect for a lesson, and for a test.
conn = sqlite3.connect("___")             # 👉 replace ___ with :memory:

# 2) Rows come back as tuples by default. This makes them subscriptable
#    by column name instead.
conn.___ = sqlite3.Row                    # 👉 replace ___ with row_factory
cur = conn.cursor()

cur.execute("CREATE TABLE book (id INTEGER PRIMARY KEY, title TEXT NOT NULL, pages INTEGER)")

# 3) Many rows in one call, with ? placeholders — never string formatting,
#    which is how SQL injection gets in.
cur.___(                                   # 👉 replace ___ with executemany
    "INSERT INTO book (title, pages) VALUES (?, ?)",
    [("Dune", 412), ("Emma", 474), ("Bee", 96)],
)
conn.commit()

# 4) The parameters go in a tuple, separate from the SQL.
cur.execute("SELECT title, pages FROM book WHERE pages > ? ORDER BY pages", (___,))  # 👉 replace ___ with 100
for row in cur.fetchall():
    print(row["title"], "-", row["pages"], "pages")

cur.execute("SELECT COUNT(*) AS n, AVG(pages) AS avg FROM book")
# 5) One row expected, so take one rather than a list of one.
summary = cur.___()                        # 👉 replace ___ with fetchone
print("Books:", summary["n"], "average pages:", round(summary["avg"], 1))

cur.execute("UPDATE book SET pages = ? WHERE title = ?", (100, "Bee"))
print("Rows changed:", cur.rowcount)

conn.close()

# ✅ Expected output:
# Dune - 412 pages
# Emma - 474 pages
# Books: 3 average pages: 327.3
# Rows changed: 1

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.

Practice quiz

Which built-in module lets Python talk to a SQLite database directly?

sqlalchemy
pysqlite
sqlite3
dbapi

Answer: sqlite3. sqlite3 ships with Python's standard library for direct SQLite operations.

What does sqlite3.connect(":memory:") create?

A temporary in-memory database
A connection to a file named memory.db
A read-only database
A network connection

Answer: A temporary in-memory database. ":memory:" makes a database that lives in RAM — perfect for tests and demos.

Why use ? placeholders in cursor.execute(sql, params)?

To make queries shorter
To sort the results
They are required by SQLite
To prevent SQL injection

Answer: To prevent SQL injection. Parameterised queries with ? safely escape values, preventing SQL injection.

What does cursor.fetchone() return after a SELECT?

A list of all rows
A single row as a tuple (or None)
The number of rows
A dictionary

Answer: A single row as a tuple (or None). fetchone() returns the next row as a tuple, or None when there are no more rows.

What does cursor.fetchall() return?

A list of all remaining rows (each a tuple)
One row
A count of rows
A boolean

Answer: A list of all remaining rows (each a tuple). fetchall() returns every remaining row as a list of tuples.

After modifying data, which call saves the changes to the database?

conn.save()
conn.flush()
conn.commit()
conn.write()

Answer: conn.commit(). conn.commit() persists pending INSERT/UPDATE/DELETE changes; without it they can be lost.

What does an ORM map a database table to?

A Python function
A Python class
A dictionary
A SQL string

Answer: A Python class. An ORM maps tables to classes, rows to object instances, and columns to attributes.

In SQLAlchemy, a table row corresponds to what?

A class
A column
A query
An object instance

Answer: An object instance. Each row becomes an object instance, e.g. user = User(name='Alice').

What is the N+1 query problem?

Running one query too many by mistake
Loading a list, then firing a separate query per item for related data
A syntax error in SQL
Using too many indexes

Answer: Loading a list, then firing a separate query per item for related data. 1 query loads the parents, then N more load each parent's relations — fixed with eager loading.

Which SQLAlchemy option avoids the N+1 problem with a single JOIN?

lazy_load()
select_all()
joinedload(User.posts)
prefetch()

Answer: joinedload(User.posts). joinedload eagerly loads the relationship in one JOIN query instead of N separate ones.

Continue this course

Previous: Building and Reusing Custom Decorator Libraries
Next: REST API Clients & External Service Integrations — Call REST APIs with requests, handle auth, and parse JSON responses
Quick reference: Python cheat sheet