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

앞선 포스팅에서 DB와 테이블을 생성했다면, 이번에는 추가와 검색을 해보도록 하겠다.

앞선 엑셀의 예시 자료를 커서를 이용해 입력할 예정인데,

입력하는 과정은

 

1. 동일 도서 등록 확인

 books_info 테이블에 제목을 검색 후, 제목과 출판사가 같은 등록 현황이 있는지 확인한다.

 아래의 매서드로 도서명과 출판사를 파라미터로 받고, 

 결과 값이 없을 때는 (0, 0, 0)을,

 결과 값이 있을 때는 (book_key, total_count, available_count)를 튜플로 전달 받고 반환한다.

# 동일 도서 등록 여부 확인
def check_books_info_by_title_publisher(book_title, publisher):

    cursor.execute(f'SELECT BOOK_KEY, TOTAL_COUNT, AVAILABLE_COUNT FROM BOOKS_INFO WHERE BOOK_TITLE = \'{book_title}\' AND PUBLISHER = \'{publisher}\'')
    select_check_book_result = cursor.fetchall()
    
    
    if len(select_check_book_result) == 0:
        check_book_result = (0, 0, 0)
    else:
        check_book_result = select_check_book_result[0]
    #print(check_book_result)

    return check_book_result

 

 여기서 반환된 값을 통해 등록이 된 도서라면 total_count와 available_cout 를 증가(update) 하고,

미등록 도서라면 신규로 등록(insert)한다.

 

2. 도서 등록 쿼리 실행

# 도서 등록 쿼리
def registe_new_book(group_code, book_title, writer, publisher, add_count, location):
    returned_check_book_result = check_books_info_by_title_publisher(book_title, publisher)

    # 동일 도서가 등록이 되어 있을 경우, returned_check_book_result 값이 books_info 테이블의 (book_key, total_count, available_cout) 값이 튜플로 반환됨
    # 미등록 되어 있을 경우, books_info 테이블에 해당 도서 등록 후, book_detail_info 등록
    if returned_check_book_result[0] == 0:
        cursor.execute("insert into books_info (group_code, book_title, writer, publisher, total_count, available_count, lacation) values('{}','{}','{}','{}','{}','{}','{}')".format(group_code, book_title, writer, publisher, add_count, add_count, location))

    else:
        # books_info 수량 변경
        cursor.execute(f'UPDATE BOOKS_INFO SET TOTAL_COUNT = \'{int(returned_check_book_result[1])+ int(add_count)}\', AVAILABLE_COUNT = \'{int(returned_check_book_result[2])+ int(add_count)}\' \
            where book_key = \'{returned_check_book_result[0]}\'')
    
    returned_check_book_result = check_books_info_by_title_publisher(book_title, publisher) # 신규 등록 시 book_key 값이 0인 상태로 book_detail_key 가 입력되어 1번더 실행

    # book_detail_info 등록
    for a_count in range(0, int(add_count)):
        cursor.execute("insert into book_detail_info (books_key, user_key, rent_stat, rent_start_date, rent_expire_date) values('{}','{}','{}','{}','{}')".format(returned_check_book_result[0], '', '', '', ''))

        print('detail 생성')

반응형

3. book_detail_info 추가

 이후, book_detail_info에 추가된 도서들을 개별로 추가하면 등록 과정은 끝이난다.

  - 위 코드 for 문 : [for a_count in range(0, int(add_count)): ]

 

4. 등록 현황을 확인할 수 있는 쿼리를 하나더 추가해보겠다.

# 등록 도서 확인1(books_info)
def select_books_info_all_view():
    cursor.execute('select * from books_info order by 1')
    view_list = cursor.fetchall()
    print(view_list)


# 등록 도서 확인2(book_detail_info)
def select_book_detail_info_all_view():
    cursor.execute('select * from book_detail_info order by 1')
    view_list = cursor.fetchall()
    print(view_list)

 

 

아래의 전체 코드를 실행해보면

 

정상적으로 db에 data가 쌓이는 것을 볼 수 있는데,

처음 신규 생성시에는 books_info 와 book_detail_info 테이블의 조회 내역이 아무 것도 없었으나,

4-1)과 4-2)를 진행하며 books_info 에는 2건, book_detail_info 에는 4건의 데이터가 생겼다.

우리가 2개의 테이블을 book_key 값을 통해 연동을 했는데,

처음 books_info 에 데이터를 생성할 때, 자동으로 생성된 book_key 값이

book_detail_info 테이블에 데이터를 등록하며, 2번째 요소에 생성되는 것을 볼 수 있다.

(빨간색은 2번째 요소가 1, 초록색은 2번째 요소가 2)

 

DB에 대해 약간의 이해도가 있다면,

book_detail_info 의 1번째 요소(book_detail_key) 값을 통해 각각의 책을 구별할 수 있고,

해당 책을 대여하게 되면, 2번째 요소(book_key) 값을 통해 books_info 테이블의 available_count를 수정해주면 

