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

# MySQL에서 words 테이블 생성
create table words(
	eng varchar(100) primary key,
    kor varchar(100) not null,
    lev int
);

 

# DTO
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 getEng(self):
        return self.eng

    def setKor(self, kor):
        self.kor = kor
    
    def getKor(self):
        return self.kor

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

import MySQLdb

# DAO 역할의 클래스
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 words values (%s, %s, %s)"
        data = (word.getEng(), word.getKor(), word.getLev())
        cur.execute(sql, data)
        self.db.commit()
        self.disconnect()     
        
    def selectAll(self):
        self.connect()
        cur = self.db.cursor()
        sql = 'select eng, kor, lev from words'
        cur.execute(sql)
        row = cur.fetchall()
        self.disconnect()
        return row

    def search(self, eng):
        self.connect()
        cur = self.db.cursor()
        sql = 'select eng, kor, lev from words where eng=%s'
        data = (eng,)
        cur.execute(sql, data)
        row = cur.fetchone()
        self.disconnect()
        return row
    
    def update(self, word):
        self.connect()
        cur = self.db.cursor()
        sql = 'update words 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('해당 단어가 없습니다')
        self.disconnect()
    
    def delete(self, eng):
        self.connect()
        cur = self.db.cursor()
        sql = 'delete from words where eng=%s'
        data = (eng,)
        result = cur.execute(sql, data)
        self.db.commit()
        if result > 0:
            print('삭제되었습니다')
        else:
            print('해당 단어가 없습니다')
        self.disconnect()

# Service를 담당하는 클래스
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()
        print(datas)
    
    def searchWord(self):
        eng = input('검색할 단어를 입력하세요')
        word = self.dao.search(eng)
        if word:
            print(word)
        else:
            print('찾는 단어가 없습니다')
    
    def editWord(self):
        eng = input('수정할 단어를 입력하세요')
        word = self.dao.search(eng)
        if word == None:
            print('수정할 단어를 찾지 못했습니다')
        else:
            kor = input('새로운 뜻을 입력하세요')
            lev = input('새로운 레벨을 입력하세요')
            word = Words(eng, kor, lev)
            self.dao.update(word)
    
    def delWord(self):
        eng = input('삭제할 단어를 입력하세요')
        self.dao.delete(eng)

# View 역할을 하는 클래스
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:
                    break
            except Exception as e:
                print(e)
                print('다시 입력하세요')

start = Menu()
start.run()