Pandasデータフレームをテーブルに高速INSERTする方法

テーブルへの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処理が数十倍遅くなるので注意!この先バージョンが更新されれば解消されるかも?)

データベースの種類に依存しない手法だと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関連の基本的な処理ができていないとバックエンドエンジニアとしては致命的ですし。

プログラミングの独学はとても難しい


プログラミングは小学校の義務教育にも導入され始めており、これから社会人として生きていく上でプログラミングはもはや出来て当たり前、出来なれば論外というエクセルレベルの必須スキルになりつつあります。そしてそういう話を聞いて参考書なりを購入して独学でプログラミング勉強しようと思っている人も少なくないでしょう。しかしプログラミングを独学で勉強し始めようと思うものの



・「分からない箇所で詰まって挫折してしまった」

・「勉強する時間が足りない」

・「ネットの記事だと情報が断片的でよくわからない」

・「コードのエラーの原因が分からない」



という壁にぶち当たって、プログラミングの勉強を止めてしまう方が少なくありません。独学でプログラミングを勉強してる時間のほとんどはつまづいている時間です。実際僕も最初のころ独学でプログラミングを勉強していた頃はエラーの原因が分からず丸1日を不意にしてしまった・・・そんな苦い経験がありました。



それで僕は一度はプログラミングの学習を諦めてしまいましたが、就活で現実を知る中で「プログラミングを勉強して、いずれフリーランスとして自由な生き方がしたい」「エンジニアとして若いうちから高収入を得たい」という気持ちから一念発起して「侍エンジニアのwebサービスコース」に申し込み、プロのエンジニアの方に対面でマンツーマンでPythonによるWebサービス作り方とWeb技術の基本を教えてもらい、ようやくプログラミングが理解でき、今ではエンジニアとしてそこそこの暮らしができるようになりました。





侍エンジニアでは、とりあえずプログラミングやインターネットの基本を知っておきたい人から、HTML・cssなどでWebサイトやWebアプリを作ってみたい人やPythonを勉強してデータサイエンティストやAIエンジニアになりたい人まで幅広いニーズに応えた様々なコースが用意されています。



IT業界と言ってもエンジニアの仕事はプログラミング言語次第でサーバーから機械学習・ディープラーニングまで多種多様ですし、侍エンジニアの無料レッスン(カウンセリング)を受けてみて、自分のやりたいITの仕事は何なのか?を見つけるのがエンジニアへの第一歩になります。ちなみに今侍エンジニアの無料レッスンを受けると1000円分のAmazonギフト券がもらえるので、試しに受けてみるだけもお得です。


自分は半端に独学やオンラインスクールで勉強して金と時間を無駄にするくらいなら、リアルのプログラミングスクールに通ってしっかりプログラミングを勉強した方がいいと思います。ちなみに今、侍エンジニアに申し込むと、25歳以下の学生の方であれば、受講料が20%OFFになるので超お得です。


そして、プログラミングは大勢で授業を受けたり漫然とオンライン学習をするよりも自分が分からない箇所をピンポイントでプロの講師に直接質問して、ちゃんと納得するというスタイルの方がお金は確かに少し掛かりますが、独学で学ぶよりも絶対にモノになります。


シェアする

  • このエントリーをはてなブックマークに追加

フォローする