[Python] SQLite 데이터베이스에 미리 manipulate 하기

영어사전 류의 앱을 만들 때는 미리 데이터를 정제하여 데이터베이스에 밀어 넣어 두는 것이 필요하다. 예를 들어 엑셀 파일이나 텍스트 파일등에 들어있는 데이터가 있다고 한다면 미리 sqlite 파일로 옮겨놓는 작업에 대해 잠깐 메모한다.

먼저 데이터 베이스 파일을 생성한다. 데이터베이스 파일의 생성은 firefox의 sqlmanager를 사용하여 GUI를 통해 생성할 수도 있다. 만약 필드 수가 많다면 일일이 코드를 타이핑하여 스크립트를 만드는 것보다 이를 사용하는 것이 훨씬 쉽다. 그렇지 않다면 다음과 같이 간단히 테이블 생성 쿼리를 보내 DB파일을 생성하는 스크립트를 실행해 준다.

#preparing DB
import sqlite3

filename = 'dictionary.sqlite'
db = sqlite3.connect(filename)
query = '''CREATE TABLE IF NOT EXISTS KRDIC (
ID INT PRIMARY KEY NOT NULL AUTOINCREMENT,
KEYWORD TEXT,
DESCRIPTION TEXT);'''
cursor = db.cursor()
cursor.excute(query)
db.commit()
db.close()

만약 원본 데이터가 엑셀에 있다면 엑셀을 사용해서 각 셀이 탭으로 구분된 텍스트 파일로 저장한다. SQLite에 넣으려면 기본적으로 UTF-8로 인코딩되어야 하는데, 엑셀이 유니코드를 지원하는지는 잘 모르겠다. 아마 CP949로 인코딩된 한글 텍스트 파일일 가능성이 크다. 따라서 스크립트 내에서 인코딩 문제는 처리해야 한다.

아래 스크립트의 주요 내용은 이러하다.

  1. 텍스트 파일을 열고 한 줄의 내용을 읽어들인다.
  2. 한 줄의 내용을 탭 문자로 분리하여 리스트(배열)로 만든다.
  3. KEY 값으로 주기 위해 number 타입 변수를 하나 생성한다.
  4. 각각의 줄에 대해서 KEY 및 각 필드의 값을 추출하여 리스트로 (혹은 튜플로) 만든다.
  5. 루프를 돌면서 한 줄씩 필드의 내용을 DB에 insert 한다
  6. 루프가 끝나면 DB와 파일을 각각 닫는다.
아래는 예제 코드. 마지막 부분의 if __name__ == “__main__”:은 파일을 바로 실행했을 때, 메인 함수처럼 동작하도록 하는 부분이다.
[python]
def fileInsert(sourceFilename = 'kordict.txt'):
db = sqlite3.connect('dictionary.sqlite')
cursor = db.cursor()
f = open(sourceFilename, 'r', encoding='cp949')
l = f.readline()
index = 0
while(l):
    lineItem =  [index,] + l.split('\t')
    recordList = []
    for i in lineItem:
        if (type(i)==type('a')):
            recordList.append(i.encode('utf-8'))
        else:
            recordList.append(i)

    cursor.execute('''INSERT INTO KRDICT VALUES (?,?,?)''', tuple(recordList[0:3]))
    index = index + 1
    try:
        l = f.leadline()
    except:
        print("error")

print("complete")

db.commit()
db.close()
f.close()

if __name__ == "__main__":
fileInsert() 
[/python]