Edited at
ViibarDay 14

Jupyter notebookでMySQLに接続する

More than 3 years have 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でやると幸せになれそうです。

以上です。