てるてるの奮闘日記 自由に開発、自由にアウトプット

注目キーワード

【Python】Pythonを使ってSQL Serverのテーブルにbulk insertする方法2選

【Python】Pythonを使ってSQL Serverのテーブルにbulk insertする方法2選

プロジェクトが終わって（外されて…）ひと段落したのでブログを再開したいと思います。

それでは今回は、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というテーブルに格納すると仮定し、入れるデータは下記のようなものと仮定します。

idnameclass
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 : 一括インポートするデータの行数を指定します。

-c : このオプションを使用すると、フィールドごとにプロンプトが表示されません。 char をプレフィックスなしのストレージ型として、また \t (タブ) をフィールド区切り文字、 \r\n (改行文字) を行ターミネータとして使用します。 

-C : データファイル内のデータのコード ページを指定します。 code_page は、データに char、 varchar、 text 列 (文字値が 127 より大きいか、32 未満) が含まれている場合にのみ当てはまります。…(中略)

-a : サーバーとの間で送信されるネットワーク パケットごとのバイト数を指定します。 

-t : フィールド ターミネータを指定します。 既定値は \t (タブ文字) です。

bcp ユーティリティ – Microsoft SQL ドキュメントより


要約すると、上記でおこなっていたコードは以下のようになります。

-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万件を超えるデータを入れる際は必ず使用した方がパフォーマンスが上昇するので、是非使用してみてください。

まとめ
・pyodbcは、odbc経由でデータベースに接続することが可能なライブラリ
・bcpコマンドは、ファイルを元にデータを登録することができる

Pythonでの bulk insert の方法は２通り
１. pyodbcのexecutemanyメソッドを使用する
２. bcpコマンドで、CSVファイル等からデータを入れる

この記事が誰かのお役に立てますように。


SQL Server関連ブログはこちら！

【SQL Server】コピペで使えるSQL Serverを調査・解析する時に便利なクエリ一覧


SQL Serverカテゴリの最新記事