FastAPI: SQLAlchemy で MariaDB を使う


次のページを参考にしました。
[SQL (Relational) Databases](https://fastapi.tiangolo.com/ja/tutorial/sql-databases/)

Ubuntu でのライブラリーのインストール

```bash
sudo apt install libmysqlclient-dev
sudo apt install python3-mysqldb
```

MariaDB に次のデータベースを作成します。

```text
host ='localhost'
data_base = 'db_test'
user ='scott'
password = 'tiger123'
```

データベースが出来ていることの確認

```text
$ mysql -uscott -ptiger123 db_test
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 42
Server version: 10.11.2-MariaDB-1-log Ubuntu 23.04

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [db_test]>
```



## プログラム ##

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

```py: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, name=user.name)
    db.add(db_user)
    db.commit()
    db.refresh(db_user)
    return db_user
```

```py:database.py
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
#
host ='localhost'
data_base = 'db_test'
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()
```

```py: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("/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
```

```py: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(255), unique=True, index=True)
    hashed_password = Column(String(255))
    is_active = Column(Boolean, default=True)
    name = Column(String(255), index=True)
```

```py:schemas.py
from pydantic import BaseModel

class UserBase(BaseModel):
    email: str

class UserCreate(UserBase):
    password: str
    name: str

class User(UserBase):
    id: int
    is_active: bool
    name: str

    class Config:
        orm_mode = True
```

## サーバーの実行 ##

```bash
uvicorn sql_app.main:app --reload
```

## テストスクリプト ##

データの作成

```bash:http_create.sh
http 'http://127.0.0.1:8000/users/' email="aa@bbb.com" password="secret" name="Tom"
http 'http://127.0.0.1:8000/users/' email="bb@bbb.com" password="secret" name="Betty"
http 'http://127.0.0.1:8000/users/' email="cc@bbb.com" password="secret" name="John"
```

データの取得

```bash:http_get.sh
http 'http://127.0.0.1:8000/users/?skip=0&limit=100'
```

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

```bash:http_user.sh
http 'http://127.0.0.1:8000/users/1'
```

実行結果

```text
$ ./http_user.sh 
HTTP/1.1 200 OK
content-length: 59
content-type: application/json
date: Fri, 19 May 2023 05:45:58 GMT
server: uvicorn

{
    "email": "aa@bbb.com",
    "id": 1,
    "is_active": true,
    "name": "Tom"
}
```

## 確認したバージョン

```text
$ python
Python 3.11.2 (main, Mar 13 2023, 12:18:29) [GCC 12.2.0] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import fastapi
>>> fastapi.__version__
'0.91.0'
>>> import sqlalchemy
>>> sqlalchemy.__version__
'1.4.46'
```