20200209のMySQLに関する記事は9件です。

別の Docker コンテナから mysqldump !!

ローカル環境で開発中に今のデータベースの状態保存しておきたい!
ってことたまにありますよね。

そんな時、汎用的に使えるコンテナ用意してみました :sunglasses:

MySQL の Docker コンテナの中でそれやればよくね?
っていうツッコミはご勘弁ください m(_ _)m 笑

デモ用のプロジェクト

こちら

ディレクトリ構成

operate_mysql_from_external
 - docker-compose.yml
 - .env
 - mysqldump
    - Dockerfile
    - mysqldump.sh
    - dumpfiles
       - 

docker-compose.yml

自動で .env ファイルを読み込んでくれるのは楽チンですよね。

ちなみにここで環境変数の受け渡しをしているのは、環境変数を設定せずに実行した時に下記のようなエラーを出してくれるからです。 docker-compose 最高ですね :relaxed:
You need to set the {環境変数名} environment variable.

version: '3'

services:
  mysqldump:
    build: mysqldump
    container_name: "demo-mysqldump"
    volumes:
      - './mysqldump/mysqldump.sh:/mysqldump.sh'
      - './mysqldump/dumpfiles:/dumpfiles'
    environment:
      MYSQL_HOST: "${MYSQL_HOST}"
      MYSQL_PORT: "${MYSQL_PORT}"
      MYSQL_USER: "${MYSQL_USER}"
      MYSQL_PASSWORD: "${MYSQL_PASSWORD}"
      MYSQLDUMP_OPTIONS: "${MYSQLDUMP_OPTIONS}"
      MYSQLDUMP_DATABASE_OPTION: "${MYSQLDUMP_DATABASE_OPTION}"
    command: sh mysqldump.sh

networks:
  dafault:
    external:
      name: "${MYSQL_NETWORK}"

参考
ネットワークの external 設定について
既存のネットワークを使う

「MySQL Docker コンテナが使用しているネットワーク名」については

docker network ls

を使って確認してみてください。

特別なことをしていなければ
{プロジェクト名}_default
もしくは
{プロジェクト名}_{ docker-compose.yml で指定しているネットワーク名}
となっているはずです!

.env

コンテナ内で使用する環境変数を記述しておきます。
以下はサンプルなので必要に応じて変更してください :robot:

MYSQL_HOST=mysql
MYSQL_PORT=3306
MYSQL_USER=user
MYSQL_PASSWORD=password

# MySQL Docker コンテナが使用しているネットワーク名
MYSQL_NETWORK=demo_mysql

MYSQLDUMP_OPTIONS=--quick --single-transaction
MYSQLDUMP_DATABASE_OPTION=demo

mysqldump

mysqldump する Docker コンテナ関連をまとめたディレクトリです。

Dockerfile

軽量の alpine に mysql-client を入れて使います。

FROM alpine:3.11

