【python】pandasのto_sqlを試してみる
はじめに
気軽にDataFrameをデータベーステーブルに変換できそうなto_sqlなるものがあるので、試してみます。
sqliteを使いたかった
ドキュメントではSQLAlchemyを使ってSQLiteを叩いているようですが、SQLAlchemy*1は素人なので、ここでのファイルの指定方法がいまいち謎です。
とりあえず、
データベースエンジン — SQLAlchemy 0.6.5 ドキュメント (和訳)
をざっと眺めて適当にやったら、エラーが出ました。
sqlalchemy.exc.ArgumentError: Invalid SQLite URL: sqlite://test.db Valid SQLite URL forms are: sqlite:///:memory: (or, sqlite://) sqlite:///relative/path/to/file.db sqlite:////absolute/path/to/file.db
あ、なるほど、相対パスならスラッシュ3つ、絶対パスならスラッシュ4つにするんですね。すごくどうでも良いところでハマりました。
実行して確認してみる
何はともあれ、こんなコードを実行します。
import pandas as pd from sqlalchemy import create_engine df = pd.DataFrame({"A":["hoge", "fuga"], "B":[1, 2], "C":[1.0, 2.0]}) engine = create_engine('sqlite:///test.db', echo=False) df.to_sql("df1", engine)
lsしてtest.dbが生成されたことを確認したら、コンソールからsqliteでつなぎます。
$ sqlite3 test.db
sqlite> .tables
df1
sqlite> .schema df1
CREATE TABLE df1 (
"index" BIGINT,
"A" TEXT,
"B" BIGINT,
"C" FLOAT
);
CREATE INDEX ix_df1_index ON df1 ("index");
sqlite> select A, B, C from df1;
hoge|1|1.0
fuga|2|2.0まずはできてるっぽくて安心。型はよくわからないけど、こんなものだと思います。
複数回実行
問題になりそうなのは複数回呼んだときにどうなるかです。DataFrameを更新してテーブルにも反映する・・・といった処理を想定しています。
デフォルトの挙動は「エラーになる」です。
ValueError: Table 'df1' already exists.
ただしまったく使えないという訳ではなく、ドキュメントに書いてあることですが、
if_exists : {‘fail’, ‘replace’, ‘append’}, default ‘fail’
というパラメータがあって、これで調整できます。
dfの定義を書き換えて、if_exists="replace"にして実行してみます。
import pandas as pd from sqlalchemy import create_engine df = pd.DataFrame({"A":["aa", "hoge", "fuga"], "B":[-100, 1, 2], "C":[-100.0, 1.0, 2.0]}) engine = create_engine('sqlite:///test.db', echo=False) df.to_sql("df1", engine, if_exists="replace")
実行してから中身を見ます。
$ sqlite3 test.db sqlite> select A, B, C from df1; aa|-100|-100.0 hoge|1|1.0 fuga|2|2.0
テーブルの形が変わっても同様のことができます。
import pandas as pd from sqlalchemy import create_engine df = pd.DataFrame({"A":["aa", "hoge", "fuga"], "B":[-100, 1, 2], "C":[-100.0, 1.0, 2.0], "D":[1, 2, 3]}) engine = create_engine('sqlite:///test.db', echo=False) df.to_sql("df1", engine, if_exists="replace")
sqlite> select A, B, C, D from df1; aa|-100|-100.0|1 hoge|1|1.0|2 fuga|2|2.0|3
一回消して作り直しているのと同じようなものと考えるべき・・・でしょうか。
続けてappendを試します。
append: Insert new values to the existing table.
いまいちよくわからない説明なので、念の為に新しいテーブルを作って試します。
import pandas as pd from sqlalchemy import create_engine df = pd.DataFrame({"A":["aa", "hoge", "fuga"], "B":[-100, 1, 2], "C":[-100.0, 1.0, 2.0], "D":[1, 2, 3]}) engine = create_engine('sqlite:///test.db', echo=False) df.to_sql("df1", engine, if_exists="append")
sqlite> select * from df2; 0|aa|-100 1|hoge|1
一回目は普通。
もう一回実行すると、下みたいになります。
sqlite> select * from df2; 0|aa|-100 1|hoge|1 0|aa|-100 1|hoge|1
なんとなく納得しました。
列を追加すると、どうなるんでしょうね。
import pandas as pd from sqlalchemy import create_engine df = pd.DataFrame({"A":["aa", "hoge"], "B":[-100, 1], "C":[0.1, 0.2]}) engine = create_engine('sqlite:///test.db', echo=False) df.to_sql("df2", engine, if_exists="append")
なんとなく予想していたことですが、エラーを吐かれました。
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) table df2 has no column named C [SQL: INSERT INTO df2 ("index", "A", "B", "C") VALUES (?, ?, ?, ?)] [parameters: ((0, 'aa', -100, 0.1), (1, 'hoge', 1, 0.2))] (Background on this error at: http://sqlalche.me/e/e3q8)
便利に変更部分だけ反映してくれたりはしないので、使いみちは限られると言えます。それがしたければif_exists="replace"の方が良いのですが、頻繁にやると遅くなるのでなにか考えた方が良いでしょう。
まとめ
ちょっと微妙・・・
使い方次第でしょうね。pandasデータフレームを何も考えずにデータベースに突っ込めるので、そういうことをしたいときに重宝するでしょう。だけど、積極的にデータベースと連携させるために使うかというと、それは違う気がします。
*1:ところで、こういうタイピングに苦痛を伴う命名はやめてほしい
