静かなる名辞

pythonとプログラミングのこと

2019/03/22:TechAcademyがteratailの質問・回答を盗用していた件
2019/03/26:TechAcademy盗用事件 公式発表と深まる疑念



【python】pandasのto_sqlを試してみる

はじめに

 気軽にDataFrameをデータベーステーブルに変換できそうなto_sqlなるものがあるので、試してみます。

pandas.DataFrame.to_sql — pandas 0.23.4 documentation

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:ところで、こういうタイピングに苦痛を伴う命名はやめてほしい