RUN apk update \
    && apk add mysql-client \
    && rm -rf /var/cache/apk/*

mysqldump.sh

mysqldump の処理を行うスクリプトです。

#!/bin/sh

# コマンドが失敗したら終了
set -e

echo "Domping for ${MYSQLDUMP_DATABASE_OPTION} from ${MYSQL_HOST}..."

readonly local OUTPUT_FILE="/dumpfiles/$(date +"%Y-%m-%dT%H%M%SZ").dump.sql"
readonly local MYSQL_HOST_OPTS="-h ${MYSQL_HOST} -P ${MYSQL_PORT} -u${MYSQL_USER} -p${MYSQL_PASSWORD}"

mysqldump ${MYSQL_HOST_OPTS} ${MYSQLDUMP_OPTIONS} ${MYSQLDUMP_DATABASE_OPTION} > "${OUTPUT_FILE}"

echo "Successfully dumped"

dumpfiles

dump したファイルたちがここに出力されます。

使い方

  1. MySQL の Docker コンテナが起動しているか確認
  2. .env に環境変数を設定する
  3. プロジェクトディレクトリで下記のコマンドを実行
# --rm を付けると実行後に自動でコンテナを削除してくれるよ
docker-compose run --rm mysqldump

おわりに

いかがでしょうか。
無事に mysqldump できましたか?
もし、こうした方がいいよ〜なんてことがあればコメントお待ちしています ( ^ ^ )/

参考

docker compose run について
mysqldump 公式リファレンス

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む

Raspberry Piを移行するついでにDjangoのDBをSQLite3からMySQL on Dockerに移行した

今回の目的

ちょっとごちゃごちゃしてます。

  1. Raspberry Pi 3B+で(直接)動いていた自分用DjangoプロジェクトをRaspberry Pi 4Bに移行
  2. そのSQLite3 DBが肥大化してきた(vacuumして120MBくらい)のと並行性確保のためMySQLに移行
  3. せっかくなのでDjangoプロジェクトをdocker-composeで動かすようにする
  4. MySQLもdocker-composeで動かそう
  5. DBを自動で更新する定期実行スクリプトもdocker-composeで動かそう

1. Raspberry Pi 4Bの初期セットアップ

まずはRaspberry Pi 4Bをセットアップする。

3B+で動いていたStretchは4Bでは動かないというような話を聞いたので、Download Raspbian for Raspberry Piから新しいRaspbian Busterを持ってきてMicro SDカードに焼く。メモリ節約(増えたけど)できそうなのでBusterはDesktopではなくLiteにした(どうせサーバ用途)。

mini HDMIケーブル/アダプタの持ち合わせがなかった(つらい、Microならあるのに...)ので、モニタレスセットアップする。100均で売ってるので必要になったら調達。

Micro SDカードを本体に挿し、LANケーブルでルータに接続。5V 3A Type-C電源(つらい、Microから買い替え)を接続して電源を入れる(Micro USBとType-Cの変換も100均にありそうなので電源を買い替えたくなければ... ちょっとこわいけど)。

ルータからDHCPで割り当てられたIPを調べて、LAN内でSSH接続(初期パスワード)。

Too many authentication failuresと言われたり、publickeyで弾かれるときは公開鍵で認証しようとして失敗してるので、-o PreferredAuthentications=password-o PubkeyAuthentication=noをsshコマンドのオプションに加えるか、~/.ssh/configPreferredAuthentications passwordPubkeyAuthentication noを加える。

パスワード変更・ユーザ名変更

パスワード変更、それから同時にユーザ名を変更する。piユーザにログインしたままだとユーザ名を変更できないので、新しいsudoerなユーザを作成してログインし直す(今回はLAN内なので一時的にrootパスワードを設定してもいいかもだけど)。

piユーザで以下のコマンドを実行してtmpuserを作成。

# useraddの場合ホームディレクトリは作られない
sudo useradd tmpuser
sudo passwd tmpuser

次にtmpuserをsudoersに追加する。

sudo adduser tmpuser sudo

ちょっと回り道をしたい場合、/etc/sudoersを編集してtmpuserを追加する。なんか/etc/sudoers他がreadonlyになってるので(chmodしてもいいけど)、/etc/sudoers.d/011_tmpuserを作る(sudoグループに追加でもいける)。

# /etc/sudoers.d/011_tmpuser
tmpuser ALL=(ALL:ALL) ALL

一度ログアウト、tmpuserユーザでログインし直して、以下のコマンドでpiユーザの名前を変更、piグループの名前も変更、最後にホームディレクトリを移動する。

sudo usermod -l NEW_NAME pi
sudo groupmod -n NEW_NAME pi
sudo usermod -m -d /home/NEW_NAME NEW_NAME

tmpuserユーザからログアウトして、NEW_NAMEユーザで再ログイン、tmpuserユーザを削除する。回り道をした場合、/etc/sudoers.d/011_tmpuserも削除する。なお、デフォルトでpiユーザがsudoグループに属しているので、NEW_NAMEユーザをあらためてsudoersに加える必要はない(はず)。

sudo userdel tmpuser
# sudo rm /etc/sudoers.d/011_tmpuser

ホスト名変更

# /etc/hostname
NEW_HOSTNAME

# /etc/hosts
...
127.0.1.1 NEW_HOSTNAME

公開鍵認証

NEW_NAMEユーザに公開鍵を登録、/etc/ssh/sshd_configを編集してSSHの認証を公開鍵のみにする。

pi側

mkdir ~/.ssh
chmod 700 ~/.ssh

ホスト側

cd ~/.ssh
ssh-keygen -f KEY_NAME
scp KEY_NAME.pub RPI4_HOST:.ssh/

pi側

cd ~/.ssh
cat KEY_NAME.pub >> authorized_keys
chmod 600 authorized_keys

あとは~/.ssh/configでIdentityFileを指定すればよい。やはりToo many authentication failuresといわれるときは、IdentitiesOnly yesを追加する。

必要に応じて/etc/ssh/sshd_configを編集してPasswordAuthentication noを設定するなどする。

2. Raspberry Pi 4BにDocker/docker-composeを導入

sudo curl -fsSL https://get.docker.com/ | sh
sudo apt install python3-pip
sudo apt install libffi-dev
sudo pip3 install docker-compose

3. DjangoプロジェクトのDocker/docker-compose移行

Djangoプロジェクトはgitで管理してたので、git cloneで新サーバにプログラムを移行。DB(SQLite3)はscpで移行。

旧サーバではvirtualenvで環境管理してたので、ここからrequirements.txtを生成。

pip3 freeze > requirements.txt

せっかくなので新サーバの環境構築はDocker/docker-composeで行う。django:wsgi-gunicorn-nginxの構成だったが、まずは単体で動作テスト。

# Dockerfile
FROM python:3
ENV PYTHONUNBUFFERED 1
RUN mkdir /code
WORKDIR /code
COPY requirements.txt /code/
RUN pip install -r requirements.txt
COPY . /code/
# docker-compose.yml
version: '3'

services:
    web:
        build: .
        command: python manage.py runserver 0.0.0.0:8000
        volumes:
            - .:/code
        ports:
            - "127.0.0.1:8000:8000"
        environment:
            - ENVIRONMENT=production
sudo docker-compose up

4. MySQLをDocker上で動かす(docker-compose/Raspberry Pi 4)

Raspberry Pi上のdocker-composeでMySQL(MariaDB)を動かす。jsurf/rpi-mariadbを使う。

...
    db:
        # image: mariadb
        image: jsurf/rpi-mariadb
        command: mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
        volumes:
            - DATABASE_DIRECTORY:/var/lib/mysql
        environment:
            - MYSQL_ROOT_PASSWORD=ROOT_PASSWORD
            - MYSQL_DATABASE=DATABASE_NAME
            - MYSQL_USER=USER
            - MYSQL_PASSWORD=PASSWORD
    web:
...

5. DjangoのDB設定をSQLite3からMySQLへ変更

あとで戻すので、簡単に戻せるようにしつつ、Djangoのsettings.pyを適当にいじって環境変数からDBを指定するようにする。

DATABASE_ENGINE = os.environ.get('DATABASE_ENGINE', 'django.db.backends.sqlite3')
DATABASE_OPTIONS = {}
if DATABASE_ENGINE == 'django.db.backends.mysql':
    DATABASE_OPTIONS = {
        'charset': os.environ.get('DATABASE_CHARSET'),
    }

DATABASES = {
    'default': {
        'ENGINE': DATABASE_ENGINE,
        'HOST': os.environ.get('DATABASE_HOST'),
        'PORT': os.environ.get('DATABASE_PORT'),
        'NAME': os.environ.get('DATABASE_NAME', os.path.join(BASE_DIR, 'db.sqlite3')),
        'USER': os.environ.get('DATABASE_USER'),
        'PASSWORD': os.environ.get('DATABASE_PASSWORD'),
        'OPTIONS': DATABASE_OPTIONS,
    },
}

docker-compose.ymlは以下のように編集。environmentのDATABASE部分をすべてコメントアウトするか、docker-compose.ymlを他に作ることでDBをSQLite3に戻せるようにしておく。

    web:
...
        environment:
            - ENVIRONMENT=production
            - DATABASE_ENGINE=django.db.backends.mysql
            - DATABASE_HOST=db
            - DATABASE_PORT=3306
            - DATABASE_NAME=DATABASE_NAME
            - DATABASE_USER=USER
            - DATABASE_PASSWORD=PASSWORD
            - DATABASE_CHARSET=utf8mb4
        depends_on:
            - db

requirements.txtPyMySQLを追記して、manage.pyの上部に以下を追記する。

if os.environ.get('DATABASE_ENGINE') == 'django.db.backends.mysql':
    import pymysql
    pymysql.install_as_MySQLdb()

なお、MySQL側の初期化が終わる前にDjango側がアクセスするとDjangoがエラー落ちするので、初回実行時はdocker-compose upを再実行する。2回目移行もDjangoが先に起動してしまう場合は、適当なsleepをはさむか待機スクリプトを作るなどして対処する。9番のセクションでgunicornをはさんだあとだと、Django(gunicorn)が先に起動してもエラーは出なくなった(みたいな)ので、あんまり気にしなくてもいいかもしれない。

        command: bash -c "sleep 5 && python manage.py runserver 0.0.0.0:8000"

6. Migration Errorの解消

DBモデルの定義によっては、sudo docker-compose up -dしてsudo docker-compose exec web python3 manage.py migrateするとエラーが出る。例えばunique制約付きのTextFieldがあって、max_lengthを指定していない場合。今回はURLをURLFieldではなくTextFieldに入れていたのをURLFieldに直し、また短い文字列とわかっているTextFieldにmax_length(255以下)を指定して解消した(ただし、Raspberry Pi上ではこれだけでは動かなかったので、あとで結局unique制約を外した)。

このあたりは高速化のためメイン機にプロジェクトとDBを移して実験しつつ行った。今回はMySQLのimageを変える必要があるが、(だいたい)同じ環境を自動で整えてくれて、しかもホストの環境を汚さない/影響を受けないのがDockerのいいところ(公式imageがないと互換性に悩まされるのが...?)。

7. DjangoのDBデータをjsonに書き出す

Migration Errorを解消したらDBをSQLite3に戻し、migrationしたあとでデータをjsonにダンプする。

sudo docker-compose run web bash
python3 manage.py makemigrations
python3 manage.py migrate
# python3 manage.py dumpdata > dump.json
python3 manage.py dumpdata --natural-foreign --natural-primary -e contenttypes -e auth.Permission > dump.json

8. DjangoのDBデータをjsonから書き戻す

python3 manage.py migrate
python3 manage.py loaddata dump.json
django.db.utils.IntegrityError: Problem installing fixture '/code/dump.json': Could not load APP.MODELNAME(pk=PK_NUM): (1062, "Duplicate entry 'ONE_FIELD_NUM' for key 'ONE_FIELD'")

OneToOneFieldにunique_together制約をかけていたのがよくなかった(OneToOneは多対1に使えない)みたいなので、ForeignKeyに変えた。また、この時点でmariadbでは動いたものの、jsurf/rpi-mariadbではutf8mb4にしているせいかKey lengthまわりのエラーが消えずに動かなかったので、すべての文字列のunique制約を外してしまうことにしたほか、こちらではmigrateが途中で止まってしまうのでmigrations以下のファイルを直接書き変えなければならなかった。別のPCで処理したDBを直接送りつけても動かなかったので、互換性にも不安が残るが...。何度も試行錯誤してようやくloaddataすることができた。

9. ホストのWebサーバ(リバースプロキシ)とDjangoの間にgunicornをはさむ

requirements.txtgunicornを追加する。

docker-compose.ymlを編集する。メモリを食う(と思う)のでワーカー数-wは必要に応じて調整する。

        # command: /usr/local/bin/gunicorn -w 4 -b 0.0.0.0:8000 MY_PROJECT.wsgi -t 300
        command: bash -c "sleep 5 && gunicorn -w 4 -b 0.0.0.0:8000 MY_PROJECT.wsgi -t 300"

manage.pyと同様に、wsgi.pyの上部に以下を追加する。

if os.environ.get('DATABASE_ENGINE') == 'django.db.backends.mysql':
    import pymysql
    pymysql.install_as_MySQLdb()

10. 定期実行スクリプトをDocker上で動かす

今回はDjangoと同じコンテナ内で定期実行を走らせる。

定期実行スクリプトはこれまでだいたいpythonでループを書いちゃうか、systemdのtimerで動かしていた。今回のものはsystemd/timerだったので、これをDockerコンテナ内に移行しようとしたが、ホストからDockerコンテナ内のスクリプトを動かすのはexecでできるものの、Dockerコンテナ内でsystemd/timerを動かすのはよくわからない。

ともあれ、python:3のベースOSはDebianでsystemdがなさそう(init.d)なので、cronで定期実行することにする。

cronを使うのは初めてなのもあって、そうとう迷走した。

Dockerで指定した環境変数を引き継ぎたいので、busyboxに含まれるcrondを使う。

まず、実行ディレクトリに以下のようなファイルcrontabを作成する。

# * * * * * cd /code && echo `env` >> env.txt
0 */6 * * * cd /code && /usr/bin/python3 AUTORUN_SCRIPT.py
# empty line

