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

대여 함수를 작성하던 지난 포스팅에 이어 나가기 전,

한가지 선행되어야 하는 조건이 있다.

 

바로 회원의 상태인데...

앞의 포스팅에서 도서를 대여할 때 가장 먼저 회원이 대여 가능한 상태인지를 확인하는 과정이 있었다.

 

그런데, 지금 해당 상태에 아무런 값이 없어, 관리자가 변경할 수 있는 함수를 먼저 추가하려 한다.

1. 회원 상태 변경 하기(관리자 기능)

# 회원 등록
def registe_members(members_id, members_name, members_dob, phone_no, address, e_mail):

    # ID 등록 여부 확인
    cursor.execute(f'select * from members where members_id = \'{members_id}\'')
    members_id_result = cursor.fetchall()

    # 같은 ID로 등록된 내역이 없으면
    if len(members_id_result) == 0: 

        cursor.execute("insert into members(members_id, members_name, members_dob, members_stat, phone_no, address, e_mail, rent_count, memo1, memo2) \
            values('{}','{}','{}','{}','{}','{}','{}','{}','{}','{}')".format(members_id, members_name, members_dob, '대기', phone_no, address, e_mail, '0', '', ''))

        # registe_members('allrounder', '올라운더', '20020101', '010-1234-5678', '서울시 어딘가', 'to_allrounder@gmail.com')

    # 같은 ID로 등록된 내역이 있으면
    else :
        print('이미 등록된 ID 입니다.')

 

먼저 이전에 작성한 회원 등록 함수를 살펴보면, 해당 회원이 처음 등록을 요청하면, 

members.members_stat 값은 '대기' 상태가 된다.

 

이제 관리자가 해야 할 일은

'대기' 상태의 회원을 조회하는 것과 '대기' 에서 '승인' 또는 거절로 변경하는 함수가 필요하다.

 

change_member_stat 이라는 함수를 만들고, 

우리가 실행할 기능을 print 문을 통해 출력한다.

그리고 input() 매서드를 통해 원하는 기능을 입력 받고, 기능을 수행한다.

def change_member_stat():
    print('1. 승인 대기 목록 조회 ')
    print('2. 승인 대기 대상자 상태 변경 [승인, 거절, 블랙 처리]')
    print('3. 지연 반납자 목록 조회')
    print('4. 블랙 리스트 조회')
    
    command_input = input('수행할 기능을 입력하세요. : ')

 

먼저 [1. 승인 대기 목록 조회] 기능을 수행해보면

    if command_input == '1':
        cursor.execute('select members_id, members_name from members where members_stat = "대기"')
        result_list = cursor.fetchall()
        print(result_list)

 

앞에서 사용하던 select 문의 결과를 조회하는 간단한 기능을 수행할 수 있다.

여기서 고유 값인 members_id와 members_name 을 확인할 수 있고,

해당 값을 result_list에 입력한다.

 

테스트를 위해 임의의 회원(superman)을 추가한 뒤,

 

해당 기능을 확인해보면,

 

신규로 등록한 superman 이 나타나는 것을 볼 수 있다.

 

이제 해당 대상자의 상태를 변경해 볼텐데, command_input 이 '2' 일 때,

상태를 변경할 회원의 id를 input_members_id 로 입력을 받고,

해당 회원을 1.승인 / 2.거절 / 3.블랙으로 상태를 변경한다.

    elif command_input == '2':
        input_memvers_id = input('승인 또는 거절 처리할 계정 ID를 입력하세요. : ')
        input_stat = input('승인 처리는 1, 거절 처리는 2, 블랙 처리는 3 를 입력하세요. : ')
        
        select_members_no = cursor.execute(f'select members_no from members where members_id = \'{input_memvers_id}\' ')
        members_no = select_members_no.fetchall()
        print(members_no)
        
        if input_stat == '1' :
            cursor.execute(f'update members set members_stat = \'승인\' where members_no = \'{members_no[0][0]}\'')
        
        elif input_stat == '2' :
            cursor.execute(f'update members set members_stat = \'거절\' where members_no = \'{members_no[0][0]}\'')

        elif input_stat == '3' :
            cursor.execute(f'update members set members_stat = \'블랙\' where members_no = \'{members_no[0][0]}\'')

 

