Jupyter Notebook（Anaconda／Windows）からMySQLに接続する（Python）

Anacondaを使って、Windows上にJupyter Notebook環境をインストールした（手順）続きとして説明します。

ファイルの量が多いのでインストールに時間はかかりますが、GUIベースで「プログラミングは不慣れだけれどやりたい分析があるんだ！」な人向けかと思います。

今回は、pymysqlというパッケージを使ってPythonから、MySQLに接続します。

Jupyter Notebookから「import pymysql」を実行してみると、「ModuleNotFoundError: No module named 'pymysql'」と表示されてしまい、モジュールが読み込まれていないことが分かります。

Anaconda Navigatorを起動して、「Environments」→「Not installed」→「pymysql」と選択/入力して表示される「pymysql」にチェックを入れて、「Apply」をクリックします（一般的には $pip install PyMySQL 的作業）。

Install Packages が表示されたら、「Apply」をクリックして、インストールを続行してください。読み込み表示がすべて完了して、「Not installed」の一覧からpymysqlが消えれば導入完了です。

PyMySQLでは、「connection」オブジェクトと「cursor」オブジェクトの２つを使って、MySQLに接続します。

MySQLサーバーへの接続設定などを使ってconnectionオブジェクトを作り、そこから取得したcursorオブジェクトにSQLを投げ込みます。

具体的には、以下のようにして実行できました。今回は、WordPressのMySQLへ接続しました。接続に必要な設定はwp-config.phpに記載されているので、その変数名（と、デフォルトポート番号）で書いてみます：

import pymysql.cursors

connection = pymysql.connect(
    host='(DB_HOST)',
    port=3306,
    user='(DB_USER)',
    password='(DB_PASSWORD)',
    db='(DB_NAME)',
    charset='(DB_CHARSET)',
    cursorclass=pymysql.cursors.DictCursor
)

try:
    with connection.cursor() as cursor:
        cursor.execute('show tables')
        result = cursor.fetchall()
        print(result)
        
    connection.commit()
finally:
    connection.close()

これで、「show tables」が実行され、DBにあるテーブル一覧が表示されます。

このとき、resultの中身は、各行に相当する「{カラム名: 値}」というdictionaryが並んだlistとして表現されています。

pandas（DataFrame）で扱う

実行結果は、少し書き加えて、次のようにしてDataFrame化できます。

import pymysql.cursors
import pandas as pd

connection = pymysql.connect(
    host='(DB_HOST)',
    port=3306,
    user='(DB_USER)',
    password='(DB_PASSWORD)',
    db='(DB_NAME)',
    charset='(DB_CHARSET)',
    cursorclass=pymysql.cursors.DictCursor
)

try:
    with connection.cursor() as cursor:
        cursor.execute('show tables')
        result = cursor.fetchall()
        print(result)
        
    connection.commit()
finally:
    connection.close()

df = pd.DataFrame(result)
df

この書き方だと、SQLをいろいろ実行するのには不便なので、どんなSQLを実行するかをSQLを実行しながら考えたり、軽くデータを見たい場合は、直接SQLをガンガン実行したいかもしれません。そんなときは、「%sql」というマジックコマンドを使うと便利です。

「%sql」を利用するには、「ipython-sql」をインストールする必要があります。インストールされていないと、「%sql」を実行して「ERROR:root:Line magic function `%sql` not found.」、「%%sql」を実行して「ERROR:root:Cell magic `%%sql` not found.」、「%load_ext sql」を実行して「ModuleNotFoundError: No module named 'sql'」が表示されます。

インストールするには、下図の再生ボタンを左クリックして「Open Terminal」をクリックして「pip install ipython-sql」を実行します。

こうした上で、「%load_ext sql」を実行してipython-sqlを読み込み、次の行で「%sql」で始まる接続用コマンドを書いて実行します。

%load_ext sql
%sql mysql+pymysql://DB_USER:DB_PASSWORD@DB_HOST:3306/DB_NAME?charset=DB_CHARSET

※大文字変数は、wp-config.phpの変数のことで、順にDBに接続するユーザー名、パスワード、ホスト名、DB名、そのDBの文字コード、です。

ここまでで接続できるので、以降、「%sql show tables」のように、SQLを実行できます。

「result = %sql show tables」のようにすれば、結果を変数に格納することもできます。

pandas（DataFrame）で扱う

このとき結果は、「sql.run.ResultSet」という形式になり、「result.DataFrame()」と呼び出すと、pandasのDataFrameオブジェクトに結果を変換できます。便利。

また、「%sql (SQL文)」ではなく、セルの先頭で「%%sql」とだけ書くとセル全体がSQLとして認識され、２行目以降にSQLを書くとすべて順に実行してくれます。

最後の結果はアンダーバー（アンダースコア）「_」変数に格納されるため、次のセルで「df = _.DataFrame()」などと呼び出すと、SQLで試行錯誤した後、途中からDataFrameで作業、というSQL→DataFrameの切り替えがスムーズにできます。

※「%%」とパーセント２つで始まるマジックコマンドはセル全体用で、１行目に書かないといけない点に注意してください。２行目以降で「%%sql」と実行すると、「SyntaxError: invalid syntax」という、具体的な解決策に気が付きにくいエラーになってしまいます。

マジックコマンド便利です。

• PyMySQL/PyMySQL: Pure Python MySQL Client
• Welcome to PyMySQL’s documentation! — PyMySQL 0.7.2 documentation
• catherinedevlin/ipython-sql: %%sql magic for IPython, hopefully evolving into full SQL client