上は1分ごとに環境変数をファイルに書き出す設定(デバッグ用)で、下は6時間ごとに/code/AUTORUN_SCRIPT.pyをrootユーザ、ワーキングディレクトリ/codeで自動実行する設定。時刻はJSTでOK。

次に、Dockerfileにcrondのインストールと設定ファイルの追加を定義する。/var/spool/cron/crontabs/rootはディレクトリではなくファイルになるのが正解。

# Dockerfile
...
RUN apt update && apt install -y \
  busybox-static
ENV TZ Asia/Tokyo
COPY crontab /var/spool/cron/crontabs/root
...

それから、Dockerコンテナ起動時にcrondが起動するようにする。今回はdocker-composeを使っているので、Dockerfile内のCMDは実行されないのに注意。代わりに、docker-compose.yml内のcommandにcrondの起動コマンドを追加する。crondはバックグラウンド実行されるので、続けて自動的にgunicornが起動する。

# docker-compose.yml
...
        # command: bash -c "busybox crond && gunicorn -w 4 -b 0.0.0.0:8000 MY_PROJECT.wsgi -t 300"
        command: bash -c "sleep 5 && busybox crond && gunicorn -w 4 -b 0.0.0.0:8000 MY_PROJECT.wsgi -t 300"
...

DjangoのDBをいじるのでAUTORUN_SCRIPT.py中にpymysqlのinstallを追加する(manage.pywsgi.pyと同じ)などして、無事動作することが確認できた。デバッグ用のcron設定を消して設定完了。

11. 起動時自動実行とバックグラウンド実行

docker-compose.ymlrestart: alwaysを追加してホスト起動時に自動的に開始するようにして、sudo docker-compose up -dでバックグラウンドで起動。あとはホストをrebootしても大丈夫(sudo docker-compose pssudo docker ps)。

結果

無事にハードウェア(Raspberry Pi)の移行、DBの移行、DBエンジンの移行、Dockerへの移行&永続化に成功した。

ハードウェアのスペック向上、MySQLへの移行によってパフォーマンスが改善した(ように思われる)ほか、DB操作を並行して行うことができるようになった(みたいな)ので、同時にDBにアクセスしたときに発生していたDatabase is lockedエラーが見られなくなった。

個人用プロジェクトなのでログ設定端折ったりだとかunique切ったりだとかしてるけど...。ここはそうとう時間かけたのであきらめた。ただ、loaddataしたあとでならmigrationでunique戻したりできるかもしれない。

あとはcronを別のコンテナに分けた方がいいのかな、と思いつつ、Djangoプロジェクトとまったく同じ依存関係なので分けずにまとめてしまった。これどう分けるんだ...?

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む

DjangoのDBをSQLite3からMySQL on Dockerに移行した on Raspberry Pi 4B

今回の目的

ちょっとごちゃごちゃしてます。

  1. Raspberry Pi 3B+で(直接)動いていた自分用DjangoプロジェクトをRaspberry Pi 4Bに移行
  2. そのSQLite3 DBが肥大化してきた(vacuumして120MBくらい)のと並行性確保のためMySQLに移行
  3. せっかくなのでDjangoプロジェクトをdocker-composeで動かすようにする
  4. MySQLもdocker-composeで動かそう
  5. DBを自動で更新する定期実行スクリプトもdocker-composeで動かそう

1. Raspberry Pi 4Bの初期セットアップ

まずはRaspberry Pi 4Bをセットアップする。

3B+で動いていたStretchは4Bでは動かないというような話を聞いたので、Download Raspbian for Raspberry Piから新しいRaspbian Busterを持ってきてMicro SDカードに焼く。メモリ節約(増えたけど)できそうなのでBusterはDesktopではなくLiteにした(どうせサーバ用途)。

mini HDMIケーブル/アダプタの持ち合わせがなかった(つらい、Microならあるのに...)ので、モニタレスセットアップする。100均で売ってるので必要になったら調達。

Micro SDカードを本体に挿し、LANケーブルでルータに接続。5V 3A Type-C電源(つらい、Microから買い替え)を接続して電源を入れる(Micro USBとType-Cの変換も100均にありそうなので電源を買い替えたくなければ... ちょっとこわいけど)。

ルータからDHCPで割り当てられたIPを調べて、LAN内でSSH接続(初期パスワード)。

Too many authentication failuresと言われたり、publickeyで弾かれるときは公開鍵で認証しようとして失敗してるので、-o PreferredAuthentications=password-o PubkeyAuthentication=noをsshコマンドのオプションに加えるか、~/.ssh/configPreferredAuthentications passwordPubkeyAuthentication noを加える。

パスワード変更・ユーザ名変更

パスワード変更、それから同時にユーザ名を変更する。piユーザにログインしたままだとユーザ名を変更できないので、新しいsudoerなユーザを作成してログインし直す(今回はLAN内なので一時的にrootパスワードを設定してもいいかもだけど)。

piユーザで以下のコマンドを実行してtmpuserを作成。

# useraddの場合ホームディレクトリは作られない
sudo useradd tmpuser
sudo passwd tmpuser

次にtmpuserをsudoersに追加する。

sudo adduser tmpuser sudo

ちょっと回り道をしたい場合、/etc/sudoersを編集してtmpuserを追加する。なんか/etc/sudoers他がreadonlyになってるので(chmodしてもいいけど)、/etc/sudoers.d/011_tmpuserを作る(sudoグループに追加でもいける)。

# /etc/sudoers.d/011_tmpuser
tmpuser ALL=(ALL:ALL) ALL

一度ログアウト、tmpuserユーザでログインし直して、以下のコマンドでpiユーザの名前を変更、piグループの名前も変更、最後にホームディレクトリを移動する。

sudo usermod -l NEW_NAME pi
sudo groupmod -n NEW_NAME pi
sudo usermod -m -d /home/NEW_NAME NEW_NAME

tmpuserユーザからログアウトして、NEW_NAMEユーザで再ログイン、tmpuserユーザを削除する。回り道をした場合、/etc/sudoers.d/011_tmpuserも削除する。なお、デフォルトでpiユーザがsudoグループに属しているので、NEW_NAMEユーザをあらためてsudoersに加える必要はない(はず)。

sudo userdel tmpuser
# sudo rm /etc/sudoers.d/011_tmpuser

ホスト名変更

# /etc/hostname
NEW_HOSTNAME

# /etc/hosts
...
127.0.1.1 NEW_HOSTNAME

公開鍵認証

NEW_NAMEユーザに公開鍵を登録、/etc/ssh/sshd_configを編集してSSHの認証を公開鍵のみにする。

pi側

mkdir ~/.ssh
chmod 700 ~/.ssh

ホスト側

cd ~/.ssh
ssh-keygen -f KEY_NAME
scp KEY_NAME.pub RPI4_HOST:.ssh/

pi側

cd ~/.ssh
cat KEY_NAME.pub >> authorized_keys
chmod 600 authorized_keys

あとは~/.ssh/configでIdentityFileを指定すればよい。やはりToo many authentication failuresといわれるときは、IdentitiesOnly yesを追加する。

必要に応じて/etc/ssh/sshd_configを編集してPasswordAuthentication noを設定するなどする。

2. Raspberry Pi 4BにDocker/docker-composeを導入

sudo curl -fsSL https://get.docker.com/ | sh
sudo apt install python3-pip
sudo apt install libffi-dev
sudo pip3 install docker-compose

3. DjangoプロジェクトのDocker/docker-compose移行

Djangoプロジェクトはgitで管理してたので、git cloneで新サーバにプログラムを移行。DB(SQLite3)はscpで移行。