해당 과정을 실행해보면

1. 대기 상태인 회원 조회(노랑)

2. superman 회원 상태 변경(주황)

3. superman 회원 상태 확인(빨강)

 

으로 우리가 계획 했던 대로 대여가 가능한 승인 상태로 상태가 변경되어 있음을 확인할 수 있다.

 

여기까지 전체코드를 보면

import sqlite3, datetime

# 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)')


# 회원 테이블
cursor.execute('CREATE TABLE IF NOT EXISTS members(members_no integer primary key autoincrement , members_id text unique, members_name text, members_dob timestamp,\
    members_stat text, phone_no text, address text, e_mail text, rent_count integer, memo1 text, memo2 text)')


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

# 동일 도서 등록 여부 확인
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)




# 회원 등록
def registe_members(members_id, members_name, members_dob, phone_no, address, e_mail):

    # ID 등록 여부 확인
    cursor.execute(f'select * from members where members_id = \'{members_id}\'')
    members_id_result = cursor.fetchall()

    # 같은 ID로 등록된 내역이 없으면
    if len(members_id_result) == 0: 

        cursor.execute("insert into members(members_id, members_name, members_dob, members_stat, phone_no, address, e_mail, rent_count, memo1, memo2) \
            values('{}','{}','{}','{}','{}','{}','{}','{}','{}','{}')".format(members_id, members_name, members_dob, '대기', phone_no, address, e_mail, '0', '', ''))

        # registe_members('allrounder', '올라운더', '20020101', '010-1234-5678', '서울시 어딘가', 'to_allrounder@gmail.com')

    # 같은 ID로 등록된 내역이 있으면
    else :
        print('이미 등록된 ID 입니다.')



# 전체 등록 회원 확인
def select_members_all_view():
    cursor.execute('select * from members order by 1')
    view_list = cursor.fetchall()
    print(view_list)



# 도서 대여 하기 (작성 중....미완)
def rent_book(members_id, book_detail_key):
    # 해당 회원 도서 대여 가능 상태 확인, 조건 members_stat = '승인', rent_count < 6
    use_rent_stat = False # DB 검색 전, 대여 가능 상태 초기화 
    cursor.execute(f'select members_no, members_stat, rent_count from members where members_id = \'{members_id}\'')
    members_result = cursor.fetchall()
    if len(members_result) == 0 :
        print('등록 되지 않은 ID 입니다. 먼저 회원 가입을 진행해주세요.')

    elif len(members_result) == 1:
        if members_result[0][1] == '승인':
            if int(members_result[0][2]) < 5 :
                # 대여 가능 상태
                use_rent_stat = True

            else:
                print('대여 가능 권수가 초과하였습니다. 반납 후 이용하세요.') 
        else:
            print('대여가 불가능한 상태입니다. 관리자에게 문의하세요.')
        

def change_member_stat():
    print('1. 승인 대기 목록 조회 ')
    print('2. 승인 대기 대상자 상태 변경 [승인, 거절, 블랙 처리]')
    print('3. 지연 반납자 목록 조회')
    print('4. 블랙 리스트 조회')

    
    command_input = input('수행할 기능을 입력하세요. : ')
    if command_input == '1':
        cursor.execute('select members_id, members_name from members where members_stat = "대기"')
        result_list = cursor.fetchall()
        print(result_list)
    
    elif command_input == '2':
        input_members_id = input('승인 또는 거절 처리할 계정 ID를 입력하세요. : ')
        input_stat = input('승인 처리는 1, 거절 처리는 2, 블랙 처리는 3 를 입력하세요. : ')
        
        select_members_no = cursor.execute(f'select members_no from members where members_id = \'{input_members_id}\' ')
        members_no = select_members_no.fetchall()
        print(members_no)
        
        if input_stat == '1' :
            cursor.execute(f'update members set members_stat = \'승인\' where members_no = \'{members_no[0][0]}\'')
        
        elif input_stat == '2' :
            cursor.execute(f'update members set members_stat = \'거절\' where members_no = \'{members_no[0][0]}\'')

        elif input_stat == '3' :
            cursor.execute(f'update members set members_stat = \'블랙\' where members_no = \'{members_no[0][0]}\'')


    elif input_stat == '3' :
        print('지연 반납자 목록 조회 작성')
    
    elif input_stat == '4' :
        select_members_no = cursor.execute(f'select members_no, members_id, members_name from members where members_stat = \'블랙\' ')
        members_no_list = select_members_no.fetchall()
        print(members_no_list)

if __name__ == '__main__':
    while True:
        print('\n************************\n')
        print('사용할 동작을 입력하세요.')
        print('  1. books_info Table 내역 전체 조회')
        print('  2. book_detail_info Talbe 내역 전체 조회')
        print('  3. 도서 추가')
        print('  4. members Table 내역 전체 조회')
        print('  5. 회원 추가')
        print('  6. 회원 상태 변경')
        print('  7. 도서 대여 테스트')
        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 == '4':
            select_members_all_view()

        elif choice_action == '5':
            members_id = input('회원 ID를 입력하세요 : ')
            members_name = input('이름을 입력하세요 : ')
            members_dob = input('생년월일을 입력하세요. 형식(YYYYMMDD) : ')
            # members_stat = '대기'
            phone_no = input('연락처를 입력하세요 : ')
            address = input('주소를 입력하세요 : ')
            e_mail = input('e-mail 주소를 입력하세요 : ')
            # rent_count = '0'
            # memo1 = ''
            # memo2 = ''

            registe_members(members_id, members_name, members_dob, phone_no, address, e_mail)
            #registe_members('allrounder', '올라운더', '20020101', '010-1234-5678', '서울시 어딘가', 'to_allrounder@gmail.com')

        elif choice_action == '6':
            change_member_stat()

        elif choice_action == '7':
            members_id = input('회원 ID를 입력하세요. : ')
            book_detail_key = int(input('책 표지에 있는 책ID 값을 입력하세요. :'))
            rent_book(members_id, book_detail_key)


        elif choice_action == '0':
            print('\n\n프로그램을 종료합니다.')
            break

 

 

대여라는 과정을 글로 풀어 쓰다보니...

벌써 2번째 포스팅이 끝이 났다.

 

이제 다음 포스팅을 마지막으로 대여에 대한 기능을 완성해보도록 하겠다.

 

사실....파이썬을 통한 전체 프로그램을 작성하기 때문에 여러 함수를 파이썬으로 구현하고 있지만,

대부분의 DB는 프로시저를 지원한다. 간단히 말하면 우리가 작성하는 함수를 DB에서 만들 수 있고,

우리가 파라미터만 전달하면 DB에 있는 프로시저(함수)를 통해 일련의 과정을 수행할 수 있다는 의미이다.

 

예를 들어 지금 처럼 우리가 직접 회원 정보를 변경하는 매서드를 파이썬으로 작성하여 DB에 직접 update를 진행할 수도 있지만, DB에 update_members_stat 이라는 프로시저를 만든 뒤, 1번째 인자로 회원 ID, 2번째 인자로 상태 값을 전달 받는 구조로 프로시저를 작성해두면 DB는 처음과 동일하게 원하는 회원의 정보를 update 할 수 있다는 의미이다.

 

어쨋든 복잡한 이야기는 뒤로 두고, 우리는 일단 우리가 할 수 있는 범위 내에서 최선을 다해보자.