Jupyter notebookでMySQLに接続する #Python3

ドーモ、引き続き、この記事も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から接続

以下、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')

rows = cur.fetchall()

rows

[{'count(*)': 300024}]

cur.execute('select * from employees limit 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でやると幸せになれそうです。

以上です。

Jupyter notebookでMySQLに接続する

前提

Jupyterインストール

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

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

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

まとめ

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