[Python] 20. [DBMS] 데이터베이스 개론, SQLite3 사용

01. 데이터베이스시스템 개론(DBMS)

- 우리가 일상 업무나 생활에서 발생하는 데이터를 컴퓨터 기반의 데이터 

저장소를 만들고 저장, 조회, 수정, 삭제, 통계를 제공하는 컴퓨터 시스템(SW).

 

1. 데이터를 저장하기위한 테이블(엔티티)의 구조

   - 데이터베이스의 구성요소이며 하나 이상이 존재 할 수 있습니다.

   - 2차원 배열의 표 형태를 가지고 있습니다.

   - Record는 Column의 집합입니다.

   - Table(entity)은 Record의 집합입니다.

   - Database는 계정과 테이블의 집합입니다.

   - 엑셀과 같은 구조임으로 엑셀을 사용하는 기업은 쉽게 정보화를 추진할 수
     있습니다.
   - 하나의 Excel 파일은 하나의 Database 파일과 대응합니다. 엑셀파일안의
     sheet는 하나의 table과 대응합니다.

no name   wdate       subject      passwd <- 컬럼, 열
   -----------------------------------------------------------------
   1  가길동 2005-01-01  안녕하세요.  123    <-- 레코드, 행
   2  나길순 2005-01-02  반갑습니다.  123$
   3  다길순 2005-01-01  안녕하세요.  123
   4  라길동 2005-01-02  반갑습니다.  123$
   5  마길동 2005-01-01  안녕하세요.  123
   6  바길순 2005-12-02  반갑습니다.  123$
                                       ↖
                                        엑셀 Sheet  
                                        테이블 -> 데이터베이스 -> 빅데이터 -> 예측 시스템 -> 인공지능
                                        회원        (기업)               (글로벌 기업, 국가 단위) 
                                        게시판 
                                        자료실
                                        갤러리
                                        MP4 플레이어
                                        설문조사 
                                        급여관리
                                        자재관리
                                        입고관리
                                        출고관리
                                        매출관리
                                        리조트 
                                        예약 
                                        펜션 
                                        상품...

 

02. SQLite3 설치

- 메모리 및 파일 기반의 임베디드 데이터 베이스.

- 데이터베이스 서버를 독립적으로 구성하지않고 application과 함께 배포함.

- 안드로이드, IPhone등에서도 바로 사용 할 수있는 모듈 지원.

 

1. https://www.sqlite.org

   

2. Precompiled Binaries for Windows 

sqlite-tools-win32-x86-3180000.zip 다운로드

  

3.  모듈함수

– sqlite3.connect(database[, timeout, detect_types, isolation_level, check_same_thread, – factory, cached_statements]) : SQLite3 DB와 연결된 Connection 객체반환

– sqlite3.complete_statement(sql) : 세미콜론으로 끝나는 sql 문에 대해 True를 반환(구문확인은 안함)

    

4. Connection 클래스 : 연결된 DB응 동작

– cursor([cursorClass]) : cursor 객체를 생성

– commit() : 트렌젝션의 변경내용을 DB에 반영

– rollback() : 트렌젝션의 변경내용을 DB에 반영하지 않음

– close() : DB 연결을 종료

– isolation_level : 트렌젝션 경리수준을 확인, 설정

– execute(sql[, parameters]) : 임시 cursor 객체에 sql 문을 실행

– executemany(sql[, parameters]) : 임시 cursor 객체에 동일한 sql 문에 파라미터를 변경하여 실행

– executescript(sql_script) : 임시 cursor 객체에 세미콜론으로 구분된 여러줄의 sql 문을 실행

– create_aggregate(name, num_params, aggregate_class) : 사용자 정의 집계함수를 생성

– create_collation(name, callable) : 문자열 정렬시 sql 에서 사용하될 이름(name)과 정렬함수를 지정

– iterdump() : 연결된 DB의 내용을 sQL 질의 현태로 출력할수 있는 이터레이터를 반환

 

5. Cursor 클래스 : 실제적으로 DB의 sql 구문을 실행시키고 조회된 결과를 가져옴

– execute(sql[, parameters]) : cursor 객체에 sql 문을 실행

– executemany(sql[, parameters]) : cursor 객체에 동일한 sql 문에 파라미터를 변경하여 실행

– executescript(sql_script) : cursor 객체에 세미콜론으로 구분된 여러줄의 sql 문을 실행

– fetchone() : 조회된 결과로 부터 데이터 1개를 반환

– fetchmany(size=cursor.arraysize) : 조회된 결과로 부터 입력받은 size 만큼의 데이터 리스트를 반환

– fetchall() : 조회된 결과 모두를 리스트형태로 반환

6. Row 클래스 : 조회된 결과 집합에서 튜플을 나타냄

7. 데이터 타입

1) 내부에 저장되는 타입

   NULL: 값이 NULL인 경우. 

   INTEGER: 부호 있는 정수. 상황에 따라 1, 2, 3, 4, 6, 8 바이트가 됨. 

   REAL: 부동소수점. IEEE 부동소수 규격에 따른 8바이트. 

   TEXT: 문자(열). 기본적으로 UTF-8으로 저장되며 UTF-16 BE나 UTF-16 LE를 사용할 수 있음. 

   BLOB: 이진 데이터. 외부에서 전달된 데이터를 그대로 저장됨.
 
2) 테이블 생성시 명시되는 타입
- http://www.sqlite.org/datatype3.html

 

8. PK 컬럼의 자동 생성
- 1부터 자동증가하여 레코드를 고유하게 구분하는 PK 컬럼의 값으로 사용됨.
/Home 폴더/sqlite3>sqlite3.exe test.db  ← 테이블 생성시 test.db 파일이 없으면 생성됨.