해당 도서가 도서관에 몇 권이 남아있는지 확인할 수 있을 것이다.

 

5.전체 코드

# main_db.py
import sqlite3


# DB생성 및 Auto Commit, RollBack
conn = sqlite3.connect(r'.\books_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)')


#########################################################################

# 동일 도서 등록 여부 확인
def check_books_info_by_title_publisher(book_title, publisher):

    cursor.execute(f'SELECT BOOK_KEY, TOTAL_COUNT, AVAILABLE_COUNT FROM BOOKS_INFO WHERE BOOK_TITLE = \'{book_title}\' AND PUBLISHER = \'{publisher}\'')
    select_check_book_result = cursor.fetchall()
    
    
    if len(select_check_book_result) == 0:
        check_book_result = (0, 0, 0)
    else:
        check_book_result = select_check_book_result[0]
    #print(check_book_result)

    return check_book_result

# cursor.execute(f'SELECT BOOK_KEY FROM BOOKS_INFO')

# 도서 등록 쿼리
def registe_new_book(group_code, book_title, writer, publisher, add_count, location):
    returned_check_book_result = check_books_info_by_title_publisher(book_title, publisher)

    # 동일 도서가 등록이 되어 있을 경우, returned_check_book_result 값이 books_info 테이블의 (book_key, total_count, available_cout) 값이 튜플로 반환됨
    # 미등록 되어 있을 경우, books_info 테이블에 해당 도서 등록 후, book_detail_info 등록
    if returned_check_book_result[0] == 0:
        cursor.execute("insert into books_info (group_code, book_title, writer, publisher, total_count, available_count, lacation) values('{}','{}','{}','{}','{}','{}','{}')".format(group_code, book_title, writer, publisher, add_count, add_count, location))

        # 신규 등록 시 book_key 값이 0인 상태로 book_detail_key 가 입력되어 check_books_info_by_title_publisher()를 재실행하여 신규로 입력된 book_key 값을 확인한다.
        returned_check_book_result = check_books_info_by_title_publisher(book_title, publisher)
        print('신규 등록 도서입니다.') 

    else:
        # books_info 수량 변경
        cursor.execute(f'UPDATE BOOKS_INFO SET TOTAL_COUNT = \'{int(returned_check_book_result[1])+ int(add_count)}\', AVAILABLE_COUNT = \'{int(returned_check_book_result[2])+ int(add_count)}\' \
            where book_key = \'{returned_check_book_result[0]}\'')
        print('사전에 등록된 추가 도서입니다.')
    
    # book_detail_info 등록
    for a_count in range(0, int(add_count)):
        cursor.execute("insert into book_detail_info (books_key, user_key, rent_stat, rent_start_date, rent_expire_date) values('{}','{}','{}','{}','{}')".format(returned_check_book_result[0], '', '', '', ''))


    # registe_new_book(100, '파이썬3', '후즈', 'C출판사', 3, '1F-3')

    # registe_new_book(100, '파이썬공부', 'To올라운더', 'A출판사', 4, '1F-A')

# 등록 도서 확인1(books_info)
def select_books_info_all_view():
    cursor.execute('select * from books_info order by 1')
    view_list = cursor.fetchall()
    print(view_list)


# 등록 도서 확인2(book_detail_info)
def select_book_detail_info_all_view():
    cursor.execute('select * from book_detail_info order by 1')
    view_list = cursor.fetchall()
    print(view_list)


if __name__ == '__main__':
    while True:
        print('\n************************\n')
        print('사용할 동작을 입력하세요.')
        print('  1. books_info Table 내역 전체 조회')
        print('  2. book_detail_info Talbe 내역 전체 조회')
        print('  3. 도서 추가 테스트')
        print('  0. 프로그램 종료')

        choice_action = input('동작을 입력하세요. ex) 1 입력 후 엔터 : ')

        if choice_action == '1':
            select_books_info_all_view()

        elif choice_action == '2':
            select_book_detail_info_all_view()

        elif choice_action == '3':
            group_code = input('group_code 를 입력하세요 : ')
            book_title = input('도서의 제목을 입력하세요 : ')
            writer = input('저자를 입력하세요 : ')
            publisher = input('출판사를 입력하세요. : ')
            add_count = int(input('추가된 도서 권수를 숫자만 입력하세요. ex) 5 :'))
            location = input('도서의 위치를 입력하세요. : ')
            registe_new_book(group_code, book_title, writer, publisher, add_count, location)            
            #registe_new_book(100, '파이썬공부', 'To올라운더', 'A출판사', 4, '1F-A')
        elif choice_action == '0':
            print('\n\n프로그램을 종료합니다.')
            break

 

 

 

이제 다음 포스팅은 대여를 하기 위해

사용자를 등록하는 과정을 포스팅해 보겠다.

앞의 포스팅들을 이해했다면 크게 어려움 없이 이해할 수 있을 코드들이다.