ドキュメント読んでる時とか、 ネットの記事を読んでいるとき、 「ちょっとこのコード試してみたいから使い捨てのデータベースサーバたてたいな..」となる時があると思います。
もちろんローカルでmysqldとか動かしていても良いと思いますが、ローカルのPCで要らないデーモンが動いているの嫌じゃないですか..？私は嫌です..。

そんな時にDockerで、docker-compose です。 Docker/docker-compose はインストール済とします。
私はWindowsで実施していますが、 wslでもネイティブなWindowsでも動くので、どこでも動くと思います。

docker-compose.yml

version: "3"

services:
  db:
    image: mysql:5.7
    ports:
      - "3306:3306"
    environment:
      MYSQL_DATABASE: playground
      MYSQL_ROOT_PASSWORD: password
    command: --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci

これ↑をちらっと書いてカレントディレクトリに置き、 docker-compose up -d でMySQLサーバが立ち上がります。

設定は見ての通りですが

• (ローカルから見える)ポートは 3306
• rootのパスワードは password
• デフォルトで作られるDB名は playground
• デフォルトで作られたDBのcharsetは utf8mb4 <= ここが書きたくてこの記事を書いた

で動いています。

勉強が済んで要らなくなったら docker-compose down で廃棄です。

PythonでMySQLを介してDBを操作してみました。
その際に引っかかった箇所や初歩的な箇所を簡単にですがまとめてみました。

対象者

PythonでMySQLを操作してみたい方
大量データを素早くDBに書き込みたい方

MySQLとは

オープンソースで公開されている関係データベース管理システム (RDBMS) の一つである。
GNU GPL(無料で使えるライセンス)と商用ライセンスのデュアルライセンスとなっている。
(ウィキペディアから一部抜粋)
ウィキペディア/MySQL

メリット

• 運用経験者が多い
• 高いパフォーマンス

デメリット

• 脆弱性対応が遅い

↓その他のDBMSとの比較するときのご参考に
SQLの観点から「Oracle Database」「PostgreSQL」「MySQL」の特徴を整理しよう！

MySQLを導入する際は下記を参考にすると良いと思います。
MySQLのダウンロード＆インストールと初期設定方法

Pythonの必要なパッケージ

pip

pip install MySQL-Python

MySQLと環境設定の説明はこの辺にして実際に書いたコードの内容に移ります。

実装概要

• MySQLに接続&文字のエンコード(utf-8)
• 指定DBのアクティブ化
• データテーブルの作成
• データの挿入(大量データに対応)
• データテーブルから指定データの取り出し

実装内容

今回はクラスが実装時に都合が良かったので下記で説明していく機能は全て１つのクラスのメソッドになります。

• MySQLに接続&文字のエンコード(utf-8)

DB_Manipulate/__init__

def __init__(self):
    # Connect
    self.conn = MySQLdb.connect(
        user='root',
        passwd='password',
        host='localhost',
        db='mysql'
    )

    # Encoding
    self.conn.set_character_set('utf8')
    self.cur = self.conn.cursor()
    self.cur.execute('SET NAMES utf8;')
    self.cur.execute('SET CHARACTER SET utf8;')
    self.cur.execute('SET character_set_connection=utf8;')

自身のMySQL上で登録したユーザー名とパスワードをいれてください。
接続後に下の行でデータを入れる際に文字コードをエンコードするようにMySQLにSQL文で指示を送っています。
MySQLに何か操作をする際には基本的にconn.cur.execute('SQL文')で送ります。

• 今回使用するSQL文

SQL

sql_DBactive = "use "
sql_cretable = "CREATE TABLE IF NOT EXISTS "
sql_insert = "INSERT INTO "

"use"は指定したDBをアクティブにする
"CREATE TABLE IF NOT EXISTS "は既に指定したテーブル名が
存在するか確認して無い場合はテーブルを作成する
"INSERT INTO "データの挿入を行います

• 指定DBのアクティブ化

DB_Manipulate/DB_activate

def DB_activate(self, DBname):
    sql = self.sql_DBactive + DBname
    self.cur.execute(sql)

実際にexcuteに入っているSQL文は'use DataBaseName'
これでどのデータベースを使用するか指定できます。

• データテーブルの作成

DB_Manipulate/CREATETABLE_City_ID_Name

