[Python][mysql] 게시판

---

article (package)

---

init.py

__all__=[
    'vo',
    'dao',
    'service',
    'menu'
]

---

dao.py

import pymysql
import 게시판.article.vo as vo

class Dao:
    def connect(self):
        return pymysql.connect(host='localhost', user='testuser', password='testpw',
                       db='testdb', charset='utf8')

    def insert(self, a):
        conn = self.connect()
        sql = 'insert into board(writer, w_date, title, content) values(%s, now(), %s, %s)'
        cursor = conn.cursor()  # 사용할 커서 객체 생성
        d = (a.writer, a.title, a.content)
        cursor.execute(sql, d)
        conn.commit()  # 쓰기 완료
        conn.close()

    def selectByWriter(self, writer):
        conn = self.connect()
        sql = 'select * from board where writer=%s'
        cursor = conn.cursor()  # 사용할 커서 객체 생성
        cursor.execute(sql, writer)  # sql실행. 실행한 결과는 cursor 객체에 담아
        articles = []
        for row in cursor:
            articles.append(vo.Board(row[0], row[1], row[2], row[3], row[4]))
        conn.close()
        return articles

    def selectByNum(self, num):
        conn = self.connect()
        sql = 'select * from board where num=%s'
        cursor = conn.cursor()  # 사용할 커서 객체 생성
        cursor.execute(sql, num)  # sql실행. 실행한 결과는 cursor 객체에 담아
        row = cursor.fetchone()  # 검색 결과 한줄추출
        conn.close()
        if row != None:
            return vo.Board(row[0], row[1], row[2], row[3], row[4])

    def selectByTitle(self, title):
        conn = self.connect()
        sql = 'select * from board where title like \'%'+title+'%\''
        cursor = conn.cursor()  # 사용할 커서 객체 생성
        cursor.execute(sql)  # sql실행. 실행한 결과는 cursor 객체에 담아
        articles = []
        for row in cursor:
            articles.append(vo.Board(row[0], row[1], row[2], row[3], row[4]))
        conn.close()
        return articles

    def selectAll(self):
        conn = self.connect()
        sql = 'select * from board'
        cursor = conn.cursor()  # 사용할 커서 객체 생성
        cursor.execute(sql)  # sql실행. 실행한 결과는 cursor 객체에 담아
        articles = []
        for row in cursor:
            articles.append(vo.Board(row[0], row[1], row[2], row[3], row[4]))
        conn.close()
        return articles

    def update(self, b):# 수정할 제품번호와 새 가격을 Test 객체로 받아옴
        conn = self.connect()
        sql = 'update board set title=%s, content=%s, w_date=now() where num=%s'
        cursor = conn.cursor()  # 사용할 커서 객체 생성
        d = (b.title, b.content, b.num)
        cursor.execute(sql, d)
        conn.commit()  # 쓰기 완료
        conn.close()

    def delete(self, num):
        conn = self.connect()
        sql = 'delete from board where num=%s'
        cursor = conn.cursor()  # 사용할 커서 객체 생성
        cursor.execute(sql, num)
        conn.commit()  # 쓰기 완료
        conn.close()

---

menu.py

import 게시판.article.service as serv
class BMenu:
    def __init__(self):
        self.service = serv.Service()

    def run(self, loginId):
        self.service.loginId = loginId
        while True:
            m = int(input('1.작성 2.전체목록 3.번호로검색 4.작성자로검색 5.제목으로검색 6.수정 7.삭제 8.종료'))
            if m == 1:
                self.service.addBoard()
            elif m == 2:
                self.service.getAll()
            elif m == 3:
                self.service.getByNum()
            elif m == 4:
                self.service.getByWriter()
            elif m == 5:
                self.service.getByTitle()
            elif m == 6:
                self.service.editBoard()
            elif m == 7:
                self.service.delBoard()
            elif m == 8:
                break

---

service.py

import 게시판.article.vo as vo
import 게시판.article.dao as b_dao
import 게시판.member.service as mem_serv

class Service:

    def __init__(self):
        self.dao = b_dao.Dao()
        self.loginId = None  #로그인 아이디

    def addBoard(self):
        print('글작성')
        title = input('title')
        content = input('content')
        self.dao.insert(vo.Board(0, self.loginId, None, title, content))

    def getAll(self):
        print('글목록')
        articles = self.dao.selectAll()
        for i in articles:
            print(i)

    def getByNum(self):
        print('글 번호로 검색')
        num = int(input('검색할 글번호:'))
        b = self.dao.selectByNum(num)
        if b==None:
            print('없는 글번호')
        else:
            print(b)

    def getByWriter(self):
        print('글 작성자로 검색')
        writer = input('검색할 작성자:')
        articles = self.dao.selectByWriter(writer)
        if len(articles)==0:
            print(writer+'로 작성된 글 없음')
        else:
            for i in articles:
                print(i)

    def getByTitle(self):
        print('글 제목으로 검색')
        title = input('검색할 제목:')
        articles = self.dao.selectByTitle(title)
        if len(articles)==0:
            print(title+'로 검색된 글 없음')
        else:
            for i in articles:
                print(i)

    def editBoard(self):
        print('글 수정')
        num = int(input('수정할 글번호:'))
        b = self.dao.selectByNum(num)
        if b == None:
            print('없는 글번호')
        else:
            if b.writer==self.loginId:
                title = input('new title')
                content = input('new content')
                self.dao.update(vo.Board(num, '', '', title, content))
            else:
                print('당신의 글이 아니므로 수정 불가')

    def delBoard(self):
        print('글 삭제')
        num = int(input('삭제할 글번호:'))
        b = self.dao.selectByNum(num)
        if b == None:
            print('없는 글번호')
        else:
            if b.writer==self.loginId:
                self.dao.delete(num)
            else:
                print('당신의 글이 아니므로 삭제 불가')

