[Python] 2022-02-10 개인 Project _ DB연습_농구스코어DB

# scoreBoardSQL.py

CREATE_TABLE = """
                CREATE TABLE 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
                    """

# scoreBoardDB.py
import sqlite3
import scoreBoardSQL


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


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


def createTable():
    con = None
    cursor = None
    try:
        con = connect('scoreBoard.db')
        cursor = con.cursor()

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


def selectAll():
    con = None
    cursor = None
    try:
        con = connect('scoreBoard.db')
        cursor = con.cursor()

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


def selectOne(backNumber):
    con = None
    cursor = None
    try:
        con = connect('scoreBoard.db')
        cursor = con.cursor()

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


def insert(backNumber, name, point):
    con = None
    cursor = None
    try:
        con = connect('scoreBoard.db')
        cursor = con.cursor()

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


def update(backNumber, name, point):
    con = None
    cursor = None
    try:
        con = connect('scoreBoard.db')
        cursor = con.cursor()

        selectOne(backNumber)

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


def delete(backNumber):
    con = None
    cursor = None
    try:
        con = connect('scoreBoard.db')
        cursor = con.cursor()

        selectOne(backNumber)

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


if __name__ == '__main__':
    try:
        createTable()
    except:
        print("Creating Table Error")

    try:
        insert(22, 'Andrew Wiggins', 15)
    except:
        print("Insert Error")

    try:
        update(22, 'Andrew Wiggins', 15)
    except:
        print("Update Error")

    # try:
    #     delete(22)
    # except:
    #     print("Delete Error")

    try:
        print(selectAll())
    except:
        print("Select All Error")

    try:
        print("%d %s %d" % selectOne(23))
    except:
        print("Select One Error")

# scoreBoardApp.py
import scoreBoardDB


def start():
    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(" - 득점: "))
                scoreBoardDB.insert(backNumber, name, point)
                print("Insertion Complete")
            except:
                print("Insert Error")
        elif menu == 'u':
            print("Update Player")
            try:
                backNumber = int(input(" - 등번호: "))
                name = input(" - 이름: ")
                point = int(input(" - 득점: "))
                scoreBoardDB.update(backNumber, name, point)
                print("Update Complete")
            except:
                print("Update Error")
        elif menu == 'd':
            print("Delete Player")
            try:
                backNumber = int(input(" - 등번호: "))
                scoreBoardDB.delete(backNumber)
                print("Deletion Complete")
            except:
                print("Delete Error")
        elif menu == 's':
            print("Print All Player")
            try:
                table = scoreBoardDB.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 = scoreBoardDB.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("종료합니다...")


start()

SELECT문으로 출력 결과