1
Help us understand the problem. What are the problem?

posted at

updated at

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

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

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

プログラム

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

$ 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 でのテスト

image.png

MariaDB の状態

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)

Register as a new user and use Qiita more conveniently

  1. You can follow users and tags
  2. you can stock useful information
  3. You can make editorial suggestions for articles
What you can do with signing up
1
Help us understand the problem. What are the problem?