파이썬(python) MSSQL 테이블에 데이터 프레임을 빠르게 insert하는 방법

200k 개의 데이터 프레임을 mssql 테이블에 삽입해야 한다. 한건씩 insert 할 때 많은 시간이 걸린다.

파이썬 스크립트는 다음과 같다.

import pandas as pd
import pyodbc
import numpy as np
   
engine=create_engine("mssql+pyodbc://server1/<database>?driver=odbc drvier 17 for sql server?trusted_connection=yes")
    
df.to_sql('<db_table_name>', engine, if_exists='append')

df.to_sql 함수를 사용하고 있다. 오류는 발생하지 않는다.

chunksize 의 매개변수를 전달해보았는데, 오류도 발생하지 않고, insert 되지 않는다.

배치를 통한 일괄 insert 방법(bulk insert)

행을 하나씩 삽입하는 대신 일괄로 INSERT를 시도해보자. 데이터 프레임을 더 작은 chunks(예: 배치당 1000행)로 나누고 배치에 삽입한다. 이렇게 하면 개별 트랜잭션의 오버헤드를 크게 줄일 수 있다.

fast_executemany 매개변수 사용해서 해결해보자

to_sql 함수에서 method 매개변수를 ‘multi’로 설정하고 fast_executemany=True 옵션을 사용할 수 있다. 이것은 더 효율적일 수 있는 행 삽입을 위한 executemany 메소드를 사용한다.

from sqlalchemy import create_engine
engine = create_engine("mssql+pyodbc://server1/<database>?driver=odbc drvier 17 for sql server?trusted_connection=yes", fast_executemany=True)

chunksize = 1000
for i in range(0, len(df), chunksize):
    df_chunk = df[i:i + chunksize]
    df_chunk.to_sql('<db_table_name>', engine, if_exists='append', method='multi')

자동으로 커밋됨으로 별도의 명령어가 필요치 않다.

주의사항으로 df 열 이름이 mssql 열 이름과 일치해야 한다.

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

engine = create_engine("mssql+pyodbc://server1/<database>?driver=odbc drvier 17 for sql server?trusted_connection=yes")
Session = sessionmaker(bind=engine)
session = Session()

conn = engine.raw_connection()
cursor = conn.cursor()

# Assuming 'data' is a list of dictionaries where keys match the column names
# This code creates an INSERT statement and executes it in bulk
insert_statement = df.to_sql('<db_table_name>', con=engine, if_exists='append', index=False)
cursor.executemany(insert_statement, data)

conn.commit()
cursor.close()
conn.close()

[reference]

how do you insert data frame to ms sql table faster