旧サーバではvirtualenvで環境管理してたので、ここからrequirements.txtを生成。

pip3 freeze > requirements.txt

せっかくなので新サーバの環境構築はDocker/docker-composeで行う。django:wsgi-gunicorn-nginxの構成だったが、まずは単体で動作テスト。

# Dockerfile
FROM python:3
ENV PYTHONUNBUFFERED 1
RUN mkdir /code
WORKDIR /code
COPY requirements.txt /code/
RUN pip install -r requirements.txt
COPY . /code/
# docker-compose.yml
version: '3'

services:
    web:
        build: .
        command: python manage.py runserver 0.0.0.0:8000
        volumes:
            - .:/code
        ports:
            - "127.0.0.1:8000:8000"
        environment:
            - ENVIRONMENT=production
sudo docker-compose up

4. MySQLをDocker上で動かす(docker-compose/Raspberry Pi 4)

Raspberry Pi上のdocker-composeでMySQL(MariaDB)を動かす。jsurf/rpi-mariadbを使う。

...
    db:
        # image: mariadb
        image: jsurf/rpi-mariadb
        command: mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
        volumes:
            - DATABASE_DIRECTORY:/var/lib/mysql
        environment:
            - MYSQL_ROOT_PASSWORD=ROOT_PASSWORD
            - MYSQL_DATABASE=DATABASE_NAME
            - MYSQL_USER=USER
            - MYSQL_PASSWORD=PASSWORD
    web:
...

5. DjangoのDB設定をSQLite3からMySQLへ変更

あとで戻すので、簡単に戻せるようにしつつ、Djangoのsettings.pyを適当にいじって環境変数からDBを指定するようにする。

DATABASE_ENGINE = os.environ.get('DATABASE_ENGINE', 'django.db.backends.sqlite3')
DATABASE_OPTIONS = {}
if DATABASE_ENGINE == 'django.db.backends.mysql':
    DATABASE_OPTIONS = {
        'charset': os.environ.get('DATABASE_CHARSET'),
    }

DATABASES = {
    'default': {
        'ENGINE': DATABASE_ENGINE,
        'HOST': os.environ.get('DATABASE_HOST'),
        'PORT': os.environ.get('DATABASE_PORT'),
        'NAME': os.environ.get('DATABASE_NAME', os.path.join(BASE_DIR, 'db.sqlite3')),
        'USER': os.environ.get('DATABASE_USER'),
        'PASSWORD': os.environ.get('DATABASE_PASSWORD'),
        'OPTIONS': DATABASE_OPTIONS,
    },
}

docker-compose.ymlは以下のように編集。environmentのDATABASE部分をすべてコメントアウトするか、docker-compose.ymlを他に作ることでDBをSQLite3に戻せるようにしておく。

    web:
...
        environment:
            - ENVIRONMENT=production
            - DATABASE_ENGINE=django.db.backends.mysql
            - DATABASE_HOST=db
            - DATABASE_PORT=3306
            - DATABASE_NAME=DATABASE_NAME
            - DATABASE_USER=USER
            - DATABASE_PASSWORD=PASSWORD
            - DATABASE_CHARSET=utf8mb4
        depends_on:
            - db

requirements.txtPyMySQLを追記して、manage.pyの上部に以下を追記する。

if os.environ.get('DATABASE_ENGINE') == 'django.db.backends.mysql':
    import pymysql
    pymysql.install_as_MySQLdb()

なお、MySQL側の初期化が終わる前にDjango側がアクセスするとDjangoがエラー落ちするので、初回実行時はdocker-compose upを再実行する。2回目移行もDjangoが先に起動してしまう場合は、適当なsleepをはさむか待機スクリプトを作るなどして対処する。9番のセクションでgunicornをはさんだあとだと、Django(gunicorn)が先に起動してもエラーは出なくなった(みたいな)ので、あんまり気にしなくてもいいかもしれない。

        command: bash -c "sleep 5 && python manage.py runserver 0.0.0.0:8000"

6. Migration Errorの解消

DBモデルの定義によっては、sudo docker-compose up -dしてsudo docker-compose exec web python3 manage.py migrateするとエラーが出る。例えばunique制約付きのTextFieldがあって、max_lengthを指定していない場合。今回はURLをURLFieldではなくTextFieldに入れていたのをURLFieldに直し、また短い文字列とわかっているTextFieldにmax_length(255以下)を指定して解消した(ただし、Raspberry Pi上ではこれだけでは動かなかったので、あとで結局unique制約を外した)。

このあたりは高速化のためメイン機にプロジェクトとDBを移して実験しつつ行った。今回はMySQLのimageを変える必要があるが、(だいたい)同じ環境を自動で整えてくれて、しかもホストの環境を汚さない/影響を受けないのがDockerのいいところ(公式imageがないと互換性に悩まされるのが...?)。

7. DjangoのDBデータをjsonに書き出す

Migration Errorを解消したらDBをSQLite3に戻し、migrationしたあとでデータをjsonにダンプする。

sudo docker-compose run web bash
python3 manage.py makemigrations
python3 manage.py migrate
# python3 manage.py dumpdata > dump.json
python3 manage.py dumpdata --natural-foreign --natural-primary -e contenttypes -e auth.Permission > dump.json

8. DjangoのDBデータをjsonから書き戻す

python3 manage.py migrate
python3 manage.py loaddata dump.json
django.db.utils.IntegrityError: Problem installing fixture '/code/dump.json': Could not load APP.MODELNAME(pk=PK_NUM): (1062, "Duplicate entry 'ONE_FIELD_NUM' for key 'ONE_FIELD'")

OneToOneFieldにunique_together制約をかけていたのがよくなかった(OneToOneは多対1に使えない)みたいなので、ForeignKeyに変えた。また、この時点でmariadbでは動いたものの、jsurf/rpi-mariadbではutf8mb4にしているせいかKey lengthまわりのエラーが消えずに動かなかったので、すべての文字列のunique制約を外してしまうことにしたほか、こちらではmigrateが途中で止まってしまうのでmigrations以下のファイルを直接書き変えなければならなかった。別のPCで処理したDBを直接送りつけても動かなかったので、互換性にも不安が残るが...。何度も試行錯誤してようやくloaddataすることができた。

9. ホストのWebサーバ(リバースプロキシ)とDjangoの間にgunicornをはさむ

requirements.txtgunicornを追加する。

docker-compose.ymlを編集する。メモリを食う(と思う)のでワーカー数-wは必要に応じて調整する。

        # command: /usr/local/bin/gunicorn -w 4 -b 0.0.0.0:8000 MY_PROJECT.wsgi -t 300
        command: bash -c "sleep 5 && gunicorn -w 4 -b 0.0.0.0:8000 MY_PROJECT.wsgi -t 300"

manage.pyと同様に、wsgi.pyの上部に以下を追加する。

if os.environ.get('DATABASE_ENGINE') == 'django.db.backends.mysql':
    import pymysql
    pymysql.install_as_MySQLdb()

10. 定期実行スクリプトをDocker上で動かす

今回はDjangoと同じコンテナ内で定期実行を走らせる。

定期実行スクリプトはこれまでだいたいpythonでループを書いちゃうか、systemdのtimerで動かしていた。今回のものはsystemd/timerだったので、これをDockerコンテナ内に移行しようとしたが、ホストからDockerコンテナ内のスクリプトを動かすのはexecでできるものの、Dockerコンテナ内でsystemd/timerを動かすのはよくわからない。

ともあれ、python:3のベースOSはDebianでsystemdがなさそう(init.d)なので、cronで定期実行することにする。

cronを使うのは初めてなのもあって、そうとう迷走した。

Dockerで指定した環境変数を引き継ぎたいので、busyboxに含まれるcrondを使う。

まず、実行ディレクトリに以下のようなファイルcrontabを作成する。

# * * * * * cd /code && echo `env` >> env.txt
0 */6 * * * cd /code && /usr/bin/python3 AUTORUN_SCRIPT.py
# empty line

上は1分ごとに環境変数をファイルに書き出す設定(デバッグ用)で、下は6時間ごとに/code/AUTORUN_SCRIPT.pyをrootユーザ、ワーキングディレクトリ/codeで自動実行する設定。時刻はJSTでOK。

