プロジェクトが終わって(外されて…)ひと段落したのでブログを再開したいと思います。
それでは今回は、Pythonを使ってbulk insertをする方法をご紹介します。
bulk insertを使うことで、クエリを何度も発行しなくてもデータ挿入できますし、バッチとして回す場合だと時間短縮にもつながります。
ここではpyodbcというPythonライブラリを用いたbulk insert、bcpというコマンドを用いたbulk insertの2つの方法をご紹介していきます。
そもそもBulk insert とは?
insert文は通常では、テーブルに行を追加する際に 1回のSQL文の実行で 1つのデータしか追加できません。
INSERT INTO student(id, name, class) VALUES (1, 'たろう', 'A');
INSERT INTO student(id, name, class) VALUES (2, 'じろう', 'A');
INSERT INTO student(id, name, class) VALUES (3, 'さぶろう', 'B');
このinsert文を、1回のSQL文の実行でまとめて複数の行を追加することをbulk insertと言います。
INSERT INTO student(id, name, class) VALUES
(1, 'たろう', 'A'), (2, 'じろう', 'A'), (3, 'さぶろう', 'B');
Pythonを使ったSQL Serverでのbulk insertをする方法は2つあります。
1つは、上記で紹介したように、insert文にデータを複数入れる方法。
もう1つは、bcpと呼ばれる ”bulk copy program”の頭文字をとった コマンドを使うことです。
bcpでは、テキストファイルや 実行クエリ文で取得したデータをbulk insertすることができます。
事前準備
事前準備として、pyodbcのインストール、bcpのインストールを行う必要があります。
方法1はpyodbcを、 方法2はbcpをインストールします。
pyodbcのインストール
まずはPythonでSQL Serverを扱うための準備としてpyodbcをインストールします。
pipを使って下記コマンドを実行してインストールします。
pip install pyodbc
# condaを使用している場合はcondaでインストールします。
conda install pyodbc
こちらで準備完了です。
bcpのインストール
次に、【方法2】bcpコマンドをつかってbulk insertする 場合は、bcpコマンドのインストールを行います。
Windowsの場合、もともとBCPコマンドが使用できるのでスキップしてください。
Mac や Linux でbcpコマンドを使用する場合には、下記のURLを参考にし、自身の環境に合ったbcpインストール方法をお試し下さい。
参考資料:Linux に SQL Server コマンドライン ツール sqlcmd および bcp をインストールする
挿入するデータ
bulk insertで挿入するデータは、studentというテーブルに格納すると仮定し、入れるデータは下記のようなものと仮定します。
id | name | class |
---|---|---|
1 | たろう | A |
2 | あきら | A |
3 | たけし | B |
4 | ゆうき | C |
… | … | … |
9999 | ひかり | Z |
10000 | あかり | Z |
ID、名前、クラスのカラムがある10000人のデータを用意してる体で、これからの話を進めていきます。
では、実際にbulk insertができるコードを紹介していきます。
【方法1】executemanyを使ってbulk insertする
SQL Serverに接続する
まずはpyodbcを使用して、SQL Serverに接続します。
接続先に合わせて、それぞれサーバー名、接続先DB、ユーザー名、パスワードを記載します。
もしポートを自身で変更している場合はportも変更してください。
以下にSQL認証の方法とWindows認証の方法をご紹介します。
SQL認証の場合
import pyodbc
server = "サーバー名"
db = "接続先DB"
user = "ユーザー名"
password = "パスワード"
port = "1433"
table = "student"
batch_size = 10000 # 後で使用する
# 接続先情報を使用してDBに接続
conn = pyodbc.connect(
'DRIVER={ODBC Driver 17 for SQL Server};' +
'SERVER=' + server + ';' +
'DATABASE=' + db + ';' +
'UID=' + user + ';' +
'PWD=' + password + ';' +
'PORT=' + port
)
Windows認証
import pyodbc
server = "サーバー名"
db = "接続先DB"
port = "1433"
table = "student"
batch_size = 1000 # 後で使用する
# 接続先情報を使用してDBに接続
conn = pyodbc.connect(
'DRIVER={ODBC Driver 17 for SQL Server};' +
'SERVER=' + server + ';' +
'DATABASE=' + db + ';' +
'PORT=' + port
'Trusted_Connection=yes;'
)
これで接続は完了です。
executemanyでbulk insertをする
今回bulk insertの代わりに使用するのは、executemanyというものです。
こちらは、クエスチョンパラメータ(バインドパラメータ)にしたSQL文にパラメータを渡す方法です。
クエスチョンパラメータをしたSQL文と、代入するパラメータをタプル型のリストで渡します。
# カーソルを定義
cursor = conn.cursor()
# クエスチョンパラメータにしたSQL文を用意
sql = "INSERT INTO student(id, name, class) VALUES (?, ?, ?);"
# データをタプル型のリストで用意する(CSVファイルから取得しても良い)
data = [(1, "たろう", "A"), (2, "あきら", "A"), (3、"たけし", "B")]
cursor.fast_executemany = True # 高速でexecutemanyを行うための呪文
batch_size = 1000 # 1回でデータを入れる量
# 全てのデータを、バッチサイズで区切ってSQLを実行する。
for i in range(0, len(data), batch_size):
cursor.executemany(sql, data[i : i + batch_size])
# コミットしてデータを反映させる
cursor.commit()
for文で分割したデータを、executemanyしてデータをbulk insertすることができます。
batch_size で区切ることで、一気にデータを入れることをせずにコンスタントにデータを入れることができます。
全体のコード
# -*- coding: utf-8 -*-
import pyodbc
server = "サーバー名"
db = "接続先DB"
user = "ユーザー名"
password = "パスワード"
port = "1433"
table = "student"
batch_size = 10000 # 後で使用する
# 接続先情報を使用してDBに接続
conn = pyodbc.connect(
'DRIVER={ODBC Driver 17 for SQL Server};' +
'SERVER=' + server + ';' +
'DATABASE=' + db + ';' +
'UID=' + user + ';' +
'PWD=' + password + ';' +
'PORT=' + port
)
"""
# Windows認証の場合
# 接続先情報を使用してDBに接続
conn = pyodbc.connect(
'DRIVER={ODBC Driver 17 for SQL Server};' +
'SERVER=' + server + ';' +
'DATABASE=' + db + ';' +
'PORT=' + port + ';' +
'Trusted_Connection=yes;'
)
"""
# カーソルを定義
cursor = conn.cursor()
# クエスチョンパラメータにしたSQL文を用意
sql = "INSERT INTO student(id, name, class) VALUES (?, ?, ?);"
# データをタプル型のリストで用意する(CSVファイルから取得しても良い)
data = [(1, "たろう", "A"), (2, "あきら", "A"), (3、"たけし", "B")]
cursor.fast_executemany = True # 高速でexecutemanyを行うための呪文
batch_size = 1000 # 1回でデータを入れる量
# 全てのデータを、バッチサイズで区切ってSQLを実行する。
for i in range(0, len(data), batch_size):
cursor.executemany(sql, data[i : i + batch_size])
# コミットしてデータを反映させる
cursor.commit()
# コネクションを閉じる
cursor.close()
conn.close()
【方法2】bcpコマンドをつかってbulk insertする
次にbcpコマンドでbulk insertをする方法を紹介します。
bcpコマンドでデータを入れる場合は、データをテキストファイル(CSVファイルなど)に記載する必要があり、DBのカラムと同様の順番でファイルに格納する必要があります。
今回はデータをCSVファイルに格納していると仮定しています。
データが入ったCSVファイルが用意できたら、下記のコードにCSVファイルのパスを追加することでbcpコマンドを使用したbulk insertをすることができます。
import subprocess
csv_file = "ファイルのパス"
# BCPコマンドを用意
bcp = "bcp {table} in '{csv_file}' -S {server} -U {user} -P {password} -d {database} -b {batch_size} -c -C 65001 -a 32784 -t ','".format(
table=table,
csv_file=csv_file,
server=server,
user=user,
password=password,
database=database,
batch_size=batch_size
)
subprocess.run(bcp, shell=True)
bcpコマンドのテキストを用意し、それをsubprocessというPython標準ライブラリで実行しています。
bcpのコマンドでは、大まかに分けて3つの要素で構成されています。
# bcpコマンドの構成
# 1. データを格納するテーブル先と、CSVファイル
bcp '格納するテーブル' in '格納したいデータがあるCSVファイル'
# 2. 接続先の情報
-S {server} -U {user} -P {password} -d {database}
# 3. オプション
-b {batch_size} -c -C 65001 -a 32784 -t ','
オプションは以下のようになっています。(公式ドキュメントから引用)
-b : 一括インポートするデータの行数を指定します。
bcp ユーティリティ – Microsoft SQL ドキュメントより
-c : このオプションを使用すると、フィールドごとにプロンプトが表示されません。 char をプレフィックスなしのストレージ型として、また \t (タブ) をフィールド区切り文字、 \r\n (改行文字) を行ターミネータとして使用します。
-C : データファイル内のデータのコード ページを指定します。 code_page は、データに char、 varchar、 text 列 (文字値が 127 より大きいか、32 未満) が含まれている場合にのみ当てはまります。…(中略)
-a : サーバーとの間で送信されるネットワーク パケットごとのバイト数を指定します。
-t : フィールド ターミネータを指定します。 既定値は \t (タブ文字) です。
要約すると、上記でおこなっていたコードは以下のようになります。
-b {batch_size} # 一括インポートする行数
-c # フィールドの確認画面なし
-C 65001 # データの文字コードを 「UTF-8」 に設定する
-a 32784 # パケットバイト数を 「32784」 に設定
-t ',' # CSVファイルの区切り文字を「カンマ」に指定
# もしCSVファイルのヘッダーがある場合はこのようにする
-F 2 # 上から2行目から値のインポートを始める
このように、CSVファイルの形式などを指定したり、インポートする形式を指定することでbulk insertを行うことができます。
こちらは【方法1】executemanyを使ってbulk insertする の方法よりも何倍も早いです。(詳細な検証はしていませんが、業務で使用した際はbcpの方が圧倒的に早かったです。)
bcpコマンドの詳しい説明はこちらの公式ドキュメントをご覧ください。
全体のコード
# -*- coding: utf-8 -*-
import pyodbc
import subprocess
server = "サーバー名"
db = "接続先DB"
user = "ユーザー名"
password = "パスワード"
port = "1433"
table = "student"
batch_size = 10000 # 後で使用する
csv_file = "ファイルのパス"
# BCPコマンドを用意
bcp = "bcp {table} in '{csv_file}' -S {server} -U {user} -P {password} -d {database} -b {batch_size} -c -C 65001 -a 32784 -t ','".format(
table=table,
csv_file=csv_file,
server=server,
user=user,
password=password,
database=database,
batch_size=batch_size
)
subprocess.run(bcp, shell=True)
まとめ
いかがだったでしょうか!
insertの観点からみたら、bcpの方が圧倒的にパフォーマンスが良いです。
しかし、insert以外にもupdateやデータ加工を行うと考えると、pyodbcの方が汎用性が高いのでどちらの方法も覚えておくと適材適所に使用できると思います!
bulk insertはかなり便利ですし、100万件を超えるデータを入れる際は必ず使用した方がパフォーマンスが上昇するので、是非使用してみてください。
・bcpコマンドは、ファイルを元にデータを登録することができる
Pythonでの bulk insert の方法は2通り
1. pyodbcのexecutemanyメソッドを使用する
2. bcpコマンドで、CSVファイル等からデータを入れる
この記事が誰かのお役に立てますように。
SQL Server関連ブログはこちら!
pythonを勉強中のものです。SQL ServerへのBulk Insertを調べてて、このサイトに辿り着きました。
cursor.commit() -> conn.commit() じゃないですか?
他のサイトだとconn.commit()が多かったです。
何を確認すれば正解が分かるか分からず、漠然とした質問で申しわけないですが。。。
ご質問していただきありがとうございます。
pyodbcのドキュメントを確認すると、cursor.commit()という用法の記載があります。
https://github.com/mkleehammer/pyodbc/wiki/Cursor#commit
connection.commit()
もcursor.commit()
も、処理は同じなのでお好みの方法で良いと思います。また、「
pyodbc "cursor.commit"
」と検索していただくと他にも使用しているコードが散見されますのでこちらも確認してみてください。