FastAPI: SQLAlchemy で MariaDB を使う (その 3)

こちらのプログラムを改造しました。
FastAPI: SQLAlchemy で MariaDB を使う (その 2)

データベースとテーブルを変更
API に delete を追加

次のデータベースに、cities というテーブルを作成します。

host ='localhost'
data_base = 'city'
user ='scott'
password = 'tiger123'

プログラム

フォルダー構造は同じです。

$ tree
.
├── __init__.py
├── crud.py
├── database.py
├── main.py
├── models.py
└── schemas.py

crud.py

from sqlalchemy.orm import Session

from . import models, schemas
#
def get_city(db: Session, city_id: str):
	return db.query(models.City).filter(models.City.id == city_id).first()
#
def get_cities(db: Session, skip: int = 0, limit: int = 100):
	return db.query(models.City).offset(skip).limit(limit).all()
#
def create_city(db: Session, city: schemas.CityCreate):
	db_city = models.City(id=city.id, name=city.name, \
		population=city.population, date_mod=city.date_mod)
	db.add(db_city)
	db.commit()
	db.refresh(db_city)
	return db_city
#
def delete_city(db: Session, city_id: str):
	db_city = db.query(models.City).filter(models.City.id == city_id).first()
	db.delete(db_city)
	db.commit()
#

database.py

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
#
host ='localhost'
data_base = 'city'
user ='scott'
password = 'tiger123'
#
db_url = "mysql://" + user + ":" + password + "@" + host + "/" + data_base
#
engine = create_engine(db_url)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

Base = declarative_base()
#

main.py

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)

app = FastAPI()

# Dependency
def get_db():
	db = SessionLocal()
	try:
		yield db
	finally:
		db.close()
#
@app.post("/cities/", response_model=schemas.City)
def create_city(city: schemas.CityCreate, db: Session = Depends(get_db)):
	return crud.create_city(db=db, city=city)
#
@app.get("/cities/", response_model=list[schemas.City])
def read_cities(skip: int = 0, limit: int = 100, db: Session = Depends(get_db)):
	cities = crud.get_cities(db, skip=skip, limit=limit)
	return cities
#
@app.get("/cities/{city_id}", response_model=schemas.City)
def read_city(city_id: str, db: Session = Depends(get_db)):
	db_city = crud.get_city(db, city_id=city_id)
	if db_city is None:
		raise HTTPException(status_code=404, detail="User not found")
	return db_city
#
@app.delete("/cities/{city_id}")
def delete_city(city_id: str, db: Session = Depends(get_db)):
	crud.delete_city(db, city_id=city_id)
#

models.py

from sqlalchemy import Boolean, Column, ForeignKey, Integer, String, DateTime
from sqlalchemy.orm import relationship

from .database import Base

class City(Base):
	__tablename__ = "cities"

	id = Column(String(15), primary_key=True, index=True)
	name = Column(String(255), index=True)
	population = Column(Integer)
	date_mod = Column(DateTime)
#

schemas.py

#
from datetime import datetime
from pydantic import BaseModel

class CityBase(BaseModel):
	id: str

class CityCreate(CityBase):
	id: str
	name: str
	population: int
	date_mod: datetime

class City(CityBase):
	id: str
	name: str
	population: int
	date_mod: datetime

	class Config:
		orm_mode = True

テストスクリプト

データの作成

http_create.sh

#
http http://127.0.0.1:8000/cities/ id="t3321" name="岡山" population=91761 date_mod='2020-05-24T12:00:00'
http http://127.0.0.1:8000/cities/ id="t3322" name="倉敷" population=71438 date_mod='2020-04-18T11:00:00'
http http://127.0.0.1:8000/cities/ id="t3323" name="津山" population=84719 date_mod='2020-05-9T09:00:00'
http http://127.0.0.1:8000/cities/ id="t3324" name="玉野" population=21782 date_mod='2020-03-12T07:00:00'

データの取得

http_get.sh

http 'http://127.0.0.1:8000/cities/?skip=0&limit=100' 

id を指定してデータの取得

http_city.sh

http http://127.0.0.1:8000/cities/t3322

id を指定してデータの削除

http_delete.sh

http DELETE http://127.0.0.1:8000/cities/t3322

GUI でのテスト

http://localhost:8000/docs

MariaDB の状態

$ mysql -uscott -ptiger123 city

MariaDB [city]> show columns from cities;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| id         | varchar(15)  | NO   | PRI | NULL    |       |
| name       | varchar(255) | YES  | MUL | NULL    |       |
| population | int(11)      | YES  |     | NULL    |       |
| date_mod   | datetime     | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
4 rows in set (0.105 sec)

MariaDB [city]> select * from cities;
+-------+--------+------------+---------------------+
| id    | name   | population | date_mod            |
+-------+--------+------------+---------------------+
| t3321 | 岡山   |      91761 | 2020-05-24 12:00:00 |
| t3322 | 倉敷   |      71438 | 2020-04-18 11:00:00 |
| t3323 | 津山   |      84719 | 2020-05-09 09:00:00 |
| t3324 | 玉野   |      21782 | 2020-03-12 07:00:00 |
+-------+--------+------------+---------------------+
4 rows in set (0.000 sec)