20210727のMySQLに関する記事は4件です。

FastAPIの認証付き開発環境を整える

TL;DR コードはここに一式置いてあります。 https://github.com/inunekousapon/easy_fastapi 環境 MacOS Big Sur バージョン11.4 Docker Desktop Version 3.4.0 作るもの FastAPIが動くこと MySQLと繋がっていること 認証の仕組みが整っていること JWTで認証と認可ができること FastAPIのDockerを作る Github公式 https://github.com/tiangolo/uvicorn-gunicorn-fastapi-docker FROM tiangolo/uvicorn-gunicorn-fastapi:python3.8 COPY ./app/requirements.txt /app RUN pip install -r requirements.txt ライブラリをインストールするためにappディレクトリを作成し、requirements.txtを追加します。 . ├── Dockerfile ├── app │ └── requirements.txt requirements.txtにはJWTの認証に必要なライブラリとデータベースの接続に必要なものを指定しておきます。 requirements.txt python-jose[cryptography]== 3.3.0 passlib[bcrypt]==1.7.4 SQLAlchemy==1.4.22 pymysql==1.0.2 python-multipart==0.0.5 MySQLを環境に加える docker-compose.ymlを加えていきます。 MySQLの設定は下記のサイトを参考にしています。 https://qiita.com/ucan-lab/items/b094dbfc12ac1cbee8cb docker-compose.yml version: "3" services: db: image: mysql:8.0 volumes: - db-store:/var/lib/mysql - ./logs:/var/log/mysql - ./docker/mysql/my.cnf:/etc/mysql/conf.d/my.cnf environment: - MYSQL_DATABASE=${DB_NAME} - MYSQL_USER=${DB_USER} - MYSQL_PASSWORD=${DB_PASS} - MYSQL_ROOT_PASSWORD=${DB_PASS} - TZ=${TZ} ports: - ${DB_PORT}:3306 web: build: . ports: - 80:80 command: /start-reload.sh volumes: - ./app:/app environment: - DB_NAME=${DB_NAME} - DB_USER=${DB_USER} - DB_PASS=${DB_PASS} - DB_PORT=${DB_PORT} - DB_HOSTNAME=db volumes: db-store: docker/mysql/my.cnf # MySQLサーバーへの設定 [mysqld] # 文字コード/照合順序の設定 character-set-server = utf8mb4 collation-server = utf8mb4_bin # タイムゾーンの設定 default-time-zone = SYSTEM log_timestamps = SYSTEM # デフォルト認証プラグインの設定 default-authentication-plugin = mysql_native_password # エラーログの設定 log-error = /var/log/mysql/mysql-error.log # スロークエリログの設定 slow_query_log = 1 slow_query_log_file = /var/log/mysql/mysql-slow.log long_query_time = 5.0 log_queries_not_using_indexes = 0 # 実行ログの設定 general_log = 1 general_log_file = /var/log/mysql/mysql-query.log # mysqlオプションの設定 [mysql] # 文字コードの設定 default-character-set = utf8mb4 # mysqlクライアントツールの設定 [client] # 文字コードの設定 default-character-set = utf8mb4 DB_NAME=homestead DB_USER=homestead DB_PASS=secret DB_PORT=3306 TZ=Asia/Tokyo ディレクトリ構成はこうなったはずです。 . ├── Dockerfile ├── app │ └── requirements.txt ├── docker │ └── mysql │ └── my.cnf └── docker-compose.yml アプリケーションを書く appフォルダ以下にmain.pyを加えていきます。 app/main.py from fastapi import FastAPI app = FastAPI() @app.get("/") async def root(): return {"message": "Hello World"} ディレクトリ構成です。 . ├── Dockerfile ├── app │ ├── app.py │ └── requirements.txt ├── docker │ └── mysql │ └── my.cnf └── docker-compose.yml 起動 起動していきましょう。 docker-compose up しばらくすると下記のような表示が出るはずです。 web_1 | INFO: Uvicorn running on http://0.0.0.0:80 (Press CTRL+C to quit) web_1 | INFO: Started reloader process [1] using watchgod web_1 | INFO: Started server process [8] web_1 | INFO: Waiting for application startup. web_1 | INFO: Application startup complete. http://0.0.0.0:80 にアクセスすると下記のレスポンスが得られるはずです。 {"message":"Hello World"} http://0.0.0.0:80/docs にアクセスするとSwagger的なドキュメントを見ることができます。 データベース連携 appフォルダ以下に database.py というファイルを追加します。 MySQLと接続するために必要です。 app/database.py import os from sqlalchemy import create_engine from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import sessionmaker user = os.getenv("DB_USER") password = os.getenv("DB_PASS") dbname = os.getenv("DB_NAME") hostname = os.getenv("DB_HOSTNAME") SQLALCHEMY_DATABASE_URL = f"mysql+pymysql://{user}:{password}@{hostname}/{dbname}" engine = create_engine(SQLALCHEMY_DATABASE_URL) SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine) Base = declarative_base() appフォルダ以下に models.py というファイルを追加します。 テーブルの定義をします。 app/models.py from sqlalchemy import Boolean, Column, Integer, String from sqlalchemy.orm import relationship from database import Base class User(Base): __tablename__ = "users" id = Column(Integer, primary_key=True, index=True) username = Column(String(120), index=True) full_name = Column(String(120)) email = Column(String(200), unique=True, index=True) hashed_password = Column(String(60)) disabled = Column(Boolean, default=True) app/main.pyに下記を足します。 本来ならばcreate_allは利用せずにmigrationツールを使いますので注意してください。 models.Base.metadata.create_all(bind=engine) app/main.py from fastapi import FastAPI import models from database import engine models.Base.metadata.create_all(bind=engine) app = FastAPI() @app.get("/") async def root(): return {"message": "Hello World"} このままだとMySQLが起動する前にFastAPIサーバーが起動してしまうので起動を待つようにします。 prestart.shというファイルをapp配下に置きます。 app/prestart.sh #! /usr/bin/env bash # Let the DB start sleep 10; 再度起動するとデータベースにテーブルが作成されます。 OAuth2認証とJWT 下記の内容になります。 https://fastapi.tiangolo.com/tutorial/security/oauth2-jwt/ app/main.pyに書き足していきます。 app/main.py from datetime import datetime, timedelta from typing import Optional from fastapi import Depends, FastAPI, HTTPException, status from fastapi.security import OAuth2PasswordBearer, OAuth2PasswordRequestForm from jose import JWTError, jwt from passlib.context import CryptContext from pydantic import BaseModel from sqlalchemy.orm import Session import models from database import engine, SessionLocal SECRET_KEY = "09d25e094faa6ca2556c818166b7a9563b93f7099f6f0f4caa6cf63b88e8d3e7" ALGORITHM = "HS256" ACCESS_TOKEN_EXPIRE_MINUTES = 30 class Token(BaseModel): access_token: str token_type: str class TokenData(BaseModel): username: Optional[str] = None class User(BaseModel): username: str email: Optional[str] = None full_name: Optional[str] = None disabled: Optional[bool] = None class UserInDB(User): hashed_password: str models.Base.metadata.create_all(bind=engine) pwd_context = CryptContext(schemes=["bcrypt"], deprecated="auto") oauth2_scheme = OAuth2PasswordBearer(tokenUrl="token") app = FastAPI() def get_db(): db = SessionLocal() try: yield db finally: db.close() def verify_password(plain_password, hashed_password): return pwd_context.verify(plain_password, hashed_password) def get_password_hash(password): return pwd_context.hash(password) def get_user(db, username: str): return db.query(models.User).filter(models.User.username == username).first() def authenticate_user(db, username: str, password: str): user = get_user(db, username) if not user: return False if not verify_password(password, user.hashed_password): return False return user def create_access_token(data: dict, expires_delta: Optional[timedelta] = None): to_encode = data.copy() if expires_delta: expire = datetime.utcnow() + expires_delta else: expire = datetime.utcnow() + timedelta(minutes=15) to_encode.update({"exp": expire}) encoded_jwt = jwt.encode(to_encode, SECRET_KEY, algorithm=ALGORITHM) return encoded_jwt async def get_current_user(token: str = Depends(oauth2_scheme), db: Session = Depends(get_db)): credentials_exception = HTTPException( status_code=status.HTTP_401_UNAUTHORIZED, detail="Could not validate credentials", headers={"WWW-Authenticate": "Bearer"}, ) try: payload = jwt.decode(token, SECRET_KEY, algorithms=[ALGORITHM]) username: str = payload.get("sub") if username is None: raise credentials_exception token_data = TokenData(username=username) except JWTError: raise credentials_exception user = get_user(db, username=token_data.username) if user is None: raise credentials_exception return user @app.post("/token", response_model=Token) async def login_for_access_token(form_data: OAuth2PasswordRequestForm = Depends(), db: Session = Depends(get_db)): user = authenticate_user(db, form_data.username, form_data.password) if not user: raise HTTPException( status_code=status.HTTP_401_UNAUTHORIZED, detail="Incorrect username or password", headers={"WWW-Authenticate": "Bearer"}, ) access_token_expires = timedelta(minutes=ACCESS_TOKEN_EXPIRE_MINUTES) access_token = create_access_token( data={"sub": user.username}, expires_delta=access_token_expires ) return {"access_token": access_token, "token_type": "bearer"} @app.get("/") async def root(current_user: User = Depends(get_current_user)): return {"message": "Hello World"} データベースのusersに下記のレコードを追加します。 id username full_name email hashed_password disabled 1 johndoe John Doe johndoe@example.com $2b$12$EixZaYVK1fsbw1ZfbX3OXePaWxn96p36WQoeG6Lruj3vjPGga31lW 使い方 http://localhost:docs に繋ぎましょう。 / Root を開いて「Try it out」押し、Executeボタンを押すと401 Unauthorizedが返ってくるのが確認できます。 右上のAuthorizeボタンを押すと認証フォームが表示されるので、usernameに「johndoe」、passwordに「secret」と入力して「Authorize」ボタンを押します。 再度、/Root を開いて先程と同じ操作をすると、今度はHTTP200が返ってきます。 ハマりどころ SQLAlchemyとMySQLが意外とセットアップ難しい。 FastAPIのドキュメントで認証のバックエンドがフェイクなのでDBとの連携する箇所が分かりづらい。 pydanticのスキーマとSQLAlchemyのスキーマとリクエストのスキーマの3種類のスキーマが存在するのが分かりづらい。 参考サイト uvicorn-gunicorn-fastapi-docker https://github.com/tiangolo/uvicorn-gunicorn-fastapi-docker MySQL8.0のコンテナ作成 https://qiita.com/ucan-lab/items/b094dbfc12ac1cbee8cb
  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む

