[実装備忘録] Python3のpandasでMySQLにアクセスする方法

MySQLパッケージの選択

"pip search mysql" を実行し、どんなMySQL関連パッケージがあるのか調べたら、大量にヒットして収拾が付かない事態になった。
そこで、バージョン番号をチェックし、継続的に保守されているであろうパッケージを絞り込んだ。

$ pip search mysql|grep "(2."
getMysqlConnPara (2.0.0)                                        - Mysql connection parameters defined in a text file, reads the parameter file and return the connection parameter's dict
pg_chameleon (2.0.0rc1)                                         - MySQL to PostgreSQL replica and migration
mysql-latin1-codec (2.0)                                        - Python string codec for MySQL's latin1 encoding
WintxDriver-MySQL (2.0.0-1)                                     - MySQL support for Wintx
WintxDriver-MySQLFabric (2.0.0-1)                               - MySQL Fabric support for Wintx
scrapy-mysql-pipeline (2017.10.10)                              - Asynchronous mysql Scrapy item pipeline
umysql (2.61)                                                   - Ultra fast MySQL driver for Python
cns.recipe.zmysqlda (2.0.9)                                     - Recipe for installing ZMySQLDA
django-cymysql (2.0.0)                                          - Django database backend for cymysql
mysql-connector-python-rf (2.2.2)                               - MySQL driver written in Python
django-mysql (2.2.0)                                            - Extensions to Django for use with MySQL/MariaDB
mysql-connector (2.1.6)                                         - MySQL driver written in Python
HTSQL-MYSQL (2.3.3)                                             - A Database Query Language (MySQL backend)
collective.recipe.zmysqlda (2.0.9)                              - Recipe for installing ZMySQLDA
mysqlpy (2.1.7)                                                 - MySQL driver written in Python

バージョン番号が2.xの域に達しているパッケージのリストを検索で調べていったところ、MySQL公式パッケージ mysql-connector-python-rf がどうも良さそうである。(Python3対応やDBAPI2対応、継続的サポートの安心度などなど)
これをインストールすることにした。

mysql-connector-python-rf のインストール

AWSのEC2インスタンス(CentOS)とローカルクライアント(Windows7)の両方にインストールすることにした。
実際にMySQLサーバーが動いているのはEC2インスタンスであるが、クライアントのPyCharmにもパッケージを認識してもらいたいからである。

CentOSへのインストール

特に難しいことはない。pip でインストールする。

$ pip install mysql-connector-python-rf
    Collecting mysql-connector-python-rf
      Downloading mysql-connector-python-rf-2.2.2.tar.gz (11.9MB)
        100% |????????????????????????????????| 11.9MB 52kB/s
    Building wheels for collected packages: mysql-connector-python-rf
      Running setup.py bdist_wheel for mysql-connector-python-rf ... done
      Stored in directory: /home/*******/.cache/pip/wheels/bb/53/e4/dced82f8a15f96a8afbe626ebb2939d2901b29e610a97fc1ba
    Successfully built mysql-connector-python-rf
    Installing collected packages: mysql-connector-python-rf
    Successfully installed mysql-connector-python-rf-2.2.2

Windows7へのインストール

管理者権限でAnaconda Promptを起動する。（そうしないと権限エラーでインストール処理が途中で止まってしまった）
Python2とPython3が併存する開発環境なので、念のためちゃんとPython3を認識しているか確認。

(C:\ProgramData\Anaconda3) C:\windows\system32>pip -V
pip 9.0.1 from C:\ProgramData\Anaconda3\lib\site-packages (python 3.6)

問題なし。
ということで、pip でインストール。

(C:\ProgramData\Anaconda3) C:\windows\system32>pip install mysql-connector-python-rf
Collecting mysql-connector-python-rf
  Downloading mysql-connector-python-rf-2.2.2.tar.gz (11.9MB)
    100% |████████████████████████████████| 11.9MB 66kB/s
