MySQLのデータをJupyter Notebook上でグラフにする

最近少しずつJupyter Notebookやpandas, matplotlibに慣れてきて、PythonによるData Scienceが面白くなってきたと感じてます。今日はMySQLのデータをSQLで持ってきて、Jupyter Notebook上でグラフにしてみる話。

セットアップ

Python3はインストールされていることを前提。jupyterなどの必要なソフトウェアを入れる環境を作って、pip でインストールする。

python3 -m venv jupyter
source jupyter/bin/activate
pip install jupyter pandas matplotlib pymysql

インストールが成功したら、こんな感じでJupyter Notebookを立ち上げる。

./jupyter/bin/jupyter notebook

立ち上がったらブラウザから適当にnotebookを作っておく。

MySQLに接続

今回はPure Pythonなpymysqlを使ってMySQLに接続する。接続先の情報は以下の例では環境変数から取っているけど、ここはpymysql.connectに直接渡すように変更してOK。connという変数に入っているのが、MySQLへのコネクションになる。

import os
import pymysql
import pandas as pd
import matplotlib.pyplot as plt

host = os.getenv('MYSQL_HOST')
port = os.getenv('MYSQL_PORT')
user = os.getenv('MYSQL_USER')
password = os.getenv('MYSQL_PASSWORD')
database = os.getenv('MYSQL_DATABASE')

conn = pymysql.connect(
    host=host,
    port=int(port),
    user=user,
    passwd=password,
    db=database,
    charset='utf8mb4')

pandas.read_sql_queryを使ってSQLを実行

df = pd.read_sql_query(
    "SELECT DATE(created_at) AS date, COUNT(*) AS count FROM user GROUP BY date HAVING date >= '2017-04-01' ",
    conn)

次にpandas.read_sql_queryを使って、MySQLからSQLで取得した結果をpandas.DataFrameにする。この関数には

• 第1引数にSQL
• 第2引数にMySQLのコネクション

を渡せばよい。これだけでMySQLから取得した結果がそのままDataFrameになるなんて簡単すぎる…

ちなみに今回は以下のuserテーブルから、日付ごとに登録したユーザー数を取得するSQLを発行している。

CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `email` varchar(255) CHARACTER SET utf8mb4 NOT NULL,
  `followed_teacher_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
);

read_sql_queryを実行した結果の df を表示してみるとこんな感じになっている。

DataFrameをグラフにする

最後に本題のグラフ化。DataFrameのindexに date カラムの値をセットして、DataFrameのplot.bar()を呼び出せば棒グラフが表示される。

%matplotlib inline はJupyter上にグラフを表示するためのおまじないっぽい。df.tail(10)しているのは直近の10件に絞って見やすくするため。

%matplotlib inline

df.index = df['date']
p = df.tail(10).plot.bar()

ちなみに plot.pie() とかやると円グラフが表示できる。描画できるグラフの種類についてはpandasのvisualizationというページに全部載っている。

まとめ

• pandas.read_sql_query を使うと、SQLでSELECTした結果をそのままDataFrameにできる
• DataFrameからグラフを表示するのは簡単
• いろんな種類のグラフが描画できるよ

おまけ

gistに今回のnotebookを上げておいたのでよかったらどうぞ。