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

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

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

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

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
# -*- 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()
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

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

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

1
$ psql -d tongchun -f word_insert_1.sql
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX