情報科学屋さんを目指す人のメモ

方法・手順・解説を書き残すブログ。私と同じことを繰り返さずに済むように。

Jupyter Notebook(Anaconda/Windows)からMySQLに接続する(Python)

Anaconda (2) Jupyter Notebook (3) MySQL (3) Python (5) Windows (304)

Anacondaを利用して手軽に構築したJupyter Notebook環境から、MySQLに接続する流れを説明します。

pymysqlをそのまま使うプログラミングチックな方法と、マジックコマンド「%sql/%%sql」を使うライトな方法を順に紹介します。マジックコマンドを使う方法も、マジックコマンドを使うためのipython-sqlだけでなく、地味にpymysqlに依存しているので、気を付けてください。

Jupyter Notebook環境

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

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

PyMySQLをインストールする

今回は、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」オブジェクトの2つを使って、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を実行しながら考えたり、軽くデータを見たい場合は、直接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として認識され、2行目以降にSQLを書くとすべて順に実行してくれます。

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

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

ひとこと

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

参考

コメント(0)

新しいコメントを投稿