python 기초 #8 : Database - SQLite

INTRO

DB Browser for SQLite 설치

DB Handling : CRUD(Create, Read, Update, Delete), Table 생성과 index, record INSERT

DB data는 직접 SQL(Structured Query Language)을 다루어야 하나 여기서는 python을 이용한다.

python의 pandas를 이용할 때 더욱 수월하며, 여기서는 맛보기만 배운다.

 

용어

SQLite

MySQL나 PostgreSQL와 같은 데이터베이스 관리 시스템(DBMS)이지만, 서버가 아니라 응용 프로그램에 넣어 사용하는 비교적 가벼운 데이터베이스이다. 일반적인 RDBMS에 비해 대규모 작업에는 적합하지 않지만, 중소 규모라면 속도에 손색이 없다.

또 API는 단순히 라이브러리를 호출하는 것만 있으며, 데이터를 저장하는 데 하나의 파일만을 사용하는 것이 특징이다. 

구글 안드로이드 운영 체제에 기본 탑재된 데이터베이스이기도 하다.

출처 : ko.wikipedia.org/wiki/SQLite

 

DBMS(DataBase Management System ,데이터베이스 관리 시스템)

다수의 사용자들이 데이터베이스 내의 데이터를 접근할 수 있도록 해주는 소프트웨어 도구의 집합이다. DBMS은 사용자 또는 다른 프로그램의 요구를 처리하고 적절히 응답하여 데이터를 사용할 수 있도록 해준다.

출처 : ko.wikipedia.org/wiki/데이터베이스_관리_시스템

출처 : https://wikidocs.net/5326

 

 

1. DB Browser for SQLite 설치

- Download : terminal brew install

  $ brew update

  $ brew search db-browser-for-sqlite

  $ brew install --cask db-browser-for-sqlite

  Download hompage : sqlitebrowser.org/dl/

실행화면 : DB browser for SQLite

 

DB file 만들기 : pyTest directory 내에 myDB directory를 만들어 test.db 파일을 생성한다.

# -*- coding: utf-8 -*-
import os
import sqlite3

#### path & db_file
base_dbpath = './myDB'
db_name = 'test.db'
con = sqlite3.connect(os.path.join(base_dbpath,db_name))
con.close()

DB browser 확인 : "데이터베이스" 열기를 이용하여 ~/pyTest/myDB/test.db 파일을 열어 확인

DB file open 화면 캡쳐

 

2. DB Handling

공공데이터포털의 "민방위 지자체담당자 연락처"를 다운받아 DB record로 사용한다.

DB의 table, index, value(record) 생성 및 입력을 sqlite3 module을 이용하여 다루어본다.

pandas를 이용한 DB 조회 내용을 간략하게 다루고 차후 추가 설명한다. python 활용 #1 : pandas - File Handle

 

python code : pyTest/test14.py

# -*- coding: utf-8 -*-
import os
from urllib.request import urlretrieve
import csv
import sqlite3

#### path & DB sample download
base_path, sub_dirs, fnames = next(os.walk(os.getcwd()))
base_dpath = './data'
down_sname = 'file3.csv'
base_dbpath = './myDB'
db_name = 'test.db'
if not os.path.exists(base_dbpath):
	os.makedirs(os.path.join(base_path,base_dbpath))
url = 'https://www.data.go.kr/cmm/cmm/fileDownload.do?atchFileId=FILE_000000001494440&fileDetailSn=1&insertDataPrcus=N'
urlretrieve(url, os.path.join(base_dpath,down_sname))


#### list from .csv file
with open(os.path.join(base_dpath,down_sname),'r',encoding='euc-kr') as f0:
	lines_csv = csv.reader(f0)
	table_lines = []
	for line_csv in lines_csv:
		table_lines.append(line_csv)


#### DB naming : SQL table, index, record
table_index = table_lines[0]
table_records = table_lines[1:11]
table_name = 'contactbook_CivilDefense'


