5.2.18. Example: Small Application with Single Files

5.2.18.1. Create the SQLAlchemy parts

%%writefile database.py

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

# Create a database URL for SQLAlchemy
SQLALCHEMY_DATABASE_URL = "sqlite:///./sql_app.db"
# SQLALCHEMY_DATABASE_URL = "postgresql://user:password@postgresserver/db"

# Create the SQLAlchemy engine
engine = create_engine(
    SQLALCHEMY_DATABASE_URL, 
    connect_args={"check_same_thread": False} # is needed only for SQLite. It's not needed for other databases.
)

# Create a SessionLocal class
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

Base = declarative_base()

Writing database.py

5.2.18.2. Create the database models

%%writefile models.py
from sqlalchemy import Boolean, Column, ForeignKey, Integer, String
from sqlalchemy.orm import relationship

from database import Base


class User(Base):
    # tells SQLAlchemy the name of the table to use in the database for each of these models.
    __tablename__ = "users"

    # Create model attributes/columns
    id = Column(Integer, primary_key=True)
    email = Column(String, unique=True, index=True)
    hashed_password = Column(String)
    is_active = Column(Boolean, default=True)

    # Create the relationships between these models
    items = relationship("Item", back_populates="owner")


class Item(Base):
    __tablename__ = "items"

    # Create model attributes/columns
    id = Column(Integer, primary_key=True)
    title = Column(String, index=True)
    description = Column(String, index=True)
    owner_id = Column(Integer, ForeignKey("users.id"))

    # relationship to the User model
    owner = relationship("User", back_populates="items")

Overwriting models.py

5.2.18.3. Create the Pydantic models

%%writefile schemas.py
from pydantic import BaseModel


class ItemBase(BaseModel): # have common attributes while creating or reading data
    title: str
    description: str | None = None


class ItemCreate(ItemBase): # plus any additional data (attributes) needed for creation.
    pass


class Item(ItemBase):
    id: int
    owner_id: int

    class Config:
        orm_mode = True # read the data with data is not only a dict, it can be an atribute of the class


class UserBase(BaseModel): # have common attributes while creating or reading data
    email: str


class UserCreate(UserBase): # plus any additional data (attributes) needed for creation.
    password: str # the user will also have a password when creating it
    # But for security, the password won't be in other Pydantic models


class User(UserBase): # will be used when reading a user (returning it from the API) doesn't include the password.
    id: int
    is_active: bool
    items: list[Item] = []

    class Config:
        orm_mode = True # read the data even if it is not a dict

Overwriting schemas.py

5.2.18.4. CRUD utils

In this file we will have reusable functions to interact with the data in the database: DELETE, CREATE, READ, UPDATE

%%writefile crud.py
from sqlalchemy.orm import Session
import models 
import schemas

# read user by id filter
def get_user(db: Session, user_id: int):
    return db.query(models.User).filter(models.User.id == user_id).first()

# read user by email filter
def get_user_by_email(db: Session, email: str):
    return db.query(models.User).filter(models.User.email == email).first()

# Read multiple users
def get_users(db: Session, skip: int = 0, limit: int = 100):
    return db.query(models.User).offset(skip).limit(limit).all()


def create_user(db: Session, user: schemas.UserCreate):
    fake_hashed_password = user.password + "notreallyhashed"
    db_user = models.User(email=user.email, hashed_password=fake_hashed_password)
    db.add(db_user)
    db.commit()
    db.refresh(db_user)
    return db_user

# Read multiple items
def get_items(db: Session, skip: int = 0, limit: int = 100):
    return db.query(models.Item).offset(skip).limit(limit).all()


def create_user_item(db: Session, item: schemas.ItemCreate, user_id: int):
    db_item = models.Item(**item.dict(), owner_id=user_id)
    db.add(db_item)
    db.commit()
    db.refresh(db_item)
    return db_item

Overwriting crud.py

5.2.18.5. Main FastAPI app

%%writefile main.py
import crud
import models
import schemas
from database import SessionLocal, engine
from fastapi import Depends, FastAPI, HTTPException
from sqlalchemy.orm import Session

# In a very simplistic way create the database tables:
models.Base.metadata.create_all(bind=engine)

app = FastAPI()


# Dependency
def get_db():
    db = SessionLocal()
    try:
        yield db
    # run code after response is returned to the client
    finally:
        db.close()


@app.post("/users/", response_model=schemas.User)
def create_user(user: schemas.UserCreate, db: Session = Depends(get_db)):
    db_user = crud.get_user_by_email(db, email=user.email)
    if db_user:
        raise HTTPException(status_code=400, detail="Email already registered")
    return crud.create_user(db=db, user=user)


@app.get("/users/", response_model=list[schemas.User])
def read_users(skip: int = 0, limit: int = 100, db: Session = Depends(get_db)):
    users = crud.get_users(db, skip=skip, limit=limit)
    return users


@app.get("/users/{user_id}", response_model=schemas.User)
def read_user(user_id: int, db: Session = Depends(get_db)):
    db_user = crud.get_user(db, user_id=user_id)
    if db_user is None:
        raise HTTPException(status_code=404, detail="User not found")
    return db_user


@app.post("/users/{user_id}/items/", response_model=schemas.Item)
def create_item_for_user(
    user_id: int, item: schemas.ItemCreate, db: Session = Depends(get_db)
):
    return crud.create_user_item(db=db, item=item, user_id=user_id)


@app.get("/items/", response_model=list[schemas.Item])
def read_items(skip: int = 0, limit: int = 100, db: Session = Depends(get_db)):
    items = crud.get_items(db, skip=skip, limit=limit)
    return items

Overwriting main.py

5.2.18.6. Run app

Run in dev: fastapi dev main.py

Run in prod: fastapi run main.py