PostgreSQLに1回のinsertで複数データ登録 by Python

当ページのリンクには広告が含まれています。

データベースに対してデータを登録する場合は insert 文を使いますが、多くのデータベースは1回のinsert 文で複数のデータを一括登録することが可能です。

今回は、PostgresSQL における一括インサート(マルチテーブルインサート)について、具体的なSQLの描き方と、Pandas と numpy の2次元配列からマルチテーブルインサートを自動生成するPythonプログラムについて紹介します。

目次

PostgreSQLのマルチテーブルインサートの描き方

通常のインサート文(1行づつ登録するインサート文)は、

insert into テーブル名(カラム名1,カラム名2,・・・,カラム名n) values(値1,値2,・・・値n)

というフォーマットになります。

例えば、4つのデータを登録する場合は、1行づつインサート文を実行しなければなりません。

これを1つのインサート文で登録する場合、最初の1データは通常のインサート文と同じですが、2データ目以降は値の羅列をカンマ区切りで指定します。

insert into テーブル名(カラム名1,カラム名2,・・・,カラム名n) values(値1,値2,・・・値n) ,

  (値1,値2,・・・値n) , (値1,値2,・・・値n) , (値1,値2,・・・値n) , (値1,値2,・・・値n)

従って、4つのデータを1つのインサート文で登録する(マルチテーブルインサート)には、次の様になります。

マルチテーブルインサートはデータベースへの命令の発行回数が少なくなる分、処理速度が向上するというメリットがありますが、PostgreSQLの場合はそれに加えて 2データ目以降の insert ~ values の部分が省略できますので、データベースとの通信量も削減できるというメリットもあります。

1回のインサート文に、何データくらい詰め込んだらよいかについてはメモリ容量との兼ね合いで一概に言えませんが、100、500、1000、10000という風に試していって、一番よい件数を見つけていくのが良いかと思います。

Pythonによるマルチテーブルインサートの自動生成

ここからはPythonを使って、DataFrameとndarray に格納されている表形式データからマルチテーブルインサート文を自動生成するサンプルプログラムの説明に入ります。

自動生成部分は関数化していますのでコピペでお使いいただけますが、元となるデータはDドライブに存在する 「abc.csv」(下記の内容)を読み込んでいますので、その部分のみ適宜ご自身の環境に書き換えてください。

PandasのDataFrameからマルチインサート文を自動生成

PandasのDataFrameに格納された表形式のデータからマルチインサート文を自動生成するプログラムのサンプルです。

第一引数はDataFrame、第2引数はテーブル名、第3引数はカラム名の羅列で、省略された場合はDataFrameの columns の内容が使われます。

インサート文の valuesに登録する値を記述する際、 データタイプが文字列の場合は シングル クォートで括る必要があります。

そのため、DataFrameからデータタイプ型のリストを取得し

  quote = ["'" if str(dtype) == 'object' else '' for dtype in df.dtypes]

という1行によって quote 変数に シングルクォートか空文字のどちらかをセットしています。

使い方のサンプルは次の通りです。

実行結果は次の様になります。

Numpyのndarrayからマルチインサート文を自動生成

Numpyのndarray に格納された表形式データに対して、 マルチインサート文を自動生成するプログラムのサンプルです。

こちらも先ほどと同様、Windows上のpドライブにあるabc.csv からデータを取得して、マルチインサートを生成しています。

第一引数はndarray、第2引数はテーブル名、第3引数はカラム名の羅列で、省略は出来ません。

使い方のサンプルは次の通りです。

実行結果は次の通りです。

まとめ

今回は、1回のインサート文で複数のデータが登録できる「マルチテーブルインサート」について、PostreSQLの場合の描き方と、PythonのPandasとNumpyに格納された表形式データから簡単にインサート文が生成できるソースコードを紹介しました。

マルチテーブルインサートにするだけで、数倍~十数倍(20倍や30倍になるという噂も・・・)もインサートが高速化されますので、大量データを登録する際は効果的です。

是非ご活用ください。

よかったらシェアしてね!
  • URLをコピーしました!
  • URLをコピーしました!

コメント

コメントする

目次