#### sqlite3 handle : table, index, record, commit
con = sqlite3.connect(os.path.join(base_dbpath,db_name))
cur = con.cursor()
cur.execute("DROP TABLE IF EXISTS contactbook_CivilDefense")
cur.execute("CREATE TABLE contactbook_CivilDefense(ARCD1,ARCD2,ARCD3,DEPT_NM,TELNO)")
cur.execute("CREATE INDEX index_part ON contactbook_CivilDefense(ARCD1,ARCD2,ARCD3,DEPT_NM,TELNO)")
cur.executemany("INSERT INTO contactbook_CivilDefense VALUES(?,?,?,?,?);",(table_records))
con.commit()
con.close()


#### DB SELECT
con = sqlite3.connect(os.path.join(base_dbpath,db_name))
cur = con.execute("SELECT * FROM contactbook_CivilDefense")
print('DB 내용 조회\n')
for row in cur.fetchall():
    print(row)
con.close()

#### DB SELECT using pandas
import pandas as pd
con = sqlite3.connect(os.path.join(base_dbpath,db_name))
print('\nDB 내용 조회 : pandas')
print('3rd party modyle pandas pip install 설치 후 사용\n')
df = pd.read_sql_query("SELECT * FROM contactbook_CivilDefense",con)
print(df)
con.close()

 

---- SublimeText python Run test14.py ----

DB 내용 조회

('서울특별시', '종로구', '가회동', '민방위담당', '02-2148-5214')
('서울특별시', '종로구', '숭인2동', '민방위담당', '02-2148-5514')
('서울특별시', '종로구', '창신1동', '민방위담당', '02-2148-5392')
('서울특별시', '종로구', '창신3동', '민방위담당', '02-2148-5459')
('서울특별시', '종로구', '무악동', '민방위담당', '02-2148-5157')
('서울특별시', '종로구', '삼청동', '민방위담당', '02-2148-5064')
('서울특별시', '종로구', '이화동', '민방위담당', '02-2148-5302')
('서울특별시', '종로구', '혜화동', '민방위담당', '02-2148-5334')
('서울특별시', '종로구', '교남동', '민방위담당', '02-2148-5185')
('서울특별시', '종로구', '평창동', '민방위담당', '02-2148-5123')

DB 내용 조회 : pandas
3rd party modyle pandas pip install 설치 후 사용

   ARCD1 ARCD2 ARCD3 DEPT_NM         TELNO
0  서울특별시   종로구   가회동   민방위담당  02-2148-5214
1  서울특별시   종로구  숭인2동   민방위담당  02-2148-5514
2  서울특별시   종로구  창신1동   민방위담당  02-2148-5392
3  서울특별시   종로구  창신3동   민방위담당  02-2148-5459
4  서울특별시   종로구   무악동   민방위담당  02-2148-5157
5  서울특별시   종로구   삼청동   민방위담당  02-2148-5064
6  서울특별시   종로구   이화동   민방위담당  02-2148-5302
7  서울특별시   종로구   혜화동   민방위담당  02-2148-5334
8  서울특별시   종로구   교남동   민방위담당  02-2148-5185
9  서울특별시   종로구   평창동   민방위담당  02-2148-5123

***Repl Closed***

 

---- ./pyTest tree structure ----

.

├── data

│   ├── file1.csv

│   ├── file2.csv

│   └── file3.csv

├── lecture_basic_1to5

│   ├── example_import.py

│   ├── test0.py

│   ├── test1.py

│   ├── test10.py

│   ├── test2.py

│   ├── test3.py

│   ├── test4.py

│   ├── test5.py

│   ├── test6.py

│   ├── test7.py

│   ├── test8.py

│   └── test9.py

├── lecture_basic_6to10

├── myClass

│   ├── __pycache__

│   │   └── exClass1.cpython-39.pyc

│   └── exClass1.py

├── myDB

│   └── test.db

├── myfun

│   ├── __pycache__

│   │   └── mytestfun1.cpython-39.pyc

│   └── mytestfun1.py

├── py_csv.csv

├── py_intro.txt

├── py_text1.txt

├── py_text2.txt

├── py_wb_text.xlsx

├── ref11.py

├── test11.py

├── test12.py

├── test13.py

├── test14.py

├── text_csv.csv

└── text_csv.txt