Building wheels for collected packages: mysql-connector-python-rf
  Running setup.py bdist_wheel for mysql-connector-python-rf ... done
  Stored in directory: C:\Users\********\AppData\Local\pip\Cache\wheels\bb\53\e4\dced82f8a15f96a8afbe626ebb2939d2901b29e610a97fc1ba
Successfully built mysql-connector-python-rf
Installing collected packages: mysql-connector-python-rf
Successfully installed mysql-connector-python-rf-2.2.2

無事インストール完了。PyCharmも認識してくれた。

公式ドキュメント

公式ドキュメントは以下から参照できる。
https://dev.mysql.com/doc/connector-python/en/

pandas からMySQLにアクセスする

pandasには、いつもお世話になっている。解析処理は、pandasのデータフレーム抜きには考えられない。ということで、pandasから直接MySQLにアクセスして、データフレームを取ってきてもらう。

pandasのread_sql は、引数con にDBAPI2に対応しているオブジェクトを指定することができる。
https://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_sql.html

先ほどインストールした mysql-connector-python-rf だが、公式サイトを調べると、ちゃんと**Python Database API Specification v2.0（PEP 249）**に対応しているとあった。つまり、DBAPI2 に対応しているわけである。
https://dev.mysql.com/doc/connector-python/en/connector-python-introduction.html

ということは、pandasのread_sqlで使うことができるだろう。
（余談だが、仕事では Hive の接続インスタンスを指定して、pandas で直接Hiveテーブルを読むことができていたので、当然MySQLでも同じことが出来るはずと考えていた）

MySQL に接続するコード

ささっと、以下のようなクラスを組んだ。

connector/mysql.py

import mysql.connector

class MySQL():
    def __init__(self, config):
        u"""
        :param config: 接続設定を格納した辞書
        """
        self.config = config
        self.conn = None
        if config is not None:
            self.connect()

    def connect(self, config=None):
        u"""
        MySQLに接続する。
        :return:
        """
        if config is None:
            config = self.config
        conn = mysql.connector.connect(**config)
        self.conn = conn
        return conn

connectの引数configは、こんな感じで値をセットする。

config = { 'host' : 'localhost',
           'user' : 'some_user',
           'password' : 'some_password',
           'database' : 'some_database',
           'charset' : 'utf-8' }

上記以外にも引数を指定できる。
詳細は、公式サイトの以下のページを参照。
https://dev.mysql.com/doc/connector-python/en/connector-python-connectargs.html

pandasでMySQLテーブルを読む

MySQLへの接続子インスタンスを生成したら、それを引数に指定して pandasのread_sqlを使う。
するとデータフレームを取得することができる。
サンプルコードはこんな感じ。(以前、自分が作った為替市場の値動きテーブルを読む想定で実装）

sample_mysql.py

import pandas as pd
from connector.mysql import MySQL
config = { 'host' : 'localhost',
           'user' : 'some_user',
           'password' : 'some_password',
           'database' : 'some_database',
           'charset' : 'utf-8' }
mysql = MySQL(config)

# 為替市場の値動きレコードを読むサンプル
sql = """
        SELECT
            *
        FROM
            {table_name}
        WHERE
            code='{code}'
            AND tick_date='{tick_date}'
        ORDER BY tick_datetime
""".format(table_name=table_name, code=market_code, tick_date=tick_date)

# pandas でMySQLテーブルを読む
df_read = pd.read_sql(sql, mysql.conn)
print(df_read.head())

無事に動くと、こんな結果が得られる。

     time_pos  code   tick_date       tick_datetime  tick_minute     open  \
0  1447925580     1  2015-11-19 2015-11-19 18:33:00        66780  123.156
1  1447925640     1  2015-11-19 2015-11-19 18:34:00        66840  123.158
2  1447925700     1  2015-11-19 2015-11-19 18:35:00        66900  123.160
3  1447925760     1  2015-11-19 2015-11-19 18:36:00        66960  123.137
4  1447925820     1  2015-11-19 2015-11-19 18:37:00        67020  123.135

      high      low    close
0  123.164  123.156  123.158
1  123.160  123.140  123.160
2  123.160  123.139  123.139
3  123.140  123.135  123.137
4  123.149  123.135  123.149