Jupyter notebookでMySQLに接続する

  • 16
    Like
  • 0
    Comment
More than 1 year has passed since last update.

ドーモ、引き続き、この記事もViibarアドベントカレンダーのとある1日の記事でございます。

ここ数日は少しだけデータ分析業をしているので、みんなだいすきJupyterからMySQLにつないでみます。

前提

  • Mysqlサーバーがローカルにインストールしていて起動済み
  • テストデータとしてdatacharmer/test_dbを使用

Jupyterインストール

miniconda3がよいとのことだったので使いたかったのですが、Pythonzはディストリビューション違いは含めたくないというポリシーらしく(ana|mini)condaがありません。なので、pyenvを使うことにしましたが、pyenvはよくわかりません。雑に使ってみます。

brew install pyenv
eval "$(pyenv init -)"
pyenv install miniconda3-3.18.3

.pyenv/versionsにインストールされます。

$ ls ~/.pyenv/versions
miniconda3-3.18.3

conda系はcondaコマンドでvirtualenv作成、依存性管理までするようです。なので、conda上でのjupyterを使いたい場合はpyenv-virtualenvはいらないようです。

pyenv global miniconda3-3.18.3
pyenv rehash
conda create -n test01 jupyter
$ ls ~/.pyenv/versions/miniconda3-3.18.3/envs/
test01

venvができました。

source ~/.pyenv/versions/miniconda3-3.18.3/envs/test01/bin/activate test01
jupyter notebook

notebookが起動すればOKです。source activate test01だとpyenvとぶつかるのでフルパスです。condaもpyenvも使わないで、普通に手動で依存関係をインストールしてもよかったな。

依存ライブラリインストール

MySQLドライバはpymysql、定番のpandas, matplotlibも入れます。

conda install pymysql
conda install pandas
conda install matplotlib
pip install ipython-sql

catherinedevlin/ipython-sqlだけはcondaに用意されてないのでpipからインストールです。

MySQLサーバーにJupyter notbookから接続

jupyter4.gif

以下、notebookから実行です。

import pymysql

connection = pymysql.connect(host='localhost',
                             user='root',
                             password='',
                             db='employees',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)
connection.commit()
cur = connection.cursor()
cur.execute('select count(*) from employees')
1
rows = cur.fetchall()
rows
[{'count(*)': 300024}]
cur.execute('select * from employees limit 100')
100
rows = cur.fetchall()
rows
[{'birth_date': datetime.date(1953, 9, 2),
  'emp_no': 10001,
  'first_name': 'Georgi',
  'gender': 'M',
  'hire_date': datetime.date(1986, 6, 26),
  'last_name': 'Facello'},
 {'birth_date': datetime.date(1964, 6, 2),
  'emp_no': 10002,
  'first_name': 'Bezalel',
  'gender': 'F',
  'hire_date': datetime.date(1985, 11, 21),
  'last_name': 'Simmel'},
...
 {'birth_date': datetime.date(1956, 5, 25),
  'emp_no': 10099,
  'first_name': 'Valter',
  'gender': 'F',
  'hire_date': datetime.date(1988, 10, 18),
  'last_name': 'Sullins'},
 {'birth_date': datetime.date(1953, 4, 21),
  'emp_no': 10100,
  'first_name': 'Hironobu',
  'gender': 'F',
  'hire_date': datetime.date(1987, 9, 21),
  'last_name': 'Haraldson'}]

%%sqlを利用してのsql実行

次は、%%sqlを利用してMySQLに接続してみます。

%config SqlMagic.autopandas = True
%config SqlMagic.feedback = False
%load_ext sql
/Users/shirakawahiroyuki/.pyenv/versions/miniconda3-3.18.3/envs/test01/lib/python3.5/site-packages/IPython/config.py:13: ShimWarning: The `IPython.config` package has been deprecated. You should import from traitlets.config instead.
  "You should import from traitlets.config instead.", ShimWarning)
/Users/shirakawahiroyuki/.pyenv/versions/miniconda3-3.18.3/envs/test01/lib/python3.5/site-packages/IPython/utils/traitlets.py:5: UserWarning: IPython.utils.traitlets has moved to a top-level traitlets package.
  warn("IPython.utils.traitlets has moved to a top-level traitlets package.")

ここで警告でるの、import from traitlets directly instead of IPYthon by aebrahim · Pull Request #46 · catherinedevlin/ipython-sqlで、PR作ってくれてますが、マージされてません。

%%sql mysql+pymysql://root@localhost/employees?charset=utf8mb4

select * from employees limit 100;
emp_no birth_date first_name last_name gender hire_date
0 10001 1953-09-02 Georgi Facello M 1986-06-26
1 10002 1964-06-02 Bezalel Simmel F 1985-11-21
2 10003 1959-12-03 Parto Bamford M 1986-08-28
3 10004 1954-05-01 Chirstian Koblick M 1986-12-01
97 10098 1961-09-23 Sreekrishna Servieres F 1985-05-13
98 10099 1956-05-25 Valter Sullins F 1988-10-18
99 10100 1953-04-21 Hironobu Haraldson F 1987-09-21

100 rows × 6 columns

df = _

describeの意味が無いデータですがとりあえず。

df.describe()
emp_no
count 100.000000
mean 10050.500000
std 29.011492
min 10001.000000
25% 10025.750000
50% 10050.500000
75% 10075.250000
max 10100.000000

2回めからは接続の情報は不要です。

%%sql

select * from employees limit 1;
emp_no birth_date first_name last_name gender hire_date
0 10001 1953-09-02 Georgi Facello M 1986-06-26

ついでによくあるpandasとmatplotlibのサンプルも出してみました。

%matplotlib inline
import os
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt

ts = pd.Series(np.random.randn(1000), index=pd.date_range('1/1/2000', periods=1000))

ts = ts.cumsum()
ts.plot()
<matplotlib.axes._subplots.AxesSubplot at 0x10a6fac18>

まとめ

SQLの実行クライアントは、昔はCSEとかNavicat for MySQL | MySQLに対応したDB管理・開発ツールとか使ってたんですが、Macにしてからあまりいい感じのクライアントに巡りあってませんでした。
自分の用途では、管理機能とかはそんな必要としてなくて、

  • SQL文とその実行結果の保存
  • SQL文のフォーマット

くらいができればよさげだったので、Jupyter notebookがまあまあちょうどいいのかもしれません。
SQL文のフォーマットについては、andialbrecht/sqlparse使えば、SQL文をきれいにフォーマットしてくれるので、それで。
いろいろPythonでやると幸せになれそうです。

以上です。

This post is the No.14 article of Viibar Advent Calendar 2015