初学者がSQLを使ってみる

はじめに 基礎的なSQLコマンドは就職前に勉強しておいた方が良いと勧められた為、復習用としてまとめました。 SQLの使用方法 ターミナルでは、MySQLというRDBMSを使用してSQLを実行できます。 MySQLを使用するには、ターミナルからログインします。 # ホームディレクトリへ戻る % cd # MySQLに接続 % mysql -u root -u rootというオプションで、ユーザーは「ルート」でログインするという意味になります。 ここでいうルートとは、MySQLであらかじめ用意されているユーザーのことです。 このユーザーのパスワードは空で設定されているため、ログインにパスワードは必要ありません。 # MySQLに接続すると以下のような表示になる % mysql -u root Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 428 Server version: 5.6.47 Homebrew Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> 以上のような画面が表示されたら準備OKです。 SQLはmysql>の続きから、入力して実行ができます。 また、SQLはSequel Proでも使用できます。 SQLによるDB上の操作 SQL(Structured Query Language) RDBの操作を行うための言語のこと。 SQLの主な命令 データを定義する「DDL」 データを操作する「DML」 データを制御する「DCL」 ※この記事では、DDLについてのみ、まとめております。 DDL(Data Definition Language) データを定義するSQLです。以下のような命令文があります。 命令 機能 CREATE データベースやテーブルの作成 ALTER データベースやテーブルの更新   DROP データベースやテーブルの削除   各SQL文を実行してみる SQLを使って、データベースを作成してみます。ここでの作業はターミナルを使用して行います。 CREATE文 データベースやテーブルを作成できるSQLの文 CREATE DATABASE文を実行すると、指定した名前のデータベースが作成できる 実行時には以下のようにデータベース名の指定が必要です。 mysql> CREATE DATABASE sqltest; ちなみに、SQL文は小文字でも動作するようです。 ただし、SQL文と他の文字列が混ざると読み辛くなることから、大文字で記述することが一般的になります。 mysql> CREATE DATABASE sqltest; Query OK, 1 row affected (0.00 sec) 以上のような表示があれば、正常にデータベースが作成されています。 あと、SQL文の終わりには必ずセミコロンを付ける必要があります。 もしセミコロンを忘れて実行した場合は、SQLがまだ続くものだとみなされ、以下のような矢印マーク->が表示されます。 mysql> show databases -> SHOW文 データベースやテーブルを一覧表示できるSQLの文 SHOW DATABASES文を実行すると、作成されているデータベースを一覧で表示できる mysql> SHOW DATABASES; +------------------------+ | Database | +------------------------+ | 省略 | | sqltest   | | 省略 | +------------------------+ 36 rows in set (0.00 sec) コマンドを実行すると、以上のようなテーブルが一覧で表示されます。 DROP文 データベースやテーブルを削除できるSQLの文 DROP DATABASE文を実行すると、作成されているデータベースを削除できる mysql> DROP DATABASE sqltest; Query OK, 0 rows affected (0.02 sec) # sqltestがないことを確認 mysql> SHOW DATABASES; 以上のように表示されていれば、削除は完了です。 念のため、SHOW DATABASES;を使用して削除されているか確認してみてください。 USE文 どのデータベースを使用するのかを指定するSQLの文 USE 《database名》と指定することでデータベースを選択する テーブルを操作する場合は、はじめに「どのデータベースにあるテーブルか」を選択する必要があるため、USEを使います。 まずは、下記のようにCREATEを実行してデータベースを作成します。 mysql> CREATE DATABASE sqltest; Query OK, 1 row affected (0.00 sec) 続いてUSEを実行してデータベースを選択します。 mysql> USE sqltest; Database changed これで、作成したデータベースを使用できます。 次にデータを保存するためのテーブルを作成してみます。 データベースの作成のときと同様に、テーブルを作成する際はCREATE TABLE文を使用します。 実行時には以下のようにテーブル名を指定する必要があります。 また、そのテーブルに作成するカラムの名前とそのカラムの型を指定できます。 以下の例をご参考ください。 mysql> CREATE TABLE テーブル名 (カラム名1 カラム名1の型, カラム名2 カラム名2の型, …); 今回は、商品の情報を保存することを想定し、"items"という名前のテーブルを作ります。 そして、テーブルには商品idを数値で保存するidカラムと、商品名を文字列で保存するnameカラムを作成します。 また、カラムを作成する場合には型の指定が必要です。 MySQLで数値型や文字列型を定義する際は以下のような型名を使用します。 型の名前   保存できる値 INT 数字              VARCHAR(M) 最大M文字の文字列  カラムとその型を指定し、テーブルを作成します。 mysql> CREATE TABLE items (id INT, name VARCHAR(255)); Query OK, 0 rows affected (0.01 sec) 以上のようにコマンドを実行した結果、2段目のような表示であればSQL文が実行されています。 次に作成を確認するために、SHOW TABLES文を使用し、テーブルの一覧を確認してみます。 mysql> SHOW TABLES; +-------------------+ | Tables_in_sqltest | +-------------------+ | items | +-------------------+ 1 row in set (0.00 sec) 上記のように、コマンドを実行し"Tables_in_sqltest"に"items"というテーブルが確認できれば、テーブルが正しく作成されたということになります。 FROM句 対象となるテーブルを指定する際に使用するSQLの句 以下のようにコマンドを実行し2行目以降の表示がでてくれば、正常に実行できています。 mysql> SHOW columns FROM items; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(255) | YES | | NULL | | +-------+--------------+------+-----+---------+-------+ 2 rows in set (0.01 sec) これにより、itemsテーブルにはidとnameという2つのカラムがあることが確認できました。 ALTER文 データベースやテーブルを編集できるSQLの文 ALTER TABLE文を実行すると、テーブルに対してカラムの追加や削除ができる # ALTERでカラム情報の更新 mysql> ALTER TABLE 《テーブル名》 操作 # カラムを1つだけ追加する場合 mysql> ALTER TABLE テーブル名 ADD カラム名 カラムの型; # カラムを複数追加する場合 mysql> ALTER TABLE テーブル名 ADD (カラム名 カラムの型, ……); ここでは"price"と"zaiko"という2つのカラムを、共に"int"型で作成してみます。 mysql> ALTER TABLE items ADD (price int, zaiko int); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 カラムを追加したので、テーブルの構造がどう変化したか確認してみます。 下記のようにカラムが追加されていれば成功となります。 mysql> SHOW columns FROM items; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(255) | YES | | NULL | | | price | int(11) | YES | | NULL | | | zaiko | int(11) | YES | | NULL | | +-------+--------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) カラムの追加ができたので、カラムの変更も行ってみます。 カラムの変更は、先ほどと同様にALTER TABLE文を使用し、以下のような文法でSQL文を記述します。 先ほど"zaiko"というカラムを追加しましたが、他のカラムに合わせてカラム名を英語表記にするため、 "stock"という名前に変更しておきます。 以下のような表示であれば、正常に実行できています。 mysql> ALTER TABLE items CHANGE zaiko stock int; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 このとき、カラムの型は変更しなくても、再度記述をしなくてはなりません。 そのため、カラムの変更をするときはこの型の部分を間違えないように注意する必要があります。 先ほどと同様にカラムが変更されていることを確認します。 下記のように表示されていれば、正常に変更されています。 mysql> SHOW columns FROM items; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(255) | YES | | NULL | | | price | int(11) | YES | | NULL | | | stock | int(11) | YES | | NULL | | +-------+--------------+------+-----+---------+-------+ 4 rows in set (0.01 sec) 最後はALTER文を使いカラムを削除してみます。 mysql> ALTER TABLE items DROP stock; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 次にカラムが削除されていることを確認します。 以下のようにカラムがきちんと削除されていることを確認できれば成功です。 mysql> SHOW columns FROM items; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(255) | YES | | NULL | | | price | int(11) | YES | | NULL | | +-------+--------------+------+-----+---------+-------+ 3 rows in set (0.01 sec)
  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む