次に、Dockerfileにcrondのインストールと設定ファイルの追加を定義する。/var/spool/cron/crontabs/rootはディレクトリではなくファイルになるのが正解。

# Dockerfile
...
RUN apt update && apt install -y \
  busybox-static
ENV TZ Asia/Tokyo
COPY crontab /var/spool/cron/crontabs/root
...

それから、Dockerコンテナ起動時にcrondが起動するようにする。今回はdocker-composeを使っているので、Dockerfile内のCMDは実行されないのに注意。代わりに、docker-compose.yml内のcommandにcrondの起動コマンドを追加する。crondはバックグラウンド実行されるので、続けて自動的にgunicornが起動する。

# docker-compose.yml
...
        # command: bash -c "busybox crond && gunicorn -w 4 -b 0.0.0.0:8000 MY_PROJECT.wsgi -t 300"
        command: bash -c "sleep 5 && busybox crond && gunicorn -w 4 -b 0.0.0.0:8000 MY_PROJECT.wsgi -t 300"
...

DjangoのDBをいじるのでAUTORUN_SCRIPT.py中にpymysqlのinstallを追加する(manage.pywsgi.pyと同じ)などして、無事動作することが確認できた。デバッグ用のcron設定を消して設定完了。

11. 起動時自動実行とバックグラウンド実行

docker-compose.ymlrestart: alwaysを追加してホスト起動時に自動的に開始するようにして、sudo docker-compose up -dでバックグラウンドで起動。あとはホストをrebootしても大丈夫(sudo docker-compose pssudo docker ps)。

結果

無事にハードウェア(Raspberry Pi)の移行、DBの移行、DBエンジンの移行、Dockerへの移行&永続化に成功した。

ハードウェアのスペック向上、MySQLへの移行によってパフォーマンスが改善した(ように思われる)ほか、DB操作を並行して行うことができるようになった(みたいな)ので、同時にDBにアクセスしたときに発生していたDatabase is lockedエラーが見られなくなった。

個人用プロジェクトなのでログ設定端折ったりだとかunique切ったりだとかしてるけど...。ここはそうとう時間かけたのであきらめた。ただ、loaddataしたあとでならmigrationでunique戻したりできるかもしれない。

あとはcronを別のコンテナに分けた方がいいのかな、と思いつつ、Djangoプロジェクトとまったく同じ依存関係なので分けずにまとめてしまった。これどう分けるんだ...?

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む

MySQLコマンド(基本)

はじめに

今回は基本的なMySQLコマンドについて紹介します。

MySQLコマンド

ここからコマンドをどんどん列挙していきます。

・ログイン

# localhostのMySQLサーバに接続する場合
$ mysql -u [ユーザー名] -p

# 外部MySQLサーバに接続する場合
$ mysql -u [ユーザー名] -p -h [host名] -P [ポート番号]

・ログアウト

#複数あるのでいろいろためしてみてください。
mysql > \q
mysql > quit
mysql > exit

・データベースの確認

mysql > show databases;

・データベースの追加

mysql > create database 【追加するDB名】;

・データベースの選択

mysql > use  【選択したいDB名】;

・テーブル一覧の確認

mysql > show tables;

さいごに

次回の記事で(更新削除など含む)テーブル作成についてまとめたいと思います。

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む

MySQL5.7を起動時に起こるエラー「ERROR! The server quit without updating PID file」に関して

はじめに

MacOS内にMySQL8.0が入っていたが、MySQL5.7にダウングレードする必要があり、
MySQL8.0を完全にアンインストール後にMySQL5.7をHomebrewでインストールした。

しかし、MySQLサーバー起動時に「ERROR! The server quit without updating PID file」というエラーが出て起動できなかったので、その解決方を忘備録として残しておきます。

開発環境

Center align Center align
MacOS Mojave 10.14.6
Mysql 5.7.29

MySQL5.7のインストール

MacOS内にもともと入っていたMySQL8.0はこの記事を参考に削除したという前提でMySQL5.7をインストール

$ brew install mysql@5.7

$ vim .bash_profile

# .bash_profileに下記の一行を追加
export PATH="/usr/local/opt/mysql@5.7/bin:$PATH"

# .bash_profileの再読み込み
$ source .bash_profile

$ mysql.server start
Starting MySQL
.. ERROR! The server quit without updating PID file (/usr/local/var/mysql/ip-10-3-4-134.ap-northeast-1.compute.internal.pid).

解決方法

my.cnfファイルにskip-grant-tablesを追加したら、無事に起動しました。

$ vim /usr/local/etc/my.cnf

# Default Homebrew MySQL server config
[mysqld]
# Only allow connections from localhost
bind-address = 127.0.0.1
#mysqlx-bind-address = 127.0.0.1
#socket=/tmp/mysql.sock
skip-grant-tables  ←追記

$ mysql.server start                                                                                                                                                                              
Starting MySQL
. SUCCESS!

無事解決!!!

参考

MacでMySQL5.7をアンインストールする
MySQL5.7をHomebrewでmacOSにインストールする手順

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む

ModuleNotFoundError: No module named 'mysql.connector'; 'mysql' is not a package?

はじめに

Pythonを書き始めた頃に出会ったエラーが初学者の方の参考になると思い、記事にしました。

ModuleNotFoundError: No module named 'mysql.connector'; 'mysql' is not a package

mysql.png

Python3プログラム経由でMySQLに接続しようとした時、掲題の問題に出くわしました。
まず最初に、そのプログラムを実行する前にmysql-connector-python をインストールしていたことは確実でした。

pip3 install mysql-connector-python

(For readability, added line feed to each row)
Requirement already satisfied: mysql-connector-python in /opt/pypy3.6-v7.1.1-osx64/site-packages (8.0.17)
Requirement already satisfied: protobuf>=3.0.0 in /opt/pypy3.6-v7.1.1-osx64/site-packages (from mysql-connector-python) (3.9.1)
Requirement already satisfied: six>=1.9 in /opt/pypy3.6-v7.1.1-osx64/site-packages (from protobuf>=3.0.0->mysql-connector-python) (1.12.0)
Requirement already satisfied: setuptools in /opt/pypy3.6-v7.1.1-osx64/site-packages/setuptools-40.8.0-py3.6.egg (from protobuf>=3.0.0->mysql-connector-python) (40.8.0)

そのプログラム mysql.py は以下の通りでした:

import mysql.connector

if __name__ == '__main__':
    con = mysql.connector.connect(
        user='remote',
        password='password',
        host='0.0.0.0',
        database='mydb'
    )
    cur = con.cursor()
    cur.execute('select * from stock;')
    for row in cur.fetchall():
        print(row[0], row[1])
    cur.close()
    con.close()

そして、このプログラムをpypy3で実行しました。

pypy3 mysql.py

Traceback (most recent call last):
  File "mysql.py", line 1, in <module>
    import mysql.connector
  File "/Users/resotto/develop/sandbox/mysql/mysql.py", line 1, in <module>
    import mysql.connector
ModuleNotFoundError: No module named 'mysql.connector'; 'mysql' is not a package

この問題の原因は、このpython3プログラムのファイル名 mysql.py でした。

この問題を解決するために、私は次の二点を理解する必要がありました:

  • import 宣言が使われた時、Pythonは現在のディレクトリから指定の package/module を探索する
  • .py ファイルは、それがPythonモジュールであることを意味する

connector モジュールは mysql パッケージの中にあり、そしてまた、私は自分の書いたpython3プログラムを mysql.py と命名しました。

つまり、mysql-connector-python module ではなく、自分の書いた mysql モジュールをインポートしていました。
だから上記のエラーは次のメッセージを伝えていました:

'mysql' is not a package

確かに、 'mysql' は 私が書いたモジュール でした。
従って、そのプログラムのファイル名を mysql_temp.py に変更すると、上手くいきました。

pypy3 mysql_temp.py
1 eraser
2 banana
3 apple
4 orange
5 ballpoint-pen

以上です!ご一読ありがとうございました。

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む

【go + gin + gorm】webアプリにログイン機能を追加してみる

【Go+Gin+Gorm】初心者だから超簡単webサービス作ってみるの続きです。

今回はgithubに上げておきました。
https://github.com/daichiiyamada/mytweet

