# sql_app/database.py
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
SQLALCHEMY_DATABASE_URL = "sqlite:///./sql_app.db"
# SQLALCHEMY_DATABASE_URL = "postgresql://user:password@postgresserver/db"
engine = create_engine(
SQLALCHEMY_DATABASE_URL, connect_args={"check_same_thread": False}
)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
Base = declarative_base()
# "connect_args" is needed only for SQLite. It's not needed for other databases.
# default로 sqlite는 1개의 쓰레드만 이용하는데 shared memory를 방지하기 위해서이다.
# 하지만 fastapi에서는 def를 써도 1개 이상의 쓰레드가 이용되기 때문에
# 위와 같은 parameter를 넘겨줌으로써 독립적이게 접근하도록 한다.
# Now we will use the function declarative_base() that returns a class.
# Later we will inherit from this class to create each of the database
# models or classes (the ORM models):
# sql_app/models.py
from sqlalchemy import Boolean, Column, ForeignKey, Integer, String
from sqlalchemy.orm import relationship
from .database import Base
class User(Base):
__tablename__ = "users"
id = Column(Integer, primary_key=True, index=True)
email = Column(String, unique=True, index=True)
hashed_password = Column(String)
is_active = Column(Boolean, default=True)
items = relationship("Item", back_populates="owner")
class Item(Base):
__tablename__ = "items"
id = Column(Integer, primary_key=True, index=True)
title = Column(String, index=True)
description = Column(String, index=True)
owner_id = Column(Integer, ForeignKey("users.id"))
owner = relationship("User", back_populates="items")
# __tablename__은 database의 table 이름
# User의 items에는 Item의 owner를 가리키고 이는 items table을 가리킨다. 즉 my_user.items에는
# items table을 가져올 수 있고, items table에서는 ForeignKey로 users.id를 필요로 하고 있다.
# my_item.owner는 ForeignKey를 이용해 user table의 record를 가지고 올 수 있다.
# sql_app/schemas.py
# These Pydantic models define a "schema" and different between the SQLAlchemy
# models and the Pydantic models
class ItemBase(BaseModel):
title: str
description: Optional[str] = None
class ItemCreate(ItemBase):
pass
class Item(ItemBase):
id: int
owner_id: int
class Config:
orm_mode = True
class UserBase(BaseModel):
email: str
class UserCreate(UserBase):
password: str
class User(UserBase):
id: int
is_active: bool
items: List[Item] = []
class Config:
orm_mode = True
# sqlalchemy는 '=', pydantic은 ':'를 쓴다.
# dict대신 orm_mode를 쓰겠다.
# dict방식 -> id = data["id"]
# orm 방식 -> id = data.id
# SQLAlchemy and many others are by default "lazy loading".
# relation을 고려안하고 my_user.items를 선언하면 그때 가져온다.
# 하지만 orm_mode에서는 가져온다.
# sql_app/crud.py
from sqlalchemy.orm import Session
from . import models, schemas
def get_user(db: Session, user_id: int):
return db.query(models.User).filter(models.User.id == user_id).first()
def get_user_by_email(db: Session, email: str):
return db.query(models.User).filter(models.User.email == email).first()
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
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
# add: that instance object to your database session.
# commit: the changes to the database (so that they are saved).
# refresh: your instance (so that it contains any new data from the database,
# like the generated ID).
from typing import List
from fastapi import Depends, FastAPI, HTTPException
from sqlalchemy.orm import Session
from . import crud, models, schemas
from .database import SessionLocal, engine
models.Base.metadata.create_all(bind=engine) # In a very simplistic way create the database tables
app = FastAPI()
# Dependency
# We need to have an independent database session/connection (SessionLocal) per request,
# use the same session through all the request and then close it after the request is finished.
def get_db():
db = SessionLocal()
try:
yield db
finally:
db.close()
@app.post("/users/", response_model=schemas.User)
def create_user(user: schemas.UserCreate, db: Session = Depends(get_db)):
# when using the dependency in a path operation function, we declare it with the type Session
# The parameter db is actually of type SessionLocal, but this class (created with sessionmaker())
# is a "proxy" of a SQLAlchemy Session
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
# Notice that the values you return are SQLAlchemy models, or lists of SQLAlchemy models.
# But as all the path operations have a response_model with Pydantic models / schemas
# using orm_mode, the data declared in your Pydantic models will be extracted from them
# and returned to the client, with all the normal filtering and validation.
# we should declare the path operation functions and the dependency without async def