備忘:MySQLでデータベースの中身を確認する方法

よく忘れて、探すので備忘 本当にデータが登録されているかとかで使う まずはターミナルからMySQLに接続する ルートで接続 mysql -u root 本当は危険 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 276 Server version: 5.6.51 Homebrew 参考 https://www.dbonline.jp/mysql/connect/index3.html データベースを選択する use cooklog; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed 使うデータベースがわからない場合は databese.ymlで確認 development: adapter: mysql2 encoding: utf8 reconnect: false database: cooklog_development pool: 5 username: kiyomasa host: localhost test: adapter: mysql2 encoding: utf8 reconnect: false database: cooklog_test pool: 5 username: kiyomasa host: localhost 参考:https://www.dbonline.jp/mysql/database/index3.html テーブルを確認 mysql> SELECT * FROM favorites; +----+---------+-----------+----------------------------+----------------------------+ | id | user_id | recipe_id | created_at | updated_at | +----+---------+-----------+----------------------------+----------------------------+ | 1 | 1 | 23 | 2021-07-26 23:38:18.303379 | 2021-07-26 23:38:18.303379 | +----+---------+-----------+----------------------------+----------------------------+ 1 row in set (0.00 sec) mysql> SELECT * FROM users; +----+------------+-------------------+--------------------------------------------------------------+----------------------------+----------------------------+ | id | name | email | password_digest | created_at | updated_at | +----+------------+-------------------+--------------------------------------------------------------+----------------------------+----------------------------+ | 1 | sample1_18 | wanko1@prog-8.com | $2a$12$WbBJmW91Y4qDNBqy/h17A.XHpknRAD4NHtgXGO6YURP3U6IuKUMeS | 2021-05-09 04:45:52.315260 | 2021-07-25 04:43:25.422336 | | 3 | aaaaa | wanko5@prog-8.com | $2a$12$PBkaJE.sc5VI2Nl5UsBLle.rmUbmxkz0NDTSUXoaOcQ4MtKGc1ox. | 2021-06-21 22:02:01.573754 | 2021-06-21 22:02:01.573754 | | 4 | aaaaa | wanko6@prog-8.com | $2a$12$jKle2Pi5vIrNQ3tacVeo8ugTpy/IzR37Heyq3q76pvlmc3.joWl2G | 2021-06-23 22:18:20.534653 | 2021-06-23 22:18:20.534653 | +----+------------+-------------------+--------------------------------------------------------------+----------------------------+----------------------------+ 4 rows in set (0.01 sec) 参考:https://oreno-it3.info/archives/853
  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む

