파이썬 sqlite3 db 활용 대용량 데이터 관리가 가능할까

용량 큰 다량의 데이터를 파이썬 sqlite3 db에서 관리해보기 위해 db와 테이블을 생성하고 데이터를 insert 한 결과를 올려본다.

 

 

용량 큰 다량의 데이터를 파이썬 sqlite3 db에 insert 하기

지난 5월에 250개 파일(시군구 단위)로 된 특정 데이터를 MS Access db(mdb)에 insert 하여 관리하려고 하였으나 4백만 건이 넘어가면서 에러가 계속 발생하여 포기했었다. 

이번에 개인적인 활용 목적으로 파이썬에서 코드를 짜보고 장고를 활용한 웹 환경하에서의 사용 가능성을 테스트해보기 위해 sqlite3 db에서 작업을 해보았다.

그러나 pandas DataFrame to sql이나 csv to json 등을 활용한 데이터 insert 방법에서 여러 에러가 발생했고, 이 문제를 구글링 해가며 하나하나 적용을 했지만 원본 데이터의 포맷 문제인지 데이터 분류 상의 type 문제인지 아니면 초보라서 그런지 번번이 실패를 했다.
그러다가 아주 기본적인 방법으로 해보자는 생각에 그냥 sql 쿼리문을 활용한 방식으로 꼼수를 좀 써가며 에러 부분은 skip 시키면서 여러 번에 걸친 시도 끝에 sqlite3 insert 진행 결과 성공을 했다.

전체 입력 건수는 5,694,240건, 용량은 1.4GB였다. 
개인용으로 쓰는 db 치고는 너무 크다.
이제 이 부분을 조작을 통해 필요한 데이터를 뽑아내는 방법과 장고 웹 환경에서 CRUD 구현을 하고 잘 동작하는지 테스트를 해봐야 한다.

 

[파이썬 소스 코드]

import sqlite3
import os
import csv


conn = sqlite3.connect('test_sqlites.db')
cursor = conn.cursor()

## table 생성 ##
# cursor.execute('''
# CREATE TABLE bldageinfo (id integer NOT NULL PRIMARY KEY AUTOINCREMENT, 
# gisbldno float, 
# uniqno float, 
# lotcode float, 
# lotname string, 
# spcareacode string, 
# spcareaname string, 
# lotno string, 
# bldno string, 
# blddistguicode string, 
# blddistgui string, 
# daejancode string, 
# daejantype string, 
# bldname string, 
# blddongname string, 
# bldsquare float, 
# bldstruccode string, 
# bldstrucname string, 
# usecode string, 
# usename string, 
# bldheight float, 
# bldflonum integer, 
# bldundernum integer, 
# permidate date, 
# useaprodate date, 
# bldage string, 
# agegroupcode string, 
# agegroupname string, 
# agegroup5code string, 
# agegroup5name string, 
# datastdate date)
# ''')
# print('테이블 생성 완료~')


cnt = 1
for file in os.listdir('./bldinfo/buildinginfo_file'):
    print(file)  # AL_11305_D197_20220117.csv

    with open('./bldinfo/buildinginfo_file/' + file, 'r', encoding='cp949') as csv_f:
        next(csv_f)
        csv_reader = csv.reader(csv_f)
        # print(list(csv_reader))
        for row in csv_reader:
            res = ''.join("'" + str(i) + "'," for i in row)[:-1]
            # print(res[:-1])
            try:
                q = f'INSERT INTO bldageinfo (gisbldno, uniqno, lotcode, lotname, spcareacode, spcareaname, lotno, bldno, blddistguicode, blddistgui, daejancode, daejantype, bldname, blddongname, bldsquare, bldstruccode, bldstrucname, usecode, usename, bldheight, bldflonum, bldundernum, permidate, useaprodate, bldage, agegroupcode, agegroupname, agegroup5code, agegroup5name, datastdate) VALUES ({res})'
                print(q)
                cursor.execute(q)
            except:
                cnt += 1
                pass
        conn.commit()
            # break
    # break
print(f'Error Count: {cnt}')

cursor.close()
conn.close()

sqlite3는 sqlite3.connect('test_sqlites.db')로 db가 생성됨 (test_sqlites.db 파일이 없는 경우)

주석 처리된 CREATE TABLE bldageinfo (id integer NOT NULL PRIMARY KEY AUTOINCREMENT,... 부분이 테이블 생성 구문이고,

for문 for file in os.listdir('./bldinfo/buildinginfo_file'):로 해당 데이터 csv 파일이 있는 폴더에서 파일 목록을 읽어 리스트로 만든 후 순환시킨다. (아래 이미지처럼 폴더에 파일 250개 있음)

 

• next(csv_f) 코드는 각 파일의 첫 번째 행인 제목 부분을 제외시키시는 것임 (아래 내용)
• 그리고, 이 데이터 파일은 인코딩을 encoding='cp949'으로 해야 오류가 안남 (문제는 이 부분이 DataFrame에서 NaN으로 된다는 점 때문에 pandas 데이터로 insert를 못했음)
• res = ''.join("'" + str(i) + "'," for i in row)[:-1] 부분은 꼼수임. csv_reader()로 반환된 객체를 for문으로 각 row를 가져와서 insert 시킬 행 데이터로 만들기 위해 '문자데이터', '문자데이터',... 포맷으로 만드는 것임
• conn.commit() 부분 때문에 고생을 했는데, sqlite3에서는 cursor 객체로 커밋을 하면 안되고 conn으로 해야 정상적으로 commit 되었음
• 그리고, 실행 중간에 역시나 파일별로 몇건씩 오류가 발생을 했기에 예외처리를 하고 전체 파일에서 몇 건이나 오류가 나는지 체크해보니 744건이었음

 

 

[실행 결과]

최종 실행 결과로 에러 744건 발생