Jupyter notebookでMySQLに接続する

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

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

## 前提

- Mysqlサーバーがローカルにインストールしていて起動済み
- テストデータとして[datacharmer/test_db](https://github.com/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](https://github.com/catherinedevlin/ipython-sql)だけはcondaに用意されてないのでpipからインストールです。

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

![jupyter4.gif](https://qiita-image-store.s3.amazonaws.com/0/20698/350a99a1-dab0-a896-85df-0b5ae5e8b712.gif)

以下、notebookから実行です。


```python
import pymysql

connection = pymysql.connect(host='localhost',
                             user='root',
                             password='',
                             db='employees',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)
```


```python
connection.commit()
cur = connection.cursor()
cur.execute('select count(*) from employees')
```




    1




```python
rows = cur.fetchall()
```


```python
rows
```




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




```python
cur.execute('select * from employees limit 100')
```




    100




```python
rows = cur.fetchall()
```


```python
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に接続してみます。

```python
%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](https://github.com/catherinedevlin/ipython-sql/pull/46)で、PR作ってくれてますが、マージされてません。


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

select * from employees limit 100;
```


<div>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>emp_no</th>
      <th>birth_date</th>
      <th>first_name</th>
      <th>last_name</th>
      <th>gender</th>
      <th>hire_date</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>10001</td>
      <td>1953-09-02</td>
      <td>Georgi</td>
      <td>Facello</td>
      <td>M</td>
      <td>1986-06-26</td>
    </tr>
    <tr>
      <th>1</th>
      <td>10002</td>
      <td>1964-06-02</td>
      <td>Bezalel</td>
      <td>Simmel</td>
      <td>F</td>
      <td>1985-11-21</td>
    </tr>
    <tr>
      <th>2</th>
      <td>10003</td>
      <td>1959-12-03</td>
      <td>Parto</td>
      <td>Bamford</td>
      <td>M</td>
      <td>1986-08-28</td>
    </tr>
    <tr>
      <th>3</th>
      <td>10004</td>
      <td>1954-05-01</td>
      <td>Chirstian</td>
      <td>Koblick</td>
      <td>M</td>
      <td>1986-12-01</td>
    </tr>

    <tr>
      <th>97</th>
      <td>10098</td>
      <td>1961-09-23</td>
      <td>Sreekrishna</td>
      <td>Servieres</td>
      <td>F</td>
      <td>1985-05-13</td>
    </tr>
    <tr>
      <th>98</th>
      <td>10099</td>
      <td>1956-05-25</td>
      <td>Valter</td>
      <td>Sullins</td>
      <td>F</td>
      <td>1988-10-18</td>
    </tr>
    <tr>
      <th>99</th>
      <td>10100</td>
      <td>1953-04-21</td>
      <td>Hironobu</td>
      <td>Haraldson</td>
      <td>F</td>
      <td>1987-09-21</td>
    </tr>
  </tbody>
</table>
<p>100 rows × 6 columns</p>
</div>




```python
df = _
```

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

```python
df.describe()
```




<div>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>emp_no</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>count</th>
      <td>100.000000</td>
    </tr>
    <tr>
      <th>mean</th>
      <td>10050.500000</td>
    </tr>
    <tr>
      <th>std</th>
      <td>29.011492</td>
    </tr>
    <tr>
      <th>min</th>
      <td>10001.000000</td>
    </tr>
    <tr>
      <th>25%</th>
      <td>10025.750000</td>
    </tr>
    <tr>
      <th>50%</th>
      <td>10050.500000</td>
    </tr>
    <tr>
      <th>75%</th>
      <td>10075.250000</td>
    </tr>
    <tr>
      <th>max</th>
      <td>10100.000000</td>
    </tr>
  </tbody>
</table>
</div>

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

```python
%%sql

select * from employees limit 1;
```




<div>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>emp_no</th>
      <th>birth_date</th>
      <th>first_name</th>
      <th>last_name</th>
      <th>gender</th>
      <th>hire_date</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th>0</th>
      <td>10001</td>
      <td>1953-09-02</td>
      <td>Georgi</td>
      <td>Facello</td>
      <td>M</td>
      <td>1986-06-26</td>
    </tr>
  </tbody>
</table>
</div>


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

```python
%matplotlib inline
```


```python
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()
```


```python
ts.plot()
```




    <matplotlib.axes._subplots.AxesSubplot at 0x10a6fac18>

## まとめ

SQLの実行クライアントは、昔は[CSE](http://www.hi-ho.ne.jp/tsumiki/)とか[Navicat for MySQL | MySQLに対応したDB管理・開発ツール](http://jp.navicat.com/products/navicat-for-mysql)とか使ってたんですが、Macにしてからあまりいい感じのクライアントに巡りあってませんでした。
自分の用途では、管理機能とかはそんな必要としてなくて、

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

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

以上です。