SQLのCASE式を使って異なる条件の集計を1つのSQLで行うテクニック

ポイント 異なる条件の集計として、CASE式はよく用いられるらしい 例えば、以下のリンクような都道府県テーブルと、市区町村テーブルがあり そのに店舗テーブル(仮にshopsとする)をJOINさせたとする 都道府県や市区町村ごとではなく地方ごとに集計したい場合 name 件数 shop.idが1000以下のデータ件数 shop.idが1000より高いデータ件数 三重県 12 0 12 京都府 34 0 34 佐賀県 24 0 24 兵庫県 8 0 8 北海道 61 10 51 ... こんなデータを出したいとします。 case式が役に立ちます shop.idが1000以下と以上で比較したいケースなんてないだろう。。。 そんな方は、例えばエリアごと人口で女性男性別に集計するイメージを持ってほしいです。 実例 SELECT p.name, count(s.id) AS '件数', COUNT(CASE WHEN s.id <= 1000 THEN s.id ELSE NULL END) AS 'idが1000以下のデータ件数', COUNT(CASE WHEN s.id > 1000 THEN s.id ELSE NULL END) AS 'idが1000より高いデータ件数' FROM prefectures AS p -- 色んなテーブルを紐づけていく -- LEFT JOIN cities AS c ON c.prefecture_id = p.id LEFT JOIN shops AS s ON s.city_id = c.id -- グループ化 GROUP BY p.name ポイント SQLのUNIONとかせずに済む CASE式の方が1回のSQLで済む クロス表形式で結果が出力されるので、非エンジニアにデータだけcvsエクスポートして渡すときを考えると 分析者が容易に分析できるシートになっている なぜなら、カテゴリを列で持たせたから 参照 8-11p
  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む