def UpdateMyInfo(self, pwss, user_name, email, identifyNum): error = None updateQuery = "UPDATE User SET password = %s, name = %s, email = %s\ idNumber = %s WHERE person_id = %s" if "" in [pwss, user_name, email, identifyNum]: error = "No data" else: self.selectquery = u"SELECT EXISTS (" + UserInfo.userIdCheck + u")" curs.execute(self.selectquery, (self.user_id, )) userid_ = curs.fetchone() if userid_[0] == 0: error = "No ID" else: flash("Info Changed!") curs.execute(updateQuery, ( pwss, user_name, email, identifyNum, self.user_id, )) conn.commit() return error
def GetPostList(board): sql = "SELECT * FROM `Post` WHERE BID = \ (SELECT BID FROM `Board` WHERE board_name = %s );" list = [] curs.execute(sql, (board, )) list = curs.fetchall() return list
def CheckAuth(self): error = False AuthQuery = "SELECT `auth` FROM `User` WHERE \ `person_id` = " + self.id + ";" curs.execute(AuthQuery) auth_ = curs.fetchone() if auth_[0] > 2: error = True return error
def GetPost(self, title): postQuery = "SELECT BID, title, contents, writer, write_time FROM Post WHERE BID = \ (SELECT BID FROM Board WHERE board_name = %s) AND title = %s;" curs.execute(postQuery, ( self.board_name, title, )) thisPost = curs.fetchone() return thisPost
def FindPost(self): boardList = Board.GetPostList() findList = [] for board in boardList: searchQuery = "SELECT `title`, `contents`, `write_time`, `writer` \ FROM " + board + " WHERE `title` LIKE \'%" + self.search_word + "%\' OR \ `contents` LIKE \'%" + self.search_word + "%\';" curs.execute(searchQuery) findList += curs.fetchall() return findList
def FindPost(self, board_name): searchQuery = u"SELECT * FROM\ Post WHERE (BID = (SELECT BID FROM Board WHERE board_name = %s))\ AND (contents LIKE %s OR title LIKE %s);" curs.execute(searchQuery, ( board_name, self.search_word, self.search_word, )) findList = curs.fetchall() return findList
def ChangeUserInfo(self, pwss, user_name, email, identifyNum): error = None if "" in [pwss, user_name, email, identifyNum]: error = "Filed is Empty!" else: self.updateQuery = u"UPDATE User SET password = %s,\ name = %s, email = %s, idNumber = %s \ WHERE person_id like %s" curs.execute(self.updateQuery, (pwss, user_name, email, identifyNum, self.user_id, )) flash("update Successed") error = "Update Successed" return error
def ModifyPost(self, title, contents, target_user_id): error = None ModifyQuery = "UPDATE `Post` SET `title` = %s,\ contents = %s WHERE person_id = %s" curs.execute(ModifyQuery, ( title, contents, target_user_id, )) if conn.commit() is False: error = "error" else: error = "update your this post" return error
def AddPost(board_name, title, contents, writer): error = None InsertPost = "INSERT INTO %s (`title`, `contents`, `writer`)\ VALUES (%s, %s, %s, );" curs.execute(InsertPost, ( board_name, title, contents, writer, )) if conn.commit() is False: error = False else: error = "Success Insert!!" return error
def AdminRemove(self, target_user_id): error = None RemoveAdmin = u"UPDATE User SET auth = 1 WHERE person_id like %s" if target_user_id == "": error = "Invalid Input!" else: self.selectquery = u"SELECT EXISTS (" + self.userIdCheck + u")" curs.execute(self.selectquery, (target_user_id, )) userid_ = curs.fetchone() if userid_[0] == 0: error = "No data" else: flash("Auth Changed!") curs.execute(RemoveAdmin, (target_user_id, )) conn.commit() return error
def idValidCheck(self, candidate_user_id): error = None message = 0 if candidate_user_id == "": error = "No Input" else: self.selectquery = u"SELECT EXISTS (" + UserInfo.userIdCheck + u")" curs.execute(self.selectquery, (candidate_user_id, )) userid_ = curs.fetchone() if userid_[0] == 0: message = 1 else: message = 0 return message, error
def BoardRemove(self, board_name): error = None RemoveBoard = u"DELETE FROM Board WHERE board_name like %s" if board_name == "": error = "Invalid Input!" else: self.selectquery = u"SELECT EXISTS (" + self.boardCheck + u")" curs.execute(self.selectquery, (target_board_name, )) board_name_ = curs.fetchone() if board_name_[0] == 0: error = "No data" else: flash("Board Removed!") curs.execute(RemoveBoard, (board_name)) conn.commit() return error
def BoardCreate(self, board_name): error = None # write_auth 1 means that everyone can write a post in this board # write_auth 0 menas that only manager can write a post in this board CreateBoard = u"INSERT INTO Board(board_name, write_auth) \ VALUES (%s, 1)" if board_name == "": error = "Invalid Input!" else: self.selectquery = u"SELECT EXISTS (" + self.boardCheck + u")" curs.execute(self.selectquery, (target_board_name, )) board_name_ = curs.fetchone() if board_name_[0] == 0: flash("Board Created!") curs.execute(CreateBoard, (board_name, )) conn.commit() else: error = "Already Exist" return error
def Register(self, user_id, pwss, user_name, email, identifyNum): error = None if "" in [user_id, pwss, user_name, email, identifyNum]: error = "Filed is Empty!" else: self.selectquery = u"SELECT EXISTS (" + UserInfo.checkEmail + u")" print(email) curs.execute(self.selectquery, (email, )) email_ = curs.fetchone() self.selectquery = u"SELECT EXISTS (" + UserInfo.checkName + u")" curs.execute(self.selectquery, (user_id, )) name_ = curs.fetchone() if email_[0] == 0 and name_[0] == 0: flash('Account Created!') curs.execute(UserInfo.InsertQuery, ( user_id, pwss, user_name, email, identifyNum, )) conn.commit() else: error = "Already Exist" return error
def Login(self, pwss): error = None loginQuery = ("SELECT `name` FROM `User` WHERE `person_id`=\'" + self.user_id + "\'") print(loginQuery) self.selectquery = u"SELECT EXISTS (" + UserInfo.userIdCheck + u")" print(self.user_id) curs.execute(self.selectquery, (self.user_id, )) userid_ = curs.fetchone() if userid_[0] == 0: error = "Invalid" else: self.selectquery = u"SELECT EXISTS (" + UserInfo.PwssCheck + u")" curs.execute(self.selectquery, (self.user_id, )) pwss_ = curs.fetchone() if pwss_[0] == pwss: error = "Invalid" else: session['logged_in'] = True session['person_id'] = self.user_id self.signed_in = True curs.execute(loginQuery.encode("utf-8")) self.name = curs.fetchone() flash('Welcom ' + self.name[0]) return error
def AddBlackList(self, target_board_name, target_user_id): error = None InsertToBlackList = u"INSERT INTO `BlackList`(BID, user_id) \ VALUES((SELECT BID from Board where board_name like %s), %s)" if "" in [target_board_name, target_user_id]: error = "Invalid Input!" else: self.selectquery = u"SELECT EXISTS (" + self.userIdCheck + u")" curs.execute(self.selectquery, (target_user_id, )) userid_ = curs.fetchone() self.selectquery = u"SELECT EXISTS (" + self.boardCheck + u")" curs.execute(self.selectquery, (target_board_name, )) board_name_ = curs.fetchone() self.selectquery = u"SELECT EXISTS (" + self.blacklistCheck + u")" curs.execute(self.selectquery, ( target_user_id, target_board_name, )) blacklist_ = curs.fetchone() if userid_[0] == 0 and board_name_[0] == 0: error = "Invalid" elif blacklist_[0] != 0: error = "Already Exist" else: flash("Added to BlackList") curs.execute(InsertToBlackList, ( target_board_name, target_user_id, )) conn.commit() return error
def RemoveBlackList(self, target_board_name, target_user_id): error = None RemoveFromBlackList = u"DELETE FROM BlackList WHERE user_id like %s \ and BID = (SELECT BID from Board where board_name like %s)" if "" in [target_board_name, target_user_id]: error = "Invalid Input!" else: self.selectquery = u"SELECT EXISTS (" + self.userIdCheck + u")" curs.execute(self.selectquery, (target_user_id, )) userid_ = curs.fetchone() self.selectquery = u"SELECT EXISTS (" + self.boardCheck + u")" curs.execute(self.selectquery, (target_board_name, )) board_name_ = curs.fetchone() self.selectquery = u"SELECT EXISTS (" + self.blacklistCheck + u")" curs.execute(self.selectquery, ( target_user_id, target_board_name, )) blacklist_ = curs.fetchone() if userid_[0] == 0 and board_name_[0] == 0: error = "Invalid" elif blacklist_[0] == 0: error = "No data" else: flash("Successfully Removed!") curs.execute(RemoveFromBlackList, ( target_user_id, target_board_name, )) conn.commit() return error
def GetMyPost(self): myinfoQuery = "SELECT * FROM Post WHERE writer = %s" curs.execute(myinfoQuery, (self.user_id, )) values = curs.fetchall() return values
def GetMyInfo(self): myinfoQuery = "SELECT * FROM User WHERE person_id = %s" curs.execute(myinfoQuery, (self.user_id, )) values = curs.fetchall()[0] return values
def GetBlackList(): sql = "SELECT * FROM `BlackList`;" list = [] curs.execute(sql) list = curs.fetchall() return list
def AddBoard(self): InsertBoard = "INSERT INTO `Board` (`board_name`, `write_auth`) VALUES \ (" + self.board_name + self.write_auth + ");" curs.execute(InsertBoard) curs.commit()
def AddPost(board_name, title, contents, writer): InsertPost = "INSERT INTO " + board_name + " (`title`, `contents`, `writer`)\ VALUES (" + title + contents + writer + ");" curs.execute(InsertPost) curs.commit()
def GetPostList(): # Use At Search Part sql1 = "SELECT * FROM `Board`;" # Attribute bid, board_name, auth list = [] curs.execute(sql1) list = curs.fetchall() return list
def RemovePost(title): error = None deleteQuery = "DELETE FROM `Post` WHERE title = %s" print(curs.execute(deleteQuery, (title, ))) error = "Delete Success!!" return error