📈

Pandas の DataFrame から DB に登録する場合の処理速度について比較してみた

2022/01/10に公開

Python

Pandas の DataFrame から DB に登録する場合の処理速度について比較してみました。

コードはこちら

なお、DB は Azure SQL Database を使っています。

Azure SQL Database の設定を下記の変数に格納します。

server = "XXX" # データベースの詳細設定のサーバー名
database = "XXX" # データベース名
username = "XXX" # データベースの詳細設定のサーバー管理者ログイン
password = "XXX" # データベースの詳細設定の更新

デフォルトだとファイアーウォールがかかっているので、 Azure SQL Database のリソース作成後、概要 > サーバーファイアーウォール設定から IPアドレスを設定しましょう。

IPアドレスは下記サイト等で確認しましょう。

Azureポータルの Azure SQL Database のクエリエディターで SQL をたたけば内容を確認できるので便利です。

DB へのアクセスを確認

import time
import pymssql
import pandas as pd
import urllib
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from pangres import upsert

engine = create_engine(f"mssql+pymssql://{username}:{password}@{server}/{database}")

with engine.connect() as conn:
    rs = conn.execute('SELECT @@VERSION as version')
    for row in rs:
        print(row['version'])

出力:
Microsoft SQL Azure (RTM) - 12.0.2000.8
Sep 18 2021 19:01:34
Copyright (C) 2019 Microsoft Corporation

テーブルの定義

Base = declarative_base()

class Person(Base):
    __tablename__ = 'persons'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(14))
    job = Column(String(14))
    address = Column(String(50))

登録する DataFrame を作成します。

df = pd.DataFrame(
    [
        [1, "John1", "Engineer", "America"],
        [2, "John2", "Engineer", "America"],
        [3, "John3", "Engineer", "America"],
        (略)
        [28, "John28", "Engineer", "America"],
        [29, "John29", "Engineer", "America"],
        [30, "John30", "Engineer", "America"],
    ],
    columns = ["id", "name", "job", "address"]
)

for文で1つずつ登録

# テーブルの作成
Base.metadata.create_all(engine)

start = time.time()

Session = sessionmaker(bind=engine)
session = Session()

for i in range(len(df)):
    
    p = Person(
        name = df.iloc[i:i+1]["name"].values[0],
        job = df.iloc[i:i+1]["job"].values[0],
        address = df.iloc[i:i+1]["address"].values[0]
    )
    
    session.add(p)
    session.commit()

session.flush()

for_add_time = time.time() - start
print(f"elapsed_time:{round((for_add_time), 1)}[sec]")

出力:
elapsed_time:11.3[sec]

# テーブルの削除
Base.metadata.drop_all(engine)

to_sql で登録

テーブル作成処理等は前述と同様です。

start = time.time()

df[["name", "job", "address"]].to_sql('persons', engine, if_exists='append', index=False)

to_sql_time = time.time() - start
print(f"elapsed_time:{round((to_sql_time), 1)}[sec]")

出力:
elapsed_time:2.9[sec]

for文で1行ずつ登録した場合が 11.3秒、to_sql で登録が2.9秒と、to_sql を使った方が早いことがわかります。

Update for文で1行ずつ

Update もやってみます。
Update 用の DataFrame を作成します。

df_update = df.copy()
df_update["name"] = "update"
df_update["job"] = "update"
df_update["address"] = "update"

start = time.time()

Session = sessionmaker(bind=engine)
session = Session()

for i in range(len(df_update)):
    
    id_num = int(df_update.iloc[i:i+1].id.values[0])
    mydata = session.query(Person).filter(Person.id == id_num).first()
    
    mydata.name = df_update.iloc[i:i+1]["name"].values[0]
    mydata.job = df_update.iloc[i:i+1]["job"].values[0]
    mydata.address = df_update.iloc[i:i+1]["address"].values[0]
    
    session.add(mydata)
    session.commit()
    
session.flush()

for_update_time = time.time() - start
print(f"elapsed_time:{round((for_update_time), 1)}[sec]")

出力:
elapsed_time:13.2[sec]

to_sql の引数 if_exists "replace" を使うと元のテーブルを削除して新しくテーブルを登録してしまうので気を付けましょう。

pangres の upsert を使うと良いようですが、自分の場合はエラーが出たのでやめました。
使えるようになったらまとめたいと思います。

https://github.com/ThibTrip/pangres

以上になります、最後までお読みいただきありがとうございました。