29. DB를 이용한 단어장 만들기

import MySQLdb

# Words 클래스
class Words:
    def __init__(self, eng, kor, lev=1):
        self.eng = eng
        self.kor = kor
        self.lev = lev

    def setEng(self, eng):
        self.eng = eng
  
    def setKor(self, kor):
        self.kor = kor

    def setLev(self, lev):
        self.lev =lev

    def getEng(self):
        return self.eng
  
    def getKor(self):
        return self.kor

    def getLev(self):
        return self.lev​

 

# SQL에서 작동할 클래스
class WordsDao:
    def __init__(self):
        self.db = None
 
    def connect(self):
        self.db = MySQLdb.connect('localhost', 'root', '1234', 'kdt')

    def disconnect(self):
        self.db.close()

    def insert(self, word):
        self.connect()
        cur = self.db.cursor()
        
        sql = 'insert into voca (eng, kor, lev) values (%s, %s, %s)'
        data = (word.getEng(), word.getKor(), word.getLev())
        cur.execute(sql, data)
        self.db.commit()

        cur.close()
        self.disconnect()

    def selectAll(self):
        self.connect()
        cur = self.db.cursor(MySQLdb.cursors.DictCursor)
        sql = 'select eng, kor, lev from voca order by eng asc'
        cur.execute(sql)
        row = cur.fetchall()
        cur.close()
        self.disconnect()
        return row

    def search(self, eng):
        self.connect()
        cur = self.db.cursor(MySQLdb.cursors.DictCursor)
        # select eng, kor, lev from voca where eng like %pp%; 이렇게 하고 싶으면 """
        # sql =  "select eng, kor, lev from voca where eng like concat('%%', s%. '%%')"
        sql = 'select eng, kor, lev from voca where eng = %s'
        data = (eng, )
        cur.execute(sql, data)
        row = cur.fetchall()
        cur.close()
        self.disconnect()
        return row
        
    def update(self, word):
        self.connect()
        cur = self.db.cursor()
        sql = 'update voca set kor=%s, lev=%s where eng=%s'
        data = (word.getKor(), word.getLev(), word.getEng())
        result = cur.execute(sql, data)
        self.db.commit()

        if result > 0:
            print('수정되었습니다.')
        else:
            print('에러')
        cur.close()
        self.disconnect()

    def delete(self, eng):
        self.connect()
        cur = self.db.cursor()
        sql = 'delete from voca where eng=%s'
        data = (eng,)
        result = cur.execute(sql, data)
        self.db.commit()

        if result > 0:
            print('삭제되었습니다.')
        else:
            print('오류')

        cur.close()
        self.disconnect()

 

# 기능을 담은 클래스
class WordsService():
    def __init__(self):
        self.dao = WordsDao()

    def insertWord(self):
        eng = input('단어 입력 :')
        kor = input('뜻 입력 :')
        lev = input('레벨 입력 :')
        word = Words(eng, kor, lev)
        self.dao.insert(word)

    def printAll(self):
        datas = self.dao.selectAll()   # 딕셔너리 전체
        for data in datas:
            print(f"{data['eng']}, 뜻:{data['kor']}, 레벨:{data['lev']}")

    def searchWord(self):
        eng = input('검색할 단어 :')
        datas = self.dao.search(eng)
        if datas:
            for data in datas:
                print(f"{data['eng']}, 뜻:{data['kor']}, 레벨:{data['lev']}")
        else:
            print('찾는 단어가 없습니다.')

    def editWord(self):
        eng = input('수정할 단어 :')
        word = self.dao.search(eng)
        if not word:
            print('수정할 단어가 없습니다.')
        else:
            kor = input('새로운 뜻을 입력 :')
            lev = input('새로운 레벨을 입력 :')
            word = Words(eng, kor, lev)
            self.dao.update(word)

    def delWord(self):
        eng = input('삭제할 단어 :')
        word = self.dao.search(eng)
        if not word:
            print('삭제할 단어가 없습니다.')
        else:
            self.dao.delete(eng)

 

#메뉴화면 클래스
class Menu:
    def __init__(self):
        self.service = WordsService()

    def run(self):
        while True:
            try:
                menu = int(input('1.등록하기 2.출력하기 3.검색하기 4.수정하기 5.삭제하기 6.종료하기'))
                if menu == 1:
                    self.service.insertWord()
                elif menu == 2:
                    self.service.printAll()
                elif menu == 3:
                    self.service.searchWord()
                elif menu == 4:
                    self.service.editWord()
                elif menu == 5:
                    self.service.delWord()
                elif menu == 6:
                    print('프로그램 종료')
                    break
                    
            except Exception as e:
                print(e)
                print('다시 입력하세요')

 

결과