CREATE TABLE COMPANY(

   ID INTEGER PRIMARY KEY   AUTOINCREMENT,
   NAME           TEXT      NOT NULL,
   AGE              INT       NOT NULL,
   ADDRESS       CHAR(50) ,
   SALARY         REAL
);
 
INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'Paul', 32, 'California', 20000.00 ) ;
 
INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ('Allen', 25, 'Texas', 15000.00 ) ;
 
INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ('Teddy', 23, 'Norway', 20000.00 ) ;
 
 
SELECT ID, NAME,AGE,ADDRESS,SALARY
FROM COMPANY
ORDER BY ID ASC;
 
1|Paul|32|California|20000.0
2|Allen|25|Texas|15000.0
3|Teddy|23|Norway|20000.0

 

03. Sqlite3 Eclipse 연동 설정
1. Client tool 설치
- DB Browser for SQLite 3.9.1 은 한글 깨짐(비권장)
  
2. Sqlite JDBC library 다운로드
- https://bitbucket.org/xerial/sqlite-jdbc/downloads/
  sqlite-jdbc-3.16.1.jar 다운로드
 
3. Eclipse 설정 
1) View 열기

2) Data Source Explorer 열기
3) New Connection Profile 생성
4) SQlite 선택 Name 값은 파일명등 입력
5) 드라이버 신규 등록 

* 기존 jar 경로 변경시 'Edit Driver Definition' 선택하여 변경 등록
6) 데이터베이스 드라이버 종류 지정
7) 기존 jar 파일 삭제후 아래 화면처럼 다운로드받은 jar 등록
8) 'test.db' 파일명 집적 입력, [Test Connection] 버튼 클릭하여 'Ping Succeed' 출력 확인
9) sql 파일의 생성, 확장가SQL, DDL 이어야함.
10. 실행할 SQL 블럭 지정후 Popup 메뉴에서 'Execute Selected Text' 선택
11) SQL 실행 결과의 확인

4. SQL 실습

▷ /Home/ws_python/oop/sqlite3/test.sql
 -------------------------------------------------------------------------------------
1) 테이블 생성
CREATE TABLE `phonebook` (
`name` INTEGER NOT NULL,
`phone` TEXT NOT NULL,
`email` TEXT NOT NULL,
PRIMARY KEY(`email`)
);
 
2) INSERT
INSERT INTO phonebook(name, phone, email)
VALUES('가길동', '000-111-1111', 'mail1@mail.com');
 
INSERT INTO phonebook(name, phone, email)
VALUES('나길동', '000-111-1111', 'mail2@mail.com');

INSERT INTO phonebook(name, phone, email)
VALUES('다길순', '000-111-1111', 'mail3@mail.com');
  
3) SELECT
SELECT name, phone, email
FROM phonebook
ORDER BY name ASC;

 
SELECT name, phone, email
FROM phonebook
WHERE name="가길동";

SELECT name, phone, email
FROM phonebook
LIMIT 2 OFFSET 1
  
4) UPDATE
UPDATE phonebook 
SET name = '왕눈이'
WHERE email='mail1@mail.com';
  
5) DELETE
DELETE FROM phonebook 
WHERE email='mail1@mail.com';
 
6) 테이블 삭제
DROP TABLE phonebook;
-------------------------------------------------------------------------------------

 

5. id값의 생성과 사용

▷ /Home/ws_python/oop/sqlite3/test_id.sql
-------------------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS movie(
  movie_id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT,
  country  TEXT
)
 
CREATE TABLE IF NOT EXISTS actor(
  actor_id INTEGER PRIMARY KEY AUTOINCREMENT,
  movie_id INTEGER,
  name TEXT,
  gender  TEXT,
  FOREIGN KEY(movie_id) REFERENCES movie(movie_id)
)
 
INSERT INTO movie(movie_id, name, country)
VALUES(NULL, '월터의 상상은 현실이된다.', '미국');
 
SELECT movie_id, name, country 
FROM movie
ORDER BY movie_id ASC;
 
 movie_id name           country
 -------- -------------- -------
        1 월터의 상상은 현실이된다. 미국
        
        
INSERT INTO actor(movie_id, name, gender)
VALUES(1, '벤스틸러', '남자');
 
INSERT INTO actor(movie_id, name, gender)
VALUES(2, '크리스튼 위그', '여자');
 
SELECT actor_id, movie_id, name, gender
FROM actor
ORDER BY actor_id ASC;        
 
 actor_id movie_id name    gender
 -------- -------- ------- ------
        1        1 벤스틸러    남자
        2        2 크리스튼 위그 여자  ← Foreigh Key가 정상 작동하지 않음.
        
 
DELETE FROM movie 
WHERE movie_id=1;    ← Foreigh Key가 정상 작동하지 않음.
 
-- 부모 연결 없는 자식 레코드 발생.
SELECT actor_id, movie_id, name, gender
FROM actor
ORDER BY actor_id ASC;              
 

-- sequence 실습
SELECT movie_id, name, country 
FROM movie
ORDER BY movie_id ASC;
 
-- 한번 생성된 일련번호는 초기 상태로 갈 수 없음. 증가만 됨.
INSERT INTO movie(movie_id, name, country)
VALUES(NULL, '인턴', '미국');
 
SELECT movie_id, name, country 
FROM movie
ORDER BY movie_id ASC;
 
 movie_id name country
 -------- ---- -------
        2 인턴   미국
        
 
-------------------------------------------------------------------------------------