[pymysql]. 파이썬 SQL 쿼리 한꺼번에 전송하기

pymysql 패키지를 사용하여 데이터 프레임에 있는 데이터를 DB에 삽입할때 ( 쿼리를 전송할 때 ) 우리는 

 

크게 세 가지의 과정을 거친다.

 

첫 번째, 

 

DB 커넥트 ( DB 접속 정보를 가지고 DB와 연결하는 과정 )

커서 객체 생성

import pymysql

# data base connect
db = pymysql.connect(**RDS_config, cursorclass=pymysql.cursors.DictCursor)

# 커서 객체 생성
cursor = db.cursor()

두 번째,

 

쿼리문 작성 : 실행하고자 하는 쿼리문을 작성합니다.

execute : 쿼리문을 실행시킵니다.

query = """INSERT INTO {} VALUE =({},{},{})""".format(table_name,value1,value3,value3)

cursor.execute(query)

 

세 번째,

 

commit : 커밋합니다. ( 커밋하지 않으면 실제 반영되지 않음 )

db.commit()

 

나는 얼마전 까지만 해도 이 과정을 반복적으로 수행하는  for 문을 작성하여 데이터 삽입을 진행했다.

하지만 내가 채워야하는 데이터의 수는 무려 1500만건에 데이터

 

이러한 과정으로 실행을 했더니 예상시간이 무려 8시간이 나왔다.

 

이유를 알아보니 데이터를 하나 삽입할 때마나 커밋(commit)을 진행하기 때문에 시간이 많이 소요되었던 것

 

사람으로 따지면 택배를 화물차에 실어서 한번에 옮기는 것이 아니라 하나 하나씩 옮기고 있었던 것이다.

(이거 오늘 퇴근전까지는 절때못하겠다 싶음... )

 

그래서 찾아본 결과 executemany 라는 함수가 있더라.

 

executemany 는 함수명 그대로 하나씩 하는 것이 아니라 리스트와 튜플 형태로 넣어준 값을 한번에 빵! 처리해주는 함수.

data = []

# 총 1500만 건에 데이터
for i in range(len(df))):
	value = df.loc[i]
    values = [
    	int(value['idx']),
        str(value['name']),
        str(value['title'])
    ]
	# 데이터 프레임에 있는 값들을 하나하나씩 불러들여서 그 값들을 리스트에 추가합니다.
	data.append(values)

	if i % 20000 == 0:
    	query = "INSERT INTO table_name(idx,name,title)values(%s,%s,%s)"
        cursor.executemany(query,data)
        db.commit()
        data = []

# 나머지 데이터 삽입 ( 마지막 20000개까지 안 채워진 데이터 )
query = "INSERT INTO table_name(idx,name,title)values(%s,%s,%s)"
cursor.executemany(query,data)
db.commit()

#연결 해제
db.close()

 

values 들이 담겨있는 data 리스트가 20000개가 찼을 경우 ( i % 20000 == 0 ) 에 excutemany 를 해주고 커밋을 이때만 진행합니다.

이렇게 했을 때 비로소 택배트럭에 택배를 가득채우고 나서 출발하는 방식으로 바뀌었습니다.

 

여기서 중요한게 %s 로 표기해둔 데이터로 순서에 따라 자동으로 삽입되는데 유의해야합니다.

*하나씩할때(execute)는 .format을 사용하여 명시할 수 있지만 여기선 %s 를 사용함.

 

하나씩 진행할때 1500만건에 대하여 예상시간이 8시간이 되었지만

executemany를 통해서 2만건에 한 번만 commit을 진행했을 뿐인데 예상시간이 22분으로 훨---씬 속도가 줄었다.

 

8시간 -> 22분