[Python] 2022-02-15 개인 Project _ DB연습2_농구스코어DB(class 및 상속 활용)

# scoreBoardSQL.py

CREATE_TABLE = """
                CREATE TABLE IF NOT EXISTS GSW (backNumber INT PRIMARY KEY, name CHAR(30), point INT)
                """
DROP_TABLE = """
            DROP TABLE IF EXISTS GSW 
            """

INSERT_PLAYER = """
                INSERT INTO GSW VALUES (%d, '%s', %d)
                """

UPDATE_PLAYER = """
                UPDATE GSW SET name = '%s', point = %d WHERE backNumber = %d 
                """

DELETE_PLAYER = """
                DELETE FROM GSW WHERE backNumber = %d
                """

SELECT_ALL_PLAYER = """
                    SELECT * FROM GSW
                    """

SELECT_ONE_PLAYER = """
                    SELECT * FROM GSW WHERE backNumber = %d
                    """

# DB.py
import sqlite3
import scoreBoardSQL


class DB:
    def __init__(self, file):
        self.__file = file

    def connect(self):
        try:
            con = sqlite3.connect(self.__file)
        except:
            raise Exception
        return con

    def close(self, cursor, con):
        try:
            if cursor is not None:
                cursor.close()
            if con is not None:
                con.close()
        except:
            raise Exception

    def createTable(self):
        con = None
        cursor = None
        try:
            con = self.connect()
            cursor = con.cursor()

            cursor.execute(scoreBoardSQL.CREATE_TABLE)
            con.commit()
        except:
            raise Exception
        finally:
            self.close(cursor, con)

# scoreBoardDB.py
import scoreBoardSQL
from dbExercise.DB import DB


class ScoreBoardDB(DB):
    def __init__(self, file):
        super().__init__(file)
        super().createTable()

    def selectAll(self):
        con = None
        cursor = None
        try:
            con = super().connect()
            cursor = con.cursor()

            cursor.execute(scoreBoardSQL.SELECT_ALL_PLAYER)
            table = cursor.fetchall()
        except:
            raise Exception
        finally:
            super().close(cursor, con)
        return table

    def selectOne(self, backNumber):
        con = None
        cursor = None
        try:
            con = super().connect()
            cursor = con.cursor()

            cursor.execute(scoreBoardSQL.SELECT_ONE_PLAYER % backNumber)
            record = cursor.fetchone()
            if record is None:
                raise Exception
        except:
            raise Exception
        finally:
            super().close(cursor, con)
        return record

    def insert(self, backNumber, name, point):
        con = None
        cursor = None
        try:
            con = super().connect()
            cursor = con.cursor()

            cursor.execute(scoreBoardSQL.INSERT_PLAYER % (backNumber, name, point))
            con.commit()
        except:
            raise Exception
        finally:
            super().close(cursor, con)

    def update(self, backNumber, name, point):
        con = None
        cursor = None
        try:
            con = super().connect()
            cursor = con.cursor()

            self.selectOne(backNumber)  # 없는 등번호 입력 시 에러 발생시키기 위함

            cursor.execute(scoreBoardSQL.UPDATE_PLAYER % (name, point, backNumber))
            con.commit()
        except:
            raise Exception
        finally:
            super().close(cursor, con)

    def delete(self, backNumber):
        con = None
        cursor = None
        try:
            con = super().connect()
            cursor = con.cursor()

            self.selectOne(backNumber)  # 없는 등번호 입력 시 에러 발생시키기 위함

            cursor.execute(scoreBoardSQL.DELETE_PLAYER % backNumber)
            con.commit()
        except:
            raise Exception
        finally:
            super().close(cursor, con)

# scoreBoardApp.py
import scoreBoardDB


def start():
    score = scoreBoardDB.ScoreBoardDB('scoreBoard.db')
    while True:
        print("<< Golden State Warriors Score Board >>")

        print("작업할 내용을 선택하세요.")
        menu = input("Insert: i, Update: u, Delete: d, Select: s, Select One: so, Quit: q -> ")

        if menu == 'i':
            print("Insert Player")
            try:
                backNumber = int(input(" - 등번호: "))
                name = input(" - 이름: ")
                point = int(input(" - 득점: "))
                score.insert(backNumber, name, point)
                print("Insertion Complete")
            except:
                print("Insert Error")
        elif menu == 'u':
            print("Update Player")
            try:
                backNumber = int(input(" - 등번호: "))
                print("%5d  %-25s %3d" % score.selectOne(backNumber))
                name = input(" - 이름: ")
                point = int(input(" - 득점: "))
                score.update(backNumber, name, point)
                print("Update Complete")
            except:
                print("Update Error")
        elif menu == 'd':
            print("Delete Player")
            try:
                backNumber = int(input(" - 등번호: "))
                score.delete(backNumber)
                print("Deletion Complete")
            except:
                print("Delete Error")
        elif menu == 's':
            print("Print All Player")
            try:
                table = score.selectAll()
                print("")
                print("등번호  이름                       득점")
                for record in table:
                    print("%5d  %-25s %3d" % record)
                print("")
                print("Print Complete")
            except:
                print("Select Error")
        elif menu == 'so':
            print("Print One Player")
            try:
                backNumber = int(input(" - 등번호: "))
                record = score.selectOne(backNumber)
                print("")
                print("등번호  이름                       득점")
                print("%5d  %-25s %3d" % record)
                print("")
                print("Print One player Complete")
            except:
                print("Select One Error")
        elif menu == 'q':
            break
        else:
            print("잘못 입력하였습니다.")
        print("")
    print("종료합니다...")


if __name__ == '__main__':
    start()
# 등번호  이름                       득점
#    22  Andrew Wiggins             15
#    32  Otto Porter Jr.             2
#     5  Kevon Looney                8
#    11  Klay Thompson              21
#    30  Stephen Curry              18
#     0  Gary Payton II              5
#     3  Jordan Poole               11
#    99  Jonathan Kuminga           16
#     1  Damion Lee                  6
#     9  Andre Iguodala              3
#    95  Juan Toscano-Anderson       0
#     4  Moses Moody                 5
#     2  Chris Chiozza               0