---

vo.py

class Board:
    def __init__(self, num=None, writer=None, w_date=None, title=None, content=None):
        self.num = num
        self.writer = writer
        self.w_date = w_date
        self.title = title
        self.content = content

    def __str__(self):
        return 'num:'+str(self.num)+', writer:'+self.writer+', w_date:'+str(self.w_date)+', title:'+self.title+', content:'+self.content

---

member (package)

---

init.py

__all__=[
    'vo',
    'dao',
    'service',
    'menu'
]

---

dao.py

import pymysql
import 게시판.member.vo as mem
class Dao:
    def connect(self):
        return pymysql.connect(host='localhost', user='testuser', password='testpw',
                       db='testdb', charset='utf8')

    def insert(self, m):
        conn = self.connect()
        sql = 'insert into member values(%s, %s, %s, %s)'
        cursor = conn.cursor()  # 사용할 커서 객체 생성
        d = (m.id, m.pwd, m.name, m.email)
        cursor.execute(sql, d)
        conn.commit()  # 쓰기 완료
        conn.close()

    def select(self, id):
        conn = self.connect()
        sql = 'select * from member where id=%s'
        cursor = conn.cursor()  # 사용할 커서 객체 생성
        cursor.execute(sql, id)  # sql실행. 실행한 결과는 cursor 객체에 담아
        row = cursor.fetchone()#검색 결과 한줄추출
        conn.close()
        if row != None:
            return mem.Member(row[0], row[1], row[2], row[3])

    def update(self, id, pwd):# 수정할 제품번호와 새 가격을 Test 객체로 받아옴
        conn = self.connect()
        sql = 'update member set pwd=%s where id=%s'
        cursor = conn.cursor()  # 사용할 커서 객체 생성
        d = (pwd, id)
        cursor.execute(sql, d)
        conn.commit()  # 쓰기 완료
        conn.close()

    def delete(self, id):
        conn = self.connect()
        sql = 'delete from member where id=%s'
        cursor = conn.cursor()  # 사용할 커서 객체 생성
        cursor.execute(sql, id)
        conn.commit()  # 쓰기 완료
        conn.close()

---

menu.py

import 게시판.member.service as serv
class MemMenu:
    def __init__(self):
        self.service = serv.Service()

    def run(self):
        while True:
            m = int(input('1.가입 2.로그인 3.로그아웃 4.내정보수정 5.탈퇴 6.종료'))
            if m==1:
                self.service.join()
            elif m==2:
                self.service.login()
            elif m==3:
                self.service.logout()
            elif m==4:
                self.service.editInfo()
            elif m==5:
                self.service.out()
            elif m==6:
                break

    def loginCheck(self):
        if self.service.loginId==None:
            return (False,)
        else:
            return True, self.service.loginId

---

service.py

import 게시판.member.dao as mem_dao
import 게시판.member.vo as vo

'''1.가입 2.로그인 3.로그아웃 4.내정보수정 5.탈퇴'''


class Service:
    loginId = None  # 로그인한 사람의 아이디.

    def __init__(self):
        self.dao = mem_dao.Dao()

    def join(self):
        print('회원가입')
        while True:
            id = input('id:')
            m = self.dao.select(id)
            if m == None:  # 중복되지 않은 아이디
                break
            else:
                print('중복된 아이디. 다시 입력하시오')

        pwd = input('pwd:')
        name = input('name:')
        email = input('email:')
        self.dao.insert(vo.Member(id, pwd, name, email))

    def login(self):
        id = input('id:')
        m = self.dao.select(id)
        if m == None:
            print('없는 아이디')
        else:
            pwd = input('pwd:')
            if m.pwd == pwd:
                print('로그인 성공')
                Service.loginId = id  # 로그인 세션을 유지
            else:
                print('패스워드 불일치')

    def loginCheck(self):
        if Service.loginId == None:
            print('로그인 먼저')
            return False  # 로그인 안된 상태
        else:
            return True  # 로그인 된 상태

    def logout(self):
        flag = self.loginCheck()
        if flag:
            Service.loginId = None

    def editInfo(self):
        flag = self.loginCheck()
        if flag:
            pwd = input('new pwd:')
            self.dao.update(Service.loginId, pwd)

    # 추가로 회원 탈퇴하면 로그아웃 되게 만들어야 한다.
    def out(self):
        flag = self.loginCheck()
        if flag:
            self.dao.delete(Service.loginId)

---

vo.py

class Member:
    def __init__(self, id=None, pwd=None, name=None, email=None):
        self.id = id
        self.pwd = pwd
        self.name = name
        self.email = email

    def __str__(self):
        return 'id:'+self.id+', pwd:'+self.pwd+', name:'+self.name+', email:'+self.email

    

---

main.py

import 게시판.member.menu as m1
import 게시판.article.menu as m2

def main():
    mem_menu = m1.MemMenu()
    ar_menu = m2.BMenu()

    while True:
        m = int(input('1.회원관리 2.은표님게시판 3.종료'))
        if m==1:
            mem_menu.run()
        elif m==2:#로그인 되어있어야 사용 가능하도록 구현
            d = mem_menu.loginCheck()
            if d[0]:#로그인 상태. d[1]:로그인아이디
                ar_menu.run(d[1])
            else:
                print('로그인 먼저 하시오')
        elif m==3:
            break

main()