テーブルへのINSERT処理の基本
Pythonで処理したデータをSqlalchemyとかsqlite3とかで、テーブルにINSERTする方法は大きく分けて3つになります。
①for文で1つずつ入れていく
まず当たり前ですがqitta辺りあるSqlalchemy・sqlite3でのINSERT処理のサンプルコードをパクって、for文で1つ1つINSERT処理しているとゲロ遅いです。個人プログラムでちょっとしたデータ処理で使うならそれでもいいかもしれません。
がやはり、処理するデータフレームが数万行単位になってくると無限に終わる気がしなくなるので、DBのテーブルへのデータINSERT処理は基本的に一括でINSERTしてくれるBulkInsertで行うのが一般的です。
②df.to_sql()
これはPandasの関数でORMで呼び出したエンジンを使ってデータフレームのデータを一括でBulkInsertしてくれるがpandasをかましているのでちょっと遅い。でも気が利くので細かいエラーは内部で勝手に修正してくれたりする。
③辞書型にしてCOREでINSERT
df.to_dict(orient=’records’)でpandasを辞書リストに変換し変換した辞書のリストをINSERTで一機にぶち込むという方法。計測はしていないが体感df.to_sqlと大差ない。
④ORMを使わずドライバーで直接INSERTする
これはSqlalchemyやsqlite3のようなORMを使わずに各データベースのドライバーを使用して、各データベース言語で一番早いINSERT方法でINSERTするというものORMによるエラーハンドリングないため少し扱いにくいがその分、INSERT速度は上三つとは段違いに早い。ポスグレとかだとpsycopg経由でcopyfromとかがある。
PandasでのINSERT処理はdf.to_sql()が無難？
例えばあなたが手っ取り早くPandasで作ったデータフレームをテーブルにINSERTしたい場合はdf.to_sql()を使うのが無難です。データも勝手にPandasの方で合わせてくれるのでエラーも起きにくく使いやすいです。
df.to_sql()は昔は遅いとか言われてましたが、pandas0.24以降からは内部で発行しているクエリを見る限り、一括でデータをINSERTするバルクインサートになっており、INSERT速度はfor文でORMで一行一行挿入するよりは明らかに早いです。
さらにuse_batch_mode=Trueという引数をエンジンにつけるとさらに3倍くらいはやくなる（ただこのモードはまだ試作段階で適用すると、INSERT処理は早くなるが、SELECT処理が数十倍遅くなるので注意！この先バージョンが更新されれば解消されるかも？） https://github.com/pandas-dev/pandas/issues/15276
データベースの種類に依存しない手法だとPython上からDBにあるテーブルにデータをINSERTするのは、この方法が一番早くて楽だと思います。
ポスグレならORMを使わずにより早くできる
ただポスグレであればCOPY文があり、これが爆速。coreでのINSERTよりもけた違いに早い。ORMを使った時だと怒らないエラーが起こるので繊細なので、めんどくさい。
私が実務の際に苦労したのは特にint型とfloat型の扱いで、整数しか入らないところに1.0みたいな小数点を入れようとしているとかエラーが起こりました、
1.0を1にするみたいな処理はpandasやSqlalchemyを経由してINSERTすると、向こうが勝手に察してくれてintにしてくれるが、ポスグレのドライバーであるpsycopgのcopyfromでINSERTしようとすると型が違うとかエラーを吐くので、INSERTする前に、Pandasのデータフレームでエラーの原因になりそうな列をastypeやapplyやlamdaを駆使して整形しておく必要がある。
# numberという列のデータを一括で文字型に変換する
（例）df[‘number’] = df[‘number].dropna().apply(lambda x: str(int(x)))
そしてもう1つミソなのは、copyfromでINSERTするにはカンマ区切りの文字データである必要があることです。
df = pd.DataFrame([1,2,3,4], columns=['test'])
test
0 1
1 2
2 3
3 4
例えば、こんなデータフレームを対応するテーブルにcopyfromでINSERTする場合はまずこのデータフレームを区切り文字列の塊に変換する必要があります。
難しそうですが、これは皆さんお馴染みのdf.to_csv()で実現できます。普通のdf.to_csv()は第一引数に出力先のディレクトリとcsv名を指定しますが、ここをNoneにすると、戻り値にデータフレームを区切り文字列に変換したものを返してくれます。
insert_data=df.to_csv(None,index=False,header=None)
<実行結果>
‘1\r\n2\r\n3\r\n4\r\n’
ですが、区切り文字の羅列にしたSTR型はひとまとまりのデータではないので、これをINSERTしようとするとエラーになります。そのために必要なのが区切り文字を1つのデータにしてくれるStringIOです。
Python2と3の間でライブラリの呼び出し方が変わっていてコード改修時によくバグの原因になってイラっとするやつですが、やはり画像やテキスト処理に便利です。
# 区切り文字をstringIOに変換する import io f=io.StringIO(insert_data) <実行結果> >>> f <_io.StringIO object at 0x0000018594DD9AF8>
あとはこれをcopyfromでinsertすると、列の型とnotnullとかのテーブル設定をクリアしていればINSERTできています。
まあpandas+ポスグレという組み合わせがレアなので、恐らくどうでもいいような気もしますが、業務でちょっと時間をかけて発見したことなので、まとめておきます
結論
・ポスグレならpsycopg2のCOPY FORMが一番早い
・それ以外ならCORE.INSERTかdf.to_sql()
・もっと大きいならSPARKとかで分散処理
またどうでもいいことに詳しくなってしまいました、どうせならもっと大規模な処理の知見の方が転職活動とかでも活かせるのになと思うが、これも経験かなと思います。DB関連の基本的な処理ができていないとバックエンドエンジニアとしては致命的ですし。
コメント