データベースに対してデータを登録する場合は insert 文を使いますが、多くのデータベースは1回のinsert 文で複数のデータを一括登録することが可能です。
今回は、PostgresSQL における一括インサート(マルチテーブルインサート）について、具体的なSQLの描き方と、Pandas と numpy の2次元配列からマルチテーブルインサートを自動生成するPythonプログラムについて紹介します。
PostgreSQLのマルチテーブルインサートの描き方
通常のインサート文（1行づつ登録するインサート文）は、
insert into テーブル名(カラム名1,カラム名2,・・・,カラム名n) values(値1,値2,・・・値n)
というフォーマットになります。
例えば、４つのデータを登録する場合は、１行づつインサート文を実行しなければなりません。
INSERT INTO hoge (id, name, age, address) VALUES (1001, '山田', 36, 'yamada@abc.com')
INSERT INTO hoge (id, name, age, address) VALUES (1002, '鈴木', 28, 'suzuki@docoda.jp')
INSERT INTO hoge (id, name, age, address) VALUES (1003, '斎藤', 49, 'saito@cocoda.com')
INSERT INTO hoge (id, name, age, address) VALUES (1004, '田中', 53, 'tanaka@ocnco.jp')これを１つのインサート文で登録する場合、最初の1データは通常のインサート文と同じですが、2データ目以降は値の羅列をカンマ区切りで指定します。
insert into テーブル名(カラム名1,カラム名2,・・・,カラム名n) values(値1,値2,・・・値n) ,
(値1,値2,・・・値n) , (値1,値2,・・・値n) , (値1,値2,・・・値n) , (値1,値2,・・・値n)
従って、４つのデータを1つのインサート文で登録する（マルチテーブルインサート）には、次の様になります。
INSERT INTO hoge (id, name, age, address) VALUES (1001, '山田', 36, 'yamada@abc.com'),
(1002, '鈴木', 28, 'suzuki@docoda.jp'),
(1003, '斎藤', 49, 'saito@cocoda.com'),
(1004, '田中', 53, 'tanaka@ocnco.jp')マルチテーブルインサートはデータベースへの命令の発行回数が少なくなる分、処理速度が向上するというメリットがありますが、PostgreSQLの場合はそれに加えて 2データ目以降の insert ～ values の部分が省略できますので、データベースとの通信量も削減できるというメリットもあります。
1回のインサート文に、何データくらい詰め込んだらよいかについてはメモリ容量との兼ね合いで一概に言えませんが、100、500、1000、10000という風に試していって、一番よい件数を見つけていくのが良いかと思います。
Pythonによるマルチテーブルインサートの自動生成
ここからはPythonを使って、DataFrameとndarray に格納されている表形式データからマルチテーブルインサート文を自動生成するサンプルプログラムの説明に入ります。
自動生成部分は関数化していますのでコピペでお使いいただけますが、元となるデータはDドライブに存在する 「abc.csv」（下記の内容）を読み込んでいますので、その部分のみ適宜ご自身の環境に書き換えてください。
date,col1,col2,col3,col4,col5
2021/10/30 11:10:01,12,34,56,1,True
2021/10/30 11:11:02,10,20,30.0,0,True
2021/10/30 11:12:03,11,12,13,1,False
2021/10/30 11:13:04,10,15,30,1,False
PandasのDataFrameからマルチインサート文を自動生成
PandasのDataFrameに格納された表形式のデータからマルチインサート文を自動生成するプログラムのサンプルです。
第一引数はDataFrame、第2引数はテーブル名、第3引数はカラム名の羅列で、省略された場合はDataFrameの columns の内容が使われます。
import pandas as pd
def multi_insert(df,table,columns = None):
#columns が Noneの場合、df の columns からカラム名を取得
if columns is None:
columns = ",".join([i for i in df.columns])
#insert 文の insert ～ values までを作成
insert = "insert into {0}({1}) values".format(table,columns)
#カラムの列のデータタイプがobjectならシングルクォートを、それ以外なら空文字をセット
quote = ["'" if str(dtype) == 'object' else '' for dtype in df.dtypes]
#DataFrameから1行づつ読み込んで、values 以降の文字列を生成
for row in df.itertuples():
data = [quote[i] + str(row[i+1]) + quote[i] for i in range(len(quote))]
insert += "({0}),".format(','.join(data))
#末尾のカンマを削除して返す
return insert.rstrip(',')インサート文の valuesに登録する値を記述する際、 データタイプが文字列の場合は シングル クォートで括る必要があります。
そのため、DataFrameからデータタイプ型のリストを取得し
quote = ["'" if str(dtype) == 'object' else '' for dtype in df.dtypes]
という１行によって quote 変数に シングルクォートか空文字のどちらかをセットしています。
使い方のサンプルは次の通りです。
#データの読み込み
csv = pd.read_csv('d:/abc.csv')
#使い方１
sql = multi_insert(csv,'hoge','date,col1,col2,col3,col4,col5')
print(sql)
#使い方２（カラム名の指定を省略）
sql = multi_insert(csv[['date','col1','col2']],'hoge')
print(sql)実行結果は次の様になります。
Numpyのndarrayからマルチインサート文を自動生成
Numpyのndarray に格納された表形式データに対して、 マルチインサート文を自動生成するプログラムのサンプルです。
こちらも先ほどと同様、Windows上のpドライブにあるabc.csv からデータを取得して、マルチインサートを生成しています。
第一引数はndarray、第2引数はテーブル名、第3引数はカラム名の羅列で、省略は出来ません。
import numpy as np
def multi_insert2(array,table,columns):
#insert 文の insert ～ values までを作成
insert = "insert into {0}({1}) values".format(table,columns)
#カラムの列のデータタイプがobjectならシングルクォートを、それ以外なら空文字をセット
quote = ["'" if type(ar) is str else '' for ar in array[0]]
#ndarrayから1行づつ読み込んで、values 以降の文字列を生成
for row in array:
data = [quote[i] + str(row[i]) + quote[i] for i in range(len(quote))]
insert += "({0}),".format(','.join(data))
#末尾のカンマを削除して返す
return insert.rstrip(',')使い方のサンプルは次の通りです。
csv = pd.read_csv('d:/abc.csv')
sql = multi_insert2(csv.values[:,[0,1,2,3]],'hoge','date,col1,col2,col3,col4,col5')
print(sql)実行結果は次の通りです。
まとめ
今回は、1回のインサート文で複数のデータが登録できる「マルチテーブルインサート」について、PostreSQLの場合の描き方と、PythonのPandasとNumpyに格納された表形式データから簡単にインサート文が生成できるソースコードを紹介しました。
マルチテーブルインサートにするだけで、数倍～十数倍（20倍や30倍になるという噂も・・・）もインサートが高速化されますので、大量データを登録する際は効果的です。
是非ご活用ください。
コメント