SQLite3에 대한 이점 및 사용 예제

파이썬에 기본으로 탑재되어 있는 SQLite3 에 대해서 다룬다.

SQLite는 가벼운 디스크 기반의 데이터 베이스를 제공하는 C 라이브러리다.
어플리케이션 내부 데이터 저장소로 다양하게 활용되고, 사용이 간편하고 가벼워서 프로토타입용으로도 자주 쓰인다.

본문은 pandas + SQLite3에 대한 간략한 사용 예제를 담는다.

1. CSV 자료를 판다곰의 데이터프레임(DataFrame)으로 읽는다.

df = pd.read_csv('gov_loc.csv',skiprows=1,index_col=['lev1','lev2','lev3'],
                 names=['lev1','lev2','lev3','nx','ny','lon','lat'])

* 샘플 데이터는 기상청 주소/좌표 매핑 데이터를 이용했다.

lev1	lev2	lev3	nx	ny	lon	lat
서울특별시	None	None	60	127	126.980008	37.563569
서울특별시	종로구	None	60	127	126.981642	37.570378
서울특별시	종로구	청운효자동	60	127	126.970652	37.584137
서울특별시	종로구	사직동	60	127	126.970956	37.573269
서울특별시	종로구	삼청동	60	127	126.983978	37.582425
서울특별시	종로구	부암동	60	127	126.966444	37.589856

...

2. 데이터프레임을 SQLite DB로 저장한다.

conn = sqlite3.connect('gov.db')
df.to_sql(name='loc_map_book',con=conn,index=True,if_exists='replace')

3. 메타 데이터를 이용해 스키마를 확인 하자.

query_ddl = 'SELECT sql FROM sqlite_master WHERE tbl_name = "loc_map_book";'
for i in pd.read_sql(query_ddl ,con=conn).get_values():
    print (i[0])
OUT>>
CREATE TABLE "loc_map_book" (
"lev1" TEXT,
  "lev2" TEXT,
  "lev3" TEXT,
  "nx" INTEGER,
  "ny" INTEGER,
  "lon" REAL,
  "lat" REAL
)
CREATE INDEX "ix_loc_map_book_lev1_lev2_lev3"ON "loc_map_book" ("lev1","lev2","lev3")

4. 쿼리 플랜을 통해서 인덱스가 잘 동작하는지 알아 보자.

query = 'explain query plan ' + \
        'select * from loc_map_book where lev1="{0}";'.format('서울특별시')
for i in conn.execute(query):
    print(i)
OUT>>
(0, 0, 0, 'SEARCH TABLE loc_map_book USING INDEX ix_loc_map_book_lev1_lev2_lev3 (lev1=?)')


DB 업무를 먼저 시작한 이유로, 사소한 데이터도 대용량 DB와 연결을 지었는데,
로컬 파일 DB를 활용하는 것은 개발에 매우 큰 이점이 있었다.
단일 파일로 관리 됨으로 프로그램 복제가 쉬웠고, DB 구성/관리 소요비용이 없었다.

참조:
파이썬에서 대용량 자료를 핸들링 하는 방법에 대한 글
 - SQLite가 생각보다 큰 자료에 대한 핸들링도 가능한 것으로 보인다.
https://www.reddit.com/r/Python/comments/3wa22v/120gb_csv_is_this_something_i_can_handle_in_python/