PythonでMySQLのデータを取得する（mysql-connector-python）

# 概要
- `mysql-connector-python`を使った、PythonからMySQLの情報を取得する方法を紹介します
    - 前半: MySQLの基本的な操作
    - 後半: `mysql-connector-python`を使ったPythonからMySQLのデータを取得

# MySQLのデータベース作成とクエリによる読み込み
## 参考
- MySQLの基本操作を参考にしました
    - [MySQLをMacのターミナルで操作するときのメモ \- Qiita](https://qiita.com/nanatsu/items/33b57238b0e9ae0a1c78)
- その他下記の書籍をときどき参照しました
    - [SQL 第2版 ゼロからはじめるデータベース操作](https://www.amazon.co.jp/dp/B01HD5VWWO)
    - [達人に学ぶDB設計 徹底指南書](https://www.amazon.co.jp/dp/B00EE1XPAI)

## 環境構築
- [MySQL環境構築の忘備録 \- ikeh1024のブログ](https://ikeh1024.hatenablog.com/entry/2021/02/17/210308)
    - （試行錯誤して苦しんだのでまとまってないのは悪しからず…）

## MySQLへ接続

- ターミナルからMySQLへ接続します

```sh
mysql -u root -p
Enter password: 
```

- データベースを確認します

```sql
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| xxx                |
| xxxx               |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.01 sec)
```

## データベースの作成（クエリの実行）

- `demo_database`という名前でデータベースを作成します

```sql
mysql> CREATE database demo_database;
Query OK, 1 row affected (0.04 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| demo_database      |
| xxx                |
| xxxx               |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
7 rows in set (0.00 sec)
```

- 使用するデータベースを選択します
- 作成したばかりなので勿論テーブルは空ですね

```sql
mysql> USE demo_database;
Database changed
mysql> SHOW tables;
Empty set (0.00 sec)
```

## データベースの作成（MySQLダンプファイルの読み込み）
- 参考までにMySQLダンプファイルを読み込んでデータベースを作成する方法を紹介します
    - 今回は使用しません
- 適当な名称で`CREATE database "test_db";`として空のデータベースを作り、下記のように読み込めばOKです

> [パスやファイル名を指定して、コマンドライン上からデータベースにdumpファイルをインポートする](https://qiita.com/chimayu/items/90c9421067d76696a050)
> 
> ```sh
> -- 取り込みたいデータファイルと同じ場所まで移動して実行
> $ mysql -u [MySQLのユーザ名] -p test_db < example.sql
> ```

- `.sql`のスクリプトを適用したい場合は下記を参照
    - [MySQLでファイルからSQLを実行する \- Qiita](https://qiita.com/IysKG213/items/994e9f4ad12ff8aee322)

```sh
mysql -u root -p 「DB名」 < 「対象ファイル」
```

## テーブルの作成

- テーブルの作成と確認

```sql
/* CREATE TABLE */
CREATE TABLE sample_table
(
    sample_id   int(11)     NOT NULL COMMENT 'サンプルID',
    sample_name varchar(50) NOT NULL COMMENT 'サンプル名',
    UNIQUE(sample_id, sample_name)
)
COMMENT='テーブル用のコメント'
;
```

```sql
mysql> SHOW tables;
+-------------------------+
| Tables_in_demo_database |
+-------------------------+
| sample_table            |
+-------------------------+
1 row in set (0.02 sec)
```

## テーブルへデータを登録

- 適当にデータを追加します

```sql
/* Adding Data */
SET @sample_id_first  = 1;
SET @sample_id_second = 2;
SET @sample_id_third  = 3;

-- Comment
INSERT INTO sample_table VALUES (@sample_id_first,  "1番目");
INSERT INTO sample_table VALUES (@sample_id_second, "2番目");
INSERT INTO sample_table VALUES (@sample_id_third,  "3番目");
```

## データの確認: クエリ

- 先程のデータが登録されているか確認します
- また下記のクエリを、後述の`mysql-connector-python`を使ってPythonから呼び出します

```sql
SELECT
    sample_table.sample_id   AS id,
    sample_table.sample_name AS name

FROM sample_table

/* ソート設定 */
ORDER BY sample_table.sample_id
;
```

```shell
+----+---------+
| id | name    |
+----+---------+
|  1 | 1番目   |
|  2 | 2番目   |
|  3 | 3番目   |
+----+---------+
3 rows in set (0.00 sec)
```

## データの確認: SQLクライアントツール
- [DBeaver Community](https://dbeaver.io/)というSQLクライアントツールを使って、GUI的にデータベースを閲覧してみます
    - [SQL クライアントツール「DBeaver」が「OSqlEdit」レベルの使いやすさで感動した！！ \- Beeeat’s log](https://bake0937.hatenablog.com/entry/2020/04/27/220430)
- GUIで楽に確認できるのが良いですね

<img width="512" alt="image" src="https://i.imgur.com/erSQ02O.jpg">

- 接続設定は以下の通りです

<img width="512" alt="image" src="https://i.imgur.com/sXK9zYx.jpg">

- またPCを再起動してアプリを起動した場合、DBの接続がエラーになるので、一旦ターミナル経由でログインが必要でした

```shell
mysql -u root -p
```

# mysql-connector-pythonを使用してMySQLの情報を取得
## 参考
- [MySQL Connector/Python Developer Guide](https://dev.mysql.com/doc/connector-python/en/)
    - 公式サイト
- [10\.6\.4 cursor\.MySQLCursorDict Class](https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursordict.html)
    - データベースから取得した結果を辞書型で取得する   

## mysql-connector-pythonのインストール

- 下記の通りインストールします

```sh
pip install mysql-connector-python
```

## mysql-connector-pythonの使用例
- 先述の同じクエリを、Pythonから実行してMySQLから情報を取得してみます

``` Python
import mysql.connector


# DBへ接続
conn = mysql.connector.connect(
    user='root',
    password='password',
    host='localhost',
    database='demo_database'
)

# DBの接続確認
if not conn.is_connected():
    raise Exception("MySQLサーバへの接続に失敗しました")
    
cur = conn.cursor(dictionary=True)  # 取得結果を辞書型で扱う設定

query__for_fetching = """
SELECT
    sample_table.sample_id   AS id,
    sample_table.sample_name AS name
FROM sample_table
ORDER BY sample_table.sample_id
;
"""

cur.execute(query__for_fetching)

for fetched_line in cur.fetchall():
    id = fetched_line['id']
    name = fetched_line['name']
    print(f'{id}: {name}')
```

- 実行結果は以下の通りです
- 無事SQLからデータを読み込むことができてますね。

```sh
1: 1番目
2: 2番目
3: 3番目
```

- また私は扱いやすくするため、下記の`MysqlConnectorManager`のようなクラスを作成して使用しています。

```python
from dataclasses import dataclass
from typing import Any, Dict, List, Optional
import mysql.connector


@dataclass
class MysqlConnectorManager():
    user: str = ""
    password: str = ""
    host: str = ""
    database_name: str = ""
    cur: Optional[Any] = None
    conn: Optional[Any] = None

    @classmethod
    def create(cls,
               user: str,
               password: str,
               host: str,
               database_name: str,
               ) -> 'MysqlConnectorManager':
        return MysqlConnectorManager(user=user, password=password, host=host, database_name=database_name)

    def __del__(self):
        self._close_connection()

    def start_connection(self) -> None:
        """DBに接続する
        """
        self.conn = mysql.connector.connect(
            user=self.user,
            password=self.password,
            host=self.host,
            database=self.database_name
        )

        # DB接続の確認
        if not self.conn.is_connected():
            raise Exception("MySQLサーバへの接続に失敗しました")
            
        self.cur = self.conn.cursor(dictionary=True)

    def _close_connection(self) -> None:
        """DBの接続を切る
        """
        self.cur.close
        self.conn.close
        self.cur = None
        self.conn = None

    def fetch_contents(self, query: str) -> List[Dict[str, Any]]:
        """DBから情報を取得する
        """
        self.cur.execute(query)
        fetched_contents: List[Dict[str, Any]] = self.cur.fetchall()
        
        return fetched_contents
```