openpyxl을 이용해서 sql 쿼리문 만들기

간혹 엑셀에 있는 데이터를 DB에 넣어야 할 때가 있습니다.

많은 방법이 있지만 제가 선호하는 방법은 엑셀의 cell을 insert 문으로 변경해서 넣는 것을 선호합니다.

python의 openpyxl을 이용해서 엑셀을 sql 파일로 변경해 보겠습니다.

우선 엑셀 형태는 영어사전입니다. 단어가 있고, 한글 뜻, 영어 뜻 3개의 column으로 구성되어 있습니다.

openpyxl을 이용해 python 코드를 작성해 봅니다.

# -*- coding:utf-8 -*-
import openpyxl
import codecs

# 엑셀파일 열기
filename = "word-list-01.xlsm"
book = openpyxl.load_workbook(filename)

# 엑셀 파일의 첫번째 시트 추출하기
sheet = book.worksheets[0]

# sql 쿼리에 맞게 문자 변경
def replaceToQuery(text):
	if text == None:
		return ""
	else:
		text = text.replace("'", "''")
		text = text.replace("\n", "\\n")
		return text

# 저장할 sql 파일 오픈
sqlFile = codecs.open('word_insert.sql', 'w',  'utf-8')

# 시트의 각 행을 순서대로 추출하기
for row in sheet.rows:
	word	= replaceToQuery(row[0].value)
	means	= replaceToQuery(row[1].value)
	example	= replaceToQuery(row[2].value)
	
	# sql의 insert문으로 변경
	queryString = "insert into words (word, means, example) values ('%s', '%s', '%s');" % (word, means, example)

	# sql 파일에 쓰기
	sqlFile.write(queryString + '\n')

sqlFile.close()

sql파일이 생성되었다면 아래와 같이 sql파일을 실행시킬 수 있습니다.

psql의 f 옵션으로 sql파일 경로를 입력합니다.

$ psql -d tongchun -f word_insert_1.sql