ここではユーザー登録とログイン画面を作ります。
セッションはやりません(おそらく次回)。
ログイン画面からusernameとpasswordを入力してDBに存在していたらトップ画面にリダイレクトされるようになっています。

外部ライブラリのインストール

続きの方はターミナルを開いて以下を実行してください。
ここから始める方はmain.gocrypto/crypto.goのimportを見てgo runしてください。

// gormConnect()内で.envファイル(環境変数定義)から定数を取得するときに使います
go get github.com/joho/godotenv
// Usersテーブルにパスワードをそのまま保存するとセキュリティ的に危ないので、これを使って暗号化して保存します。
go get golang.org/x/crypto/bcrypt

.envファイル(環境変数定義)を使用する

先ほどインストールしたgodotenvは、go言語で.envファイルを使うためのライブラリです。
ハードコーディングを避けるため、今回は使ってみようと思います。
.envファイルの中に定数を記述して、コード内でその定数を使うことができます。使い方もクソもないかもですが、こちらを参照するとわかりやすいかもです。
main.goと同じ階層に.envファイルを作り、前回の続きの方は以下のように記述してください。
これらはMySQLのDB名だったり、ユーザー名だったりを記載しています。

.env
mytweet_DBMS=mysql
mytweet_USER=test
mytweet_PASS=12345678
mytweet_DBNAME=test

usernameをユニークに設定する

ログインを実装するにあたって、ログインフォームから受け取ったusernameをデータベースで検索して一意なユーザーを取得したいので、usernameはユニークである必要があります。
なのでmain.goファイルのUser構造体を変更しました。
gorm:"unique;not null"の部分です。
これによってアプリ側で、重複するUsernameを新たに登録しようとしてもデータベース側で弾けます。

db.AutoMigrate()はテーブルや不足しているカラムとインデックスのみ生成します。データ保護のため、既存のカラム型の変更や未使用のカラムの削除はしないので、前回と同じテーブルを使う方、申し訳ないんですが一度Usersテーブルをtruncateしてください。ここら辺railsはActive Recordがやってくれますよね。
AutoMigrateについて詳しくは公式リファレンスを参照ください。
プロダクト向きのマイグレーションツールが他にあるそうなので気になる方は調べてみてください。今回は規模が大きくないので、これでいきます。
Go言語で使えるmigrationライブラリ

ちなみに、プロダクト開発でgormを使おうと考えている方はこちらの記事を読んでみるといいかもしれません。
Go言語のGormを実践投入する時に最低限知っておくべきことのまとめ【ORM】

main.go
// User モデルの宣言
type User struct {
    gorm.Model
    Username string `form:"username" binding:"required" gorm:"unique;not null"`
    Password string `form:"password" binding:"required"`
}

ユーザー登録処理

ユーザー登録でアプリ側にさせることは、フォーム画面からユーザー名とパスワードを受け取ってDBに登録することです。

URLlocalhost:8080/signupでユーザー登録画面にいきます。トップ画面上から飛べるようにするのを忘れてました(笑)。
この画面を出すだけなら特に関数は必要ありません。
登録ボタンを押すとsignup.htmlフォーム内のaction="/signup"/signupにPOSTを投げるようにしています。

User型のformで構造体を定義し、Bind関数を使って、構造体で定義された内容と違ったデータが来てないか把握することができます。
気になる方は、GinでBindingが物珍しかったので他のフレームワークも調べてみたを読んでみるといいと思います。

フォームの内容は変数c内に格納されていて、PostForm()を使って値を取り出します。

main.go
// ユーザー登録画面
    router.GET("/signup", func(c *gin.Context) {

        c.HTML(200, "signup.html", gin.H{})
    })

    // ユーザー登録
    router.POST("/signup", func(c *gin.Context) {
        var form User
        // バリデーション処理
        if err := c.Bind(&form); err != nil {
            c.HTML(http.StatusBadRequest, "signup.html", gin.H{"err": err})
            c.Abort()
        } else {
            username := c.PostForm("username")
            password := c.PostForm("password")
            // 登録ユーザーが重複していた場合にはじく処理
            if err := createUser(username, password); err != nil {
                c.HTML(http.StatusBadRequest, "signup.html", gin.H{"err": err})
            }
            c.Redirect(302, "/")
        }
    })

取り出したusernameとpasswordをcreateUser関数に引数で渡します。
/crypto/crypto.go内のPasswordEncrypt()関数を使ってパスワードを暗号化します。
暗号化されたパスワードとユーザーネームをUsersテーブルに保存します。

package内の関数は、先頭文字を大文字にするとpublic関数になり、小文字にするとprivate関数になります。
PasswordEncrypt()関数は先頭が大文字のPなので、public関数ですね。

ユーザーが重複していたりして登録できなかったときにリダイレクトしたいので、GetErrors()でエラーを取得し、returnできるようにしています。
GORMのエラーハンドリングに関する記述

main.go
// ユーザー登録処理
func createUser(username string, password string) []error {
    passwordEncrypt, _ := crypto.PasswordEncrypt(password)
    db := gormConnect()
    defer db.Close()
    // Insert処理
    if err := db.Create(&User{Username: username, Password: passwordEncrypt}).GetErrors(); err != nil {
        return err
    }
    return nil

}

ログイン処理

ログイン処理でアプリ側でさせることは、ログインフォームから受け取ったユーザー名とパスワードがDBに同じく保存されているか探すことです。
流れとしては、

ログインフォームからユーザー名とパスワードを受け取る
↓
ユーザー名をもとにUsersテーブルからユーザーレコードを取得する
↓
ログインフォームから受け取ったパスワードとDBから取得したユーザーレコードのパスワードと比較
↓
トップ画面へリダイレクトまたはログイン画面に戻る

URLlocalhost:8080/loginでログイン画面にいきます。これまたトップ画面上から飛べるようにするのを忘れてました(笑)。
この画面を出すだけなら特に関数は必要ありません。
登録ボタンを押すとlogin.htmlフォーム内のaction="/login"/loginにPOSTを投げるようにしています。

POSTで受け取ったユーザー名の値をgetUser()に引数で入れ、DBからユーザーレコードを取得しています。取得したユーザーレコードはUser型として取得しているので、.Passwordでパスワードを取得できます。
パスワードの比較は、CompareHashAndPassword()の引数にdbPasswordformPasswordを入れることで比較することができます。この関数は、cryptoディレクトリのcrypto.goに定義されています。この関数のreturnはerror型なので、エラー内容があればif文で引っかかるようになってます。

main.go
// ユーザーログイン画面
    router.GET("/login", func(c *gin.Context) {

        c.HTML(200, "login.html", gin.H{})
    })

    // ユーザーログイン
    router.POST("/login", func(c *gin.Context) {

        // DBから取得したユーザーパスワード(Hash)
        dbPassword := getUser(c.PostForm("username")).Password
        log.Println(dbPassword)
        // フォームから取得したユーザーパスワード
        formPassword := c.PostForm("password")

        // ユーザーパスワードの比較
        if err := crypto.CompareHashAndPassword(dbPassword, formPassword); err != nil {
            log.Println("ログインできませんでした")
            c.HTML(http.StatusBadRequest, "login.html", gin.H{"err": err})
            c.Abort()
        } else {
            log.Println("ログインできました")
            c.Redirect(302, "/")
        }
    })

データベースからwhere句を使ってユーザーを1件取得する際に使ったのが、getUser内のdb.First()です。
db.Where()でも代用できると思われます。他にもdbにまつわる様々な関数があるので、詳しく知りたい方は以下を読んでみるといいかと思います。
参照1
参照2

db.First(&user, "username = ?", username)
↓
SELECT * FROM users WHERE username = "jinzhu";
main.go
// ユーザーを一件取得
func getUser(username string) User {
    db := gormConnect()
    var user User
    db.First(&user, "username = ?", username)
    db.Close()
    return user
}

起動

起動させていろいろ遊んでみましょう。
ターミナルを付けてmytweetディレクトリ内でgo run main.goをしてください。
localhost:8080/signupでユーザー登録
localhost:8080/loginでログイン
ですよ。
トップ画面から飛べるようにするの忘れてごめんなさい。

MySQLを別タブで起動してちゃんとユーザー登録できてるか、ログインできてるか確かめてみましょう。
ちなみに、SQL文って書き方よく忘れるよね。

