PythonでMySQLに一括バルクInsert

2019-10-22 2020-02-08

PythonでMySQLに一括バルクInsert

「PythonのMySQLのInsertが遅い」
大量のデータを挿入したい場合、Insertに時間がかかる。
しかし一括でInsertすることで時間短縮することができる。
今回はそのバルクInsertについて紹介する。

一括でInsertする方法

pymysql の execute の代わりに executemany を使うと一括でInsertできる。

参考：
pymysqlでbulkインサート - MasterYodaの昼下がり

検証

準備

pipで pymysql のライブラリをインストールする。

pip install pymysql

MySQLのDBに検証用のテストテーブルを作る。

CREATE TABLE <code>hoge</code> (
  <code>id</code> int(11) NOT NULL AUTO_INCREMENT,
  <code>name</code> varchar(50) NOT NULL,
  <code>value</code> double NOT NULL,
  <code>text</code> text NOT NULL,
  PRIMARY KEY (<code>id</code>)
) ENGINE=InnoDB AUTO_INCREMENT=98289 DEFAULT CHARSET=latin1

個別Insert

ローカルのDBに100,000行Insertするのに約13秒。

import pymysql
import datetime

print("Connect to DB")
conn = pymysql.connect(user="root", password="", host="localhost", database="test")

# Insert one by one
def insert_data(values):
    insert_sql = "INSERT INTO test.hoge (name, value, text) values (%s,%s,%s)"

    cur = conn.cursor()
    for value in values:
        cur.execute(insert_sql, value)

def main():
    # Generate data
    values = []
    print("Generate data")
    for i in range(100000):
        name = "name_{}".format(i)
        value = i
        text = "text_{}".format(i)
        values.append([name,value,text])
    print("Length of data: {}".format(len(values)))
    print()

    # Insert one by one
    print("Insert data")
    start_time = datetime.datetime.now()
    print("Start:" + str(start_time))
    insert_data(values)
    end_time = datetime.datetime.now()
    print("End:" + str(end_time))
    diff_time = end_time - start_time
    print("Diff:" + str(diff_time))
    print()

if __name__ == "__main__":
    main()

一括Insert

違いは insert_data_bulk の中で executemany を使っている。
ローカルのDBに100,000行Insertするのに約2秒。
5～6倍くらい早くなっている。
ちなみに1,000,000行で実行すると個別137秒、一括26秒なのでやはり5倍ほど早い。

import pymysql
import datetime

print("Connect to DB")
conn = pymysql.connect(user="root", password="", host="localhost", database="test")

# Bulk Insert
def insert_data_bulk(values):
    print("Insert bulk data")
    insert_sql = "INSERT INTO test.hoge (name, value, text) values (%s,%s,%s)"

    cur = conn.cursor()
    cur.executemany(insert_sql, values)

def main():
    # Generate data
    values = []
    print("Generate data")
    for i in range(100000):
        name = "name_{}".format(i)
        value = i
        text = "text_{}".format(i)
        values.append([name,value,text])
    print("Length of data: {}".format(len(values)))
    print()

    # Bulk Insert
    print("Insert data")
    start_time = datetime.datetime.now()
    print("Start:" + str(start_time))
    insert_data_bulk(values)
    end_time = datetime.datetime.now()
    print("End:" + str(end_time))
    diff_time = end_time - start_time
    print("Diff:" + str(diff_time))
    print()

if __name__ == "__main__":
    main()