프로젝트03. 도서 관리 프로그램 만들기2-sqlite3

앞선 포스팅에서 고민한 것과 같이 데이터를 효율적으로 관리하기 위해선

DB를 구성하는게 좋다.

 

그리고 그중 우리가 다뤄볼 DBMS는 sqlite3이다.

sqlite3의 장점은 python에 내장된 모듈이기 때문에 별도의 설치가 없어도 사용이 가능하다.

 

오늘 포스팅은 SQL의 DDL문을 통해 책을 관리할 기본 테이블을 작성하고 데이터를 넣어보는 포스팅이다.

 

1. DB 생성하기

# main_db.py

import sqlite3


# DB생성 및 Auto Commit, RollBack
conn = sqlite3.connect(r'.\books_manager_database.db', isolation_level=None) # 해당 경로에 books_manager_database.db DB파일이 생성된다.

# 커서(Cursor)의 이용
global cursor
cursor = conn.cursor()

 

main_db.py 로 전체 DB를 생성하고(books_manager_database.db) 해당 DB에 우리가 필요한 세부 테이블을 생성한다.

실제로 위의 코드 몇 줄 만으로 DB를 생성하고 관리할 수 있는데,

main_db.py를 여러번 실행하더라도 books_manager_database.db 파일이 중복으로 생성된다거나 덮어쓰지(overwirte) 않는다.

 

그래서 해당 파일이 있는지 없는지 확인을 하는 코드가 추가될 필요는 없고,

해당 .db 파일이 없으면 신규 생성, 있으면 해당 파일에 있는 내용을 읽어 쓰기 위해 참조한다고 생각하면 된다.

 

isolation_level 의 경우, SQL의 commit과 관련된 부분이다.

SQL DML문(select, insert, update, delete) 중, select 를 제외한 나머지 명령어들은 실제 자료를 추가(insert), 수정(update), 삭제(delete) 하는 명령어이다. 

그런데, DB는 해당 명령어를 입력 받아도 바로 추가, 수정 또는 삭제를 하지 않는다.

commit 이라는 명령어를 통해 변경 사항에 대한 확인 작업을 해주어야만 실제 DB에 변경 사항을 반영하고,

commit 을 실행하기 전 rollback 을 실행하면 변경 사항을 원래대로 돌려놓는다.

 

그래서 commit의 경우, 실제 실행을 반영하는 확인 명령어라고 생각할 수 있는데,

잘못된 변경사항을 돌릴 수 있다는 점에서 commit은 유효하지만,

프로그램으로 접근할 때에는 불필요한 과정이 될 수 있다.

위와 같이 sqlite에서 isolation_level = None 으로 설정할 경우,

우리가 실행하고자 하는 DML문에 commit이 포함되어 추가로 commit을 하지 않아도

insert나 update, delete를 실행하면 실제 DB에 변경사항이 반영된다.

 

그리고 이런 DML문을 좀 더 효과적으로 사용하기 위해 전역변수로 cursor를 선언하고,

해당 cursor를 통해 DML문을 직접 실행하게 된다.

반응형

 

 

2. 테이블 생성하기 

 

테이블은 이름에서 보듯이 표의 형식을 가지고 있는데,

이해도를 높히기 위해 생성하려는 테이블을 엑셀로 만들어보았다.

 

테이블의 가로 열을 컬럼(column), 세로 행을 로우(row)라고 부른다.

 

대충 이런 형태가 될텐데, book_key 값은 해당 테이블을 이루는 가장 기본이 되는 PK(Primary Key)가 되며, 

연속된 값을 자동으로 생성할 수 있도록 연속적인 시퀀스 값을 할당할 예정이다.

이런 연속적인 값을 생성하는 기능을 oracle에서는 sequences라고 부르지만, 

sqlite3에서는 autoincrement 라는 옵션으로 사용 가능하다.

 

해당 DB를 만들려면 아래의 코드를 실행하면된다.

# 도서 기준 기본 테이블
cursor.execute('CREATE TABLE IF NOT EXISTS books_info(book_key integer primary key autoincrement , group_code integer, book_title text,\
    writer text, publisher text, total_count integer, available_count integer, lacation text )')

 

몇 가지 DB에 대한 추가 설명을 더하면, books_info 테이블이 없으면 해당 컬럼명과 조건으로 테이블을 만드는 코드이다.

 - 위에서 설정한 cursor를 실행하기 위해 cursor.execute 를 사용하고,

 - books_info 테이블을 확인 후 없을 때 해당 테이블을 생성한다.(CREATE TABLE IF NOT EXISTS 테이블명[books_info])

 - 이후 (컬럼명 타입 조건)을 선언해주는데,

   타입의 경우, TEXT(문자), INTEGER(정수, 최대 8바이트), REAL(부동 소수점 8바이트), BLOB(Binary Large OBject / 원본 데이터), NULL 을 지원한다.

   추가 조건으로 입력된 primary key 는 해당 값이 빈 칸을 입력받지 않는 값으로

   autoincrement를 이용해 우리가 값을 따로 지정하지 않아도 데이터가 추가되면 자동으로 증가하는 값이 된다.

 

해당 코드를 통해 전체적인 도서 관리를 위한 테이블을 생성했다면,

이제 각각의 도서 1권마다 상태를 표시할 수 있는 data를 정리할 필요가 있다.

# 도서 기준 세부 테이블(도서 개별)
cursor.execute('CREATE TABLE IF NOT EXISTS book_detail_info(book_detail_key integer primary key autoincrement , books_key integer, user_key integer, rent_stat text,\
    rent_start_date timestamp, rent_expire_date timestamp)')

books_info 기본 테이블을 만들었던 방법처럼 book_detail 테이블을 만들고 

기본 값이 될 book_detail_key 컬럼과 books_info의 전체 도서와 연계할 boos_key 컬럼

대여 시 회원 정보를 입력할 user_key 컬럼, 대여 상태를 확인하기 위한 rent_stat 컬럼,

대여 시작일과 종료일을 나타내는 rent_start_date, rent_expire_date 컬럼을 생성한다.

 

 

해당 내용 까지 입력한 뒤, 코드를 실행해보면 우리가 지정한 이름으로 books_manager_database.db 파일이 생성된다.

 

---

전체 코드

# main_db.py
import sqlite3


# DB생성 및 Auto Commit, RollBack
conn = sqlite3.connect(r'.\book_manager_database.db', isolation_level=None) # 해당 경로에 book_manager_database.db DB파일이 생성된다.

# 커서(Cursor)의 이용
cursor = conn.cursor()




######################################################################
# 테이블 생성(Data Type : TEXT, NUMERIC INTEGER, REAL, BLOB)



# 도서 기준 기본 테이블
cursor.execute('CREATE TABLE IF NOT EXISTS books_info(book_key integer primary key autoincrement , group_code integer, book_title text,\
    writer text, publisher text, total_count integer, available_count integer, lacation text )')


# 도서 기준 세부 테이블(도서 개별)
cursor.execute('CREATE TABLE IF NOT EXISTS book_detail_info(book_detail_key integer primary key autoincrement , books_key integer, user_key integer, rent_stat text,\
    rent_start_date timestamp, rent_expire_date timestamp)')

 

이제 다음 포스팅을 통해 해당 DB의 테이블 내역을 조회 하는 방법을 알아보겠다.