파이썬(골프연습장회원관리프로그램)_2024-06-28

골프연습장_회원관리.pdf

0.33MB

 

DB

--SELECT 'DROP TABLE "' || TABLE_NAME || '" CASCADE CONSTRAINTS;' FROM user_tables;
drop table TBL_MEMBER_202201;
drop table TBL_CLASS_202201;
--update TBL_MEMBER_202201 set C_NO = '20001'

--1.강사정보 테이블 생성
CREATE TABLE TBL_TEACHER_202201 (
    TEACHER_CODE CHAR(3) PRIMARY KEY,
    TEACHER_NAME VARCHAR2(15),
    CLASS_NAME VARCHAR2(20),
    CLASS_PRICE NUMBER(8),
    TEACHER_REGIST_DATE VARCHAR2(8)
    );

--2.회원정보 테이블 생성
CREATE TABLE TBL_MEMBER_202201 (
    C_NO CHAR(5) PRIMARY KEY,
    C_NAME VARCHAR2(15),
    PHONE VARCHAR2(11),
    ADDRESS VARCHAR2(50),
    GRADE VARCHAR2(6)
    );

--3.수강정보 테이블 생성
CREATE TABLE TBL_CLASS_202201 (
    REGIST_MONTH VARCHAR2(6),
    C_NO CHAR(5),
    CLASS_AREA VARCHAR2(15),
    TUITION NUMBER(8),
    TEACHER_CODE CHAR(3),
    CONSTRAINT TBL_CLASS_202201_PK PRIMARY KEY(REGIST_MONTH, C_NO)
    );

--1.강사정보 테이블(TBL_TEACHER_202201)
insert into TBL_TEACHER_202201 values(100, '이초급', '초급반', 100000, '20220101');
insert into TBL_TEACHER_202201 values(200, '김중급', '중급반', 200000, '20220102');
insert into TBL_TEACHER_202201 values(300, '박고급', '고급반', 300000, '20220103');
insert into TBL_TEACHER_202201 values(400, '정심화', '심화반', 400000, '20220104');
commit;        

--2.회원정보 테이블(TBL_MEMBER_202201)
insert into TBL_MEMBER_202201 values(10001, '홍길동', 01011112222, '서울시 강남구', '일반');
insert into TBL_MEMBER_202201 values(10002, '장발장', 01022223333, '성남시 분당구', '일반');
insert into TBL_MEMBER_202201 values(10003, '임꺽정', 01033334444, '대전시 유성구', '일반');
insert into TBL_MEMBER_202201 values(20001, '성춘향', 01044445555, '부산시 서구', 'VIP');
insert into TBL_MEMBER_202201 values(20002, '이몽룡', 01055556666, '대구시 북구', 'VIP');
commit;

--3.수강정보 테이블(TBL_CLASS_202201)
insert into TBL_CLASS_202201 values(202203, 10001, '서울본원', 100000, 100);
insert into TBL_CLASS_202201 values(202203, 10002, '성남분원', 100000, 100);
insert into TBL_CLASS_202201 values(202203, 10003, '대전분원', 200000, 200);
insert into TBL_CLASS_202201 values(202203, 20001, '부산분원', 150000, 300);
insert into TBL_CLASS_202201 values(202203, 20002, '대구본원', 200000, 400);
commit;

 

테이블정의서1.xls

0.36MB

oracledb.init_oracle_client()

while True:
    conn = oracledb.connect(user=username, password=password, dsn=dsn)
    cur = conn.cursor()

    print("============= 골프연습장 회원관리 프로그램 =============")
    print("1. 강사조회")
    print("2. 회원정보조회")
    print("3. 강사매출현황")
    print("4. 프로그램 종료")
    print("========================================================")
    choice = int(input("선택 : "))

    if choice == 1:
        sql_query = '''
        SELECT M.TEACHER_CODE AS 강사코드,
            M.TEACHER_NAME AS 강사명,
            M.CLASS_NAME AS 강의명,
            TO_CHAR(M.CLASS_PRICE,'L9,999,999') AS 수강료,    
            TO_CHAR(TO_DATE(M.TEACHER_REGIST_DATE, 'YYYYMMDD'),'YYYY"년"MM"월"DD"일') AS 강사자격취득일
        FROM TBL_TEACHER_202201 M
        '''
        cur.execute(sql_query)

        print('강사코드    강사명      강의명              수강료 강사자격취득일')
        print('-'*100)

        for row in cur:
            data1 = row[0]
            data2 = row[1]
            data3 = row[2]
            data4 = row[3]
            data5 = row[4]
            print(f"{data1:10} {data2:10} {data3:0} {data4:10} {data5:10}")

    elif choice == 2:
        sql_query = '''
        SELECT
            TO_CHAR(TO_DATE(B.REGIST_MONTH, 'YYYYMM'),'YYYY"년"MM"월"') AS 수강월,
            A.C_NO AS 회원번호,
            A.C_NAME AS 회원명,
            D.CLASS_NAME AS 강의명,
            B.CLASS_AREA AS 강의장소,
            TO_CHAR(D.class_price,'L9,999,999') AS 수강료,
            A.GRADE AS 등급
        FROM TBL_MEMBER_202201 A
            JOIN TBL_CLASS_202201 B ON A.C_NO = B.C_NO
            JOIN TBL_TEACHER_202201 D ON B.TEACHER_CODE = D.TEACHER_CODE
        '''
        cur.execute(sql_query)

        print('  수강월         회원번호    회원명    강의명             강의장소         수강료   등급')
        print('-'*100)

        for row in cur:
            data1 = row[0]
            data2 = row[1]
            data3 = row[2]
            data4 = row[3]
            data5 = row[4]
            data6 = row[5]
            data7 = row[6]
            print(f"{data1:15} {data2:10} {data3:0} {data4:10} {data5:0} {data6:10} {data7:10}")
       
    elif choice == 3:
        sql_query = '''
        SELECT M.TEACHER_CODE AS 강사코드,
            M.CLASS_NAME AS 강의명,
            M.TEACHER_NAME AS 강사명,
            TO_CHAR(sum(D.TUITION),'L9,999,999') AS 총매출액    
        FROM TBL_TEACHER_202201 M
            JOIN TBL_CLASS_202201 D ON M.TEACHER_CODE = D.TEACHER_CODE
        GROUP by M.TEACHER_CODE, M.CLASS_NAME, M.TEACHER_NAME
        ORDER BY M.TEACHER_CODE ASC
        '''
        cur.execute(sql_query)

        print('강사코드   강의명        강사명            총매출액')
        print('-'*100)

        for row in cur:
            data1 = row[0]
            data2 = row[1]
            data3 = row[2]
            data4 = row[3]
            print(f"{data1:10} {data2:10} {data3:0} {data4:10}")


    elif choice == 4:
        print("프로그램을 종료합니다.")
        break

    else:
        print("잘못된 선택입니다. 다시 선택해주세요.")

    cur.close()
    conn.close()