// mysql起動
$ mysql -uroot -p

// testデータベースを選択
$ use test;

// usersテーブル全レコード表示
$ select * from users;

各自print文(log.Println())を使ってパスワードがちゃんとハッシュ化されてるかとか、ユーザー名がちゃんと取得できてるかとかみてみると勉強になるかと思います。

最後に

今回は、前回のコードにユーザー登録とログイン機能を追加してみました。
次回は、デプロイしてみたり、セッション機能を追加してみたりしたいと思ってます。

今回のコードはこちらから。
https://github.com/daichiiyamada/mytweet

cryptoの部分はこちらのサイトのコードを流用させていただきました。
Webアプリ初心者がGo言語でサーバサイド(2. パスワード認証機能の実装)

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む

League of Legendsのデータ取得して解析してみたい③

データベースに接続してデータを格納する

前回は自分が欲しいデータをあらかた抽出できたので、今回はそのデータをデータベースに保存していきたいと思います。
(前回の記事はこちらhttps://qiita.com/mattya_527/items/9b90451e94de246525a4)

環境

2020/2/8現在
OS:windows10
Anaconda:4.8.1
python:3.7.6
MySQL:8.0.19

データベースの設定

インストールなどの初期設定はこちらを参考にさせていただきました。(https://www.dbonline.jp/mysql/)

データベースの作成

mysql -u root -p
でpasswordを入力してmysqlを起動させます。
create database LOLdb;
でLOLdbという名前のデータベースを作成します。
show databases;
でデータベースの一覧を確認することができます。LOLdbがあればOK

データベースに接続する

まずは必要なモジュールをインストールします。
python -m pip mysql

早速Pythonのコードを書いていきます。

#MySQLdbのインポート
import MySQLdb 

#データベースへの接続とカーソルの生成
connection = MySQLdb.connect(
    host="localhost",
    user="root",
    passwd="{mysqlで設定したパスワード}",
    db="LOLdb",
    charset="utf8"
    )
cursor = connection.cursor()

これで先ほど作成したデータベースに接続することができました。次にテーブルを作成します。

#テーブルの初期化
cursor.execute("DROP TABLE IF EXISTS match_data")

#テーブルの作成
cursor.execute("""CREATE TABLE match_data(
               id INT(10) AUTO_INCREMENT NOT NULL,
               sn VARCHAR(20) NOT NULL COLLATE utf8mb4_unicode_ci,
               wol VARCHAR(20) NOT NULL COLLATE utf8mb4_unicode_ci,
               kills INT(10) NOT NULL,
               deaths INT(10) NOT NULL,
               assists INT(10) NOT NULL,
               championId  INT(10) NOT NULL,
               roles VARCHAR(20) NOT NULL COLLATE utf8mb4_unicode_ci,
               cs INT(10) NOT NULL,
               gold INT(10) NOT NULL,
               damage INT(10) NOT NULL,
               side VARCHAR(20) NOT NULL COLLATE utf8mb4_unicode_ci,
               game_time INT(10) NOT NULL,

               PRIMARY KEY(id)
               )""")

このスクリプトではmatch_dataというテーブルが存在した場合に削除してもう一度作成するという作業をしています。(完成したらこの作業はコメントアウトして処理しないようにします。)
テーブルの項目は前回にピックアップしたものにします。

#データの追加(テスト)
cursor.execute("INSERT INTO match_data(sn,wol,kills,deaths,assists,championId,roles,cs,gold,damage,side,game_time) VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)",data)


#データの一覧の表示
cursor.execute("SELECT * FROM match_data")
for row in cursor:
    print(row)


#保存を実行
connection.commit()
#接続を閉じる
connection.close()

テーブルに追加するときにdataというリストから抽出するようにしました。
必ずcommit()を忘れないようにしましょう。

これでテーブルの作成と追加、表示までができるようになります。追加するデータのコードを書いていきます。
このためには前回書いたコードを少し改良します。

###前回と同じ部分###
from riotwatcher import RiotWatcher

API_KEY = "{取得したAPIKEY}"
watcher = RiotWatcher(API_KEY)
region = "jp1" #サーバーの選択 日本鯖はjp1
name = "まっちゃくん" #サモナーネームの入力
summoner = watcher.summoner.by_name(region,name) #プレイヤーデータの取得
recentmatchlists = watcher.match.matchlist_by_account(region,summoner["accountId"]) #直近20試合のデータのリストを取得
matches = recentmatchlists["matches"]
match_data = watcher.match.by_id(region,matches[0]["gameId"]) #直近1試合だけを抽出

#サモナーネームが"まっちゃくん"のプレイヤーを抽出してそのpartipantsIdを返す
for i in range(10):
    match_data["participantIdentities"][i]["player"]
    if match_data["participantIdentities"][i]["player"]["summonerName"] == name: #サモナーネームが一致するか
        par_Id = match_data["participants"][i]["participantId"]

###前回までの部分###

###今回改良した部分###
data=[] #テーブルに追加するためのリストdataの用意
data.append(name) #nameの追加
# par_Idと一致するところのデータを出力
if match_data["participants"][par_Id-1]["stats"]["participantId"] == par_Id: #par_Id-1でインデックスと一致する
    if match_data["participants"][par_Id-1]["stats"]["win"] == True:#勝敗がTrueだったらwin,Falseだったらlose
        wol = "win"
    else:wol = "lose"
    data.append(wol) #wolの追加
    kills = match_data["participants"][par_Id-1]["stats"]["kills"] #キル数
    data.append(kills) #killsの追加
    deaths = match_data["participants"][par_Id-1]["stats"]["deaths"] #デス数
    data.append(deaths) #deathsの追加
    assists = match_data["participants"][par_Id-1]["stats"]["assists"] #アシスト数
    data.append(assists) #assistsの追加
    championId = match_data["participants"][par_Id-1]["championId"] #使用したチャンピオン
    data.append(championId) #championIdの追加(あとでチャンピオンのIDをチャンピオン名に変換する。)
    lane = match_data["participants"][par_Id-1]["timeline"]["lane"] #レーン
    role = match_data["participants"][par_Id-1]["timeline"]["role"] #ロール
    if role == "DUO_SUPPORT": #サポート
        roles = "SUP"
    elif role == "DUO_CARRY": #キャリー
        roles = "ADC"
    elif role == "SOLO": #ソロレーン
        if lane == "TOP": #トップ
            roles = "TOP"
        elif lane == "MIDDLE": #ミッド
            roles = "MID"
    elif role == "NONE":
        roles = "JG" #ジャングル
    data.append(roles) #rolesの追加(まずroleをみてSUPかADCかSOLOレーンか、ジャングルか判定できる。SOLOだったときにTOPかMIDを判別する。)
    cs = match_data["participants"][par_Id-1]["stats"]["totalMinionsKilled"] #CS **OPGGで見るCSには足りないためにCSに必要な項目がミニオンやジャングルクリープのほかにある?
    data.append(cs) #csの追加
    gold = match_data["participants"][par_Id-1]["stats"]["goldEarned"] #獲得ゴールド
    data.append(gold) #goldの追加
    damage = match_data["participants"][par_Id-1]["stats"]["magicDamageDealtToChampions"] + match_data["participants"][par_Id-1]["stats"]["physicalDamageDealtToChampions"] + match_data["participants"][par_Id-1]["stats"]["trueDamageDealtToChampions"] #チャンピオンへのダメージ
    data.append(damage) #damageの追加
    if match_data["participants"][par_Id-1]["teamId"] == 100: #100ならブルーサイド、200ならレッドサイド
        side = "RED"
    else: side = "BLUE"
    data.append(side) #sideの追加
    game_time = match_data["gameDuration"] #ゲーム時間(秒)
    data.append(game_time) #game_timeの追加(あとでゲーム時間を分に変換。)

append()を使うことによってリストの要素の最後に追加することができる。
このコードをデータベースを作成する位置の前に書いておく。

実行結果
image.png

MySQLのほうでも確認
image.png

これでテーブルを作成してデータを保存することができた。

データを複数個いれられるかの実験

これでデータを1つ格納することはできたが、複数いれられないとデータベースとしては機能できない。
てなわけで、先ほどのコードのテーブルの初期化と作成のコードをコメントアウトして消してみる。

実行結果
image.png

試しに試合のインデックスを変えて実行してみたが、ちゃんと格納することができている。

まとめ

MySQLを今回使ってみて結構簡単にPython側でいじれることが分かって楽しかったです。データベースの勉強をしてみたいという思いはあったけど踏みだせなかったのでいい機会になりました。次からは、OPGG的なWEBアプリを作ってみたいと思います。

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む

League of Legendsのデータ取得してみたい③

データベースに接続してデータを格納する

前回は自分が欲しいデータをあらかた抽出できたので、今回はそのデータをデータベースに保存していきたいと思います。
(前回の記事はこちらhttps://qiita.com/mattya_527/items/9b90451e94de246525a4)

環境

2020/2/8現在
OS:windows10
Anaconda:4.8.1
python:3.7.6
MySQL:8.0.19

データベースの設定

インストールなどの初期設定はこちらを参考にさせていただきました。(https://www.dbonline.jp/mysql/)

データベースの作成

mysql -u root -p
でpasswordを入力してmysqlを起動させます。
create database LOLdb;
でLOLdbという名前のデータベースを作成します。
show databases;
でデータベースの一覧を確認することができます。LOLdbがあればOK

データベースに接続する

まずは必要なモジュールをインストールします。
python -m pip mysql

早速Pythonのコードを書いていきます。

#MySQLdbのインポート
import MySQLdb 

#データベースへの接続とカーソルの生成
connection = MySQLdb.connect(
    host="localhost",
    user="root",
    passwd="{mysqlで設定したパスワード}",
    db="LOLdb",
    charset="utf8"
    )
cursor = connection.cursor()

これで先ほど作成したデータベースに接続することができました。次にテーブルを作成します。

#テーブルの初期化
cursor.execute("DROP TABLE IF EXISTS match_data")

#テーブルの作成
cursor.execute("""CREATE TABLE match_data(
               id INT(10) AUTO_INCREMENT NOT NULL,
               sn VARCHAR(20) NOT NULL COLLATE utf8mb4_unicode_ci,
               wol VARCHAR(20) NOT NULL COLLATE utf8mb4_unicode_ci,
               kills INT(10) NOT NULL,
               deaths INT(10) NOT NULL,
               assists INT(10) NOT NULL,
               championId  INT(10) NOT NULL,
               roles VARCHAR(20) NOT NULL COLLATE utf8mb4_unicode_ci,
               cs INT(10) NOT NULL,
               gold INT(10) NOT NULL,
               damage INT(10) NOT NULL,
               side VARCHAR(20) NOT NULL COLLATE utf8mb4_unicode_ci,
               game_time INT(10) NOT NULL,

               PRIMARY KEY(id)
               )""")

このスクリプトではmatch_dataというテーブルが存在した場合に削除してもう一度作成するという作業をしています。(完成したらこの作業はコメントアウトして処理しないようにします。)
テーブルの項目は前回にピックアップしたものにします。

#データの追加(テスト)
cursor.execute("INSERT INTO match_data(sn,wol,kills,deaths,assists,championId,roles,cs,gold,damage,side,game_time) VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)",data)


#データの一覧の表示
cursor.execute("SELECT * FROM match_data")
for row in cursor:
    print(row)


#保存を実行
connection.commit()
#接続を閉じる
connection.close()

テーブルに追加するときにdataというリストから抽出するようにしました。
必ずcommit()を忘れないようにしましょう。

これでテーブルの作成と追加、表示までができるようになります。追加するデータのコードを書いていきます。
このためには前回書いたコードを少し改良します。

###前回と同じ部分###
from riotwatcher import RiotWatcher

API_KEY = "{取得したAPIKEY}"
watcher = RiotWatcher(API_KEY)
region = "jp1" #サーバーの選択 日本鯖はjp1
name = "まっちゃくん" #サモナーネームの入力
summoner = watcher.summoner.by_name(region,name) #プレイヤーデータの取得
recentmatchlists = watcher.match.matchlist_by_account(region,summoner["accountId"]) #直近20試合のデータのリストを取得
matches = recentmatchlists["matches"]
match_data = watcher.match.by_id(region,matches[0]["gameId"]) #直近1試合だけを抽出

#サモナーネームが"まっちゃくん"のプレイヤーを抽出してそのpartipantsIdを返す
for i in range(10):
    match_data["participantIdentities"][i]["player"]
    if match_data["participantIdentities"][i]["player"]["summonerName"] == name: #サモナーネームが一致するか
        par_Id = match_data["participants"][i]["participantId"]

###前回までの部分###

###今回改良した部分###
data=[] #テーブルに追加するためのリストdataの用意
data.append(name) #nameの追加
# par_Idと一致するところのデータを出力
if match_data["participants"][par_Id-1]["stats"]["participantId"] == par_Id: #par_Id-1でインデックスと一致する
    if match_data["participants"][par_Id-1]["stats"]["win"] == True:#勝敗がTrueだったらwin,Falseだったらlose
        wol = "win"
    else:wol = "lose"
    data.append(wol) #wolの追加
    kills = match_data["participants"][par_Id-1]["stats"]["kills"] #キル数
    data.append(kills) #killsの追加
    deaths = match_data["participants"][par_Id-1]["stats"]["deaths"] #デス数
    data.append(deaths) #deathsの追加
    assists = match_data["participants"][par_Id-1]["stats"]["assists"] #アシスト数
    data.append(assists) #assistsの追加
    championId = match_data["participants"][par_Id-1]["championId"] #使用したチャンピオン
    data.append(championId) #championIdの追加(あとでチャンピオンのIDをチャンピオン名に変換する。)
    lane = match_data["participants"][par_Id-1]["timeline"]["lane"] #レーン
    role = match_data["participants"][par_Id-1]["timeline"]["role"] #ロール
    if role == "DUO_SUPPORT": #サポート
        roles = "SUP"
    elif role == "DUO_CARRY": #キャリー
        roles = "ADC"
    elif role == "SOLO": #ソロレーン
        if lane == "TOP": #トップ
            roles = "TOP"
        elif lane == "MIDDLE": #ミッド
            roles = "MID"
    elif role == "NONE":
        roles = "JG" #ジャングル
    data.append(roles) #rolesの追加(まずroleをみてSUPかADCかSOLOレーンか、ジャングルか判定できる。SOLOだったときにTOPかMIDを判別する。)
    cs = match_data["participants"][par_Id-1]["stats"]["totalMinionsKilled"] #CS **OPGGで見るCSには足りないためにCSに必要な項目がミニオンやジャングルクリープのほかにある?
    data.append(cs) #csの追加
    gold = match_data["participants"][par_Id-1]["stats"]["goldEarned"] #獲得ゴールド
    data.append(gold) #goldの追加
    damage = match_data["participants"][par_Id-1]["stats"]["magicDamageDealtToChampions"] + match_data["participants"][par_Id-1]["stats"]["physicalDamageDealtToChampions"] + match_data["participants"][par_Id-1]["stats"]["trueDamageDealtToChampions"] #チャンピオンへのダメージ
    data.append(damage) #damageの追加
    if match_data["participants"][par_Id-1]["teamId"] == 100: #100ならブルーサイド、200ならレッドサイド
        side = "RED"
    else: side = "BLUE"
    data.append(side) #sideの追加
    game_time = match_data["gameDuration"] #ゲーム時間(秒)
    data.append(game_time) #game_timeの追加(あとでゲーム時間を分に変換。)

append()を使うことによってリストの要素の最後に追加することができる。
このコードをデータベースを作成する位置の前に書いておく。

実行結果
image.png

MySQLのほうでも確認
image.png

これでテーブルを作成してデータを保存することができた。

データを複数個いれられるかの実験

これでデータを1つ格納することはできたが、複数いれられないとデータベースとしては機能できない。
てなわけで、先ほどのコードのテーブルの初期化と作成のコードをコメントアウトして消してみる。

実行結果
image.png

試しに試合のインデックスを変えて実行してみたが、ちゃんと格納することができている。

まとめ

MySQLを今回使ってみて結構簡単にPython側でいじれることが分かって楽しかったです。データベースの勉強をしてみたいという思いはあったけど踏みだせなかったのでいい機会になりました。次からは、OPGG的なWEBアプリを作ってみたいと思います。

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む