def CREATETABLE_City_ID_Name(self, table_name):
    self.table_name = table_name
    sql = self.sql_cretable +\
            self.table_name +\
            " (ID INT," +\
            "City CHAR(100)," +\
            "Country CHAR(100))"
    self.cur.execute(sql)

CREATE TABLEとそのテーブルの中に入れる列名とデータ型の指定をしています。
MySQLのデータ型の詳細は【MySQL】データ型一覧
扱えるデータ型がまとまっておりわかりやすいです。
また、一度テーブル作成後のカラムの変更は可能です詳細はカラムの名前と定義の変更

- データの挿入(大量データに対応)

DB_Manipulate/INSERT_City_ID_Name

def INSERT_City_ID_Name(self, city_data):
    sql = self.sql_insert +\
            self.table_name+\
            " (ID, City, Country) VALUES (%s, %s, %s)"
    self.cur.executemany(sql, city_data)
    self.conn.commit()

データを挿入する時はINSERT文で1つずつデータ挿入しますが大量データを挿入する際は一括で挿入する必要があります。
この目的を達成するにはSQL文とexcuteに工夫が必要で
"INSART INTO TABLENAME (カラム名＊カラムの数だけ記入する) VALUES (%s＊カラムの数だけ記入する)"
することで大量データを入力するSQL文にします
次にexecuteでSQL文の投げていたところをexecutemanyに変更することで処理時間を短く簡単なコードでその目的を達成することが出来ます。

• データテーブルから指定データの取り出し

DB_Manipulate/SELECT_Column

def SELECT_Column(self, table_name, *input_column_name):
    for i in range(len(input_column_name)):
        if i == 0:
            column_name = input_column_name[0]
        else:
            column_name = column_name + ',' + input_column_name[i]

    sql = "SELECT " + column_name + " from " + table_name
    self.cur.execute(sql)

    return self.cur.fetchall()

データの取り出しについてですが任意のカラムを引っ張れるようなコードにしてみました。
input_column_nameで複数の引数を入力できるようにしてfor文で適当なSQL文の形に修正しています。
ちなみにinput_column_nameに"*"を入力すると通常のSQL文同様全カラムのデータを取得するようになっています。
executeでSQL文を投げた後return のfetchallで取得したデータを返しています。

コード全文

DB_Manipulate

import MySQLdb

class cl_DB_Manipulate:
    #Properties
    sql_DBactive = "use "
    sql_cretable = "CREATE TABLE IF NOT EXISTS "
    sql_insert = "INSERT INTO "

    #constructor
    def __init__(self):
        # Connect
        self.conn = MySQLdb.connect(
            user='root',
            passwd='password',
            host='localhost',
            db='mysql'
        )

        # Encoding
        self.conn.set_character_set('utf8')
        self.cur = self.conn.cursor()
        self.cur.execute('SET NAMES utf8;')
        self.cur.execute('SET CHARACTER SET utf8;')
        self.cur.execute('SET character_set_connection=utf8;')

    #method
    def DB_activate(self, DBname):
        sql = self.sql_DBactive + DBname
        self.cur.execute(sql)

    def CREATETABLE_City_ID_Name(self, table_name):
        self.table_name = table_name
        sql = self.sql_cretable +\
                self.table_name +\
                " (ID INT," +\
                "City CHAR(100)," +\
                "Country CHAR(100))"
        self.cur.execute(sql)

    def INSERT_City_ID_Name(self, city_data):
        sql = self.sql_insert +\
                self.table_name+\
                " (ID, City, Country) VALUES (%s, %s, %s)"
        self.cur.executemany(sql, city_data)
        self.conn.commit()

    def SELECT_Column(self, table_name, *input_column_name):
        for i in range(len(input_column_name)):
            if i == 0:
                column_name = input_column_name[0]
            else:
                column_name = column_name + ',' + input_column_name[i]
        sql = "SELECT " + column_name + " from " + table_name
        self.cur.execute(sql)

        return self.cur.fetchall()

    def __del__(self):
        self.conn.close()

このコード私個人が使いやすい形にしているだけなので使用する場合は各々使いやすい形にしてもらえればと思います。

また、クラスを使った理由はコンストラクタとデストラクタでクラスを定義した時にMySQLと接続、開放した時に接続を切る処理を行ってくれるためです。
Pythonの詳しいクラスの使い方はPython基礎講座(13 クラス)

まとめ

データを効率的に扱いたいと言うのが独学でDBを扱うモチベーションかなと思いまずは大量データ書き込みをメインに話を進めていきました。
PythonでDBを扱ってみたい方の助けになればと思います。

現在はwebアプリにMySQLを絡めた簡単ものを作成中です。
完成したらそちらも投稿しようと思います。

要約

アプリケーションサーバが重くなって、DBにコネクションが溜まって、サービス全体がダウン、なんてことにならないように、DBコネクションにwait_timeoutを設定するのは良いことです。
しかし、PDOではコネクションが切れてしまったときに自動で再接続する手段が提供されていません。
そこで、PDOのラッパークラスを作って、再接続できるようにしました。

環境

• PHP7.0以上
• MySQL(MariaDB10.1)

ハマりどころ

PDOをラップして、PDO::exec()の時に例外が発生したらcachして、ロストコネクションだったら再接続する、っていうだけなら簡単なんですが、このやりかただとPDO::prepare()->execute()されたときにcatchできなくなります。
execute()はPDOStatementのメソッドだからです。

解決策

PDOのラッパークラス自身にタイマーを持たせて、wait_timeoutが経過したら無駄にクエリを1回発行して例外を発生させることで、かならずラッパークラス自身で再接続するようにしました。
ダサいとか言わないで。

サンプルコード

<?php

class ReConnectablePdo
{
    /** @var \PDO $pdo */
    private $pdo;

    private $dsn;
    private $username;
    private $password;
    private $options;
    private $timeout;
    private $lastExecMicroTime;

    /**
     * ReConnectablePdo constructor.
     * @param string $dsn
     * @param string $username
     * @param string $password
     * @param array $options
     * @param int|null $timeout
     */
    public function __construct($dsn, $username, $password, $options, $timeout)
    {
        $this->dsn = $dsn;
        $this->username = $username;
        $this->password = $password;
        $this->options = $options;
        $this->timeout = $timeout;

        $this->connect();
    }

    /**
     * @param $statement
     * @return int
     * @throws RuntimeException
     */
    public function exec($statement)
    {
        return $this->callPDOMethod('exec', [$statement]);
    }

    // こんな感じで各メソッドをラップする

    /**
     * @param string $methodName
     * @param array $params
     * @return int|mixed
     * @throws RuntimeException|\Exception
     */
    private function callPDOMethod($methodName, $params = [])
    {
        $retried = false;
        while (true) {
            try {
                // PDOから直接DBにアクセスせずPDOStatement経由でアクセスする場合があるので
                // クラス内でもTimeoutを監視しておく
                // 前回実行時からTimeout以上経過していた場合は
                // PDO経由でダミーのクエリを発行し再接続を促す
                if ($this->timeout && $this->lastExecMicroTime + $this->timeout - 0.1 <= microtime(true)) {
                    $this->pdo->exec('SELECT 1');
                }
                $ret = call_user_func_array([$this->pdo, $methodName], $params);
                $this->lastExecMicroTime = microtime(true);
                return $ret;
            } catch (\Exception $exception) {
                // ２連続でロストコネクション、またはロストコネクション以外の場合は例外を投げる
                if ($retried || !self::causedByLostConnection($exception)) {
                    throw $exception;
                }
                $this->connect();
                $retried = true;
            }
        }
        // ここには来ない
        return 0;
    }

    /**
     * @return void
     */
    private function connect()
    {
        if (isset($this->pdo)) {
            unset($this->pdo);
        }

        $this->pdo = new \PDO($this->dsn, $this->username, $this->password, $this->options);

        if ($this->timeout) {
            $this->pdo->exec("SET wait_timeout = {$this->timeout}");
        }

        $this->lastExecMicroTime = microtime(true);
    }

    /**
     * @param \Exception $exception
     * @return bool
     */
    private static function causedByLostConnection(\Exception $exception)
    {
        $errorMessagePatterns = [
            'server has gone away',
            'no connection to the server',
            'Lost connection',
            'is dead or not enabled',
            'Error while sending',
            'decryption failed or bad record mac',
            'server closed the connection unexpectedly',
            'SSL connection has been closed unexpectedly',
            'Error writing data to the connection',
            'Resource deadlock avoided',
        ];

        $message = $exception->getMessage();

        foreach ($errorMessagePatterns as $pattern) {
            if (stripos($message, $pattern) !== false) {
                return true;
            }
        }
        return false;
    }
}