pandas DataFrameのデータをMySQLへ保存したり、逆にMySQLのテーブルをDataFrameとして読み込む方法についてメモ。
magicコマンドを使うので、Jupyter上での操作を想定です。
1.使うライブラリ
私は下記のライブラリを使っています。ライブラリの役割は下記のとおり。
| ライブラリ | 役割 |
|---|---|
| PyMySQL | MySQLと接続するコネクタ |
| sqlalchemcy | python製のORM |
| ipython-sql | jupyter上でsqlを実行できるようになる |
インストールしてなければ、インストール。
$ pip install PyMySQL
$ pip install sqlalchemy
$ pip install ipython-sql
2.接続するデータベースの設定
接続するためのエンジンを作成。
import pandas as pd
import sqlalchemy as sa
url = 'mysql+pymysql://username:password@hostname:port/dbname?charset=utf8'
engine = sa.create_engine(url, echo=False)
3.データの受け渡し
pandasのDataFrameをMySQLに保存する。DataFrameはpandasの関数が使えるのでデータベースとの連携が簡単！
ただ、if_exsits = 'replace'にしておくと、テーブルが問答無用で上書きされるので、本番環境では非推奨。
また、通常の設定のままだと1行ずつインサートするため、非常に時間がかかる。
引数method = "multi"を設定すると複数行をまとめてインサートでき、引数chunksizeを設定すると、インサート時の行数の上限が設定できる。
df.to_sql('df', engine, index=False,
method = "multi",chunksize = 10000 ,if_exists='replace')
4.データの読み込み
データベースにあるテーブルをpandas.DataFrame形式で読み込む。pandasのread_sql関数が簡単。
query = "select * from dbname.df"
df = pd.read_sql(query,con = engine)
5.Jupyter上でのSQL操作
magicコマンドを読み込んでやると、%%sql $urlでsqlに接続できるようになり、セルからqueryをなげられる。
%load_ext sql
%%sql $url
SHOW TABLES;
6.JupyterLab extension
セルからちょろっと操作するのでなく、もっとがっつりqueryを書いたり、データをしっかり見にいく必要がある場合には、JupyterLab extensionのjupyterlab-sqlがフィットするかも。
https://github.com/pbugnion/jupyterlab-sql