def update_solved_num(self) -> bool: sql = '''UPDATE users SET solved_num=%s WHERE id=%s''' connect = get_connect() with connect.cursor() as cursor: cursor.execute(sql, (self.solved_num, self.id)) connect.commit() return True
def remove(self) -> None: """ 删除用户 """ sql = '''DELETE FROM users WHERE id=%s''' connect = get_connect() with connect.cursor() as cursor: cursor.execute(sql, (self.id, )) connect.commit()
def confirm(self) -> None: """ 确认用户 """ sql = '''UPDATE users SET `status`='fetching' WHERE id=%s''' connect = get_connect() with connect.cursor() as cursor: cursor.execute(sql, (self.id, )) connect.commit()
def set_notice(notice: str): """ 设置公告 :return: """ sql = '''UPDATE server_infos SET notice=%s WHERE id=1''' connect = get_connect() with connect.cursor() as cursor: cursor.execute(sql, (notice, )) connect.commit()
def get_notice(): """ 获取公告 :return: """ sql = '''SELECT notice FROM server_infos LIMIT 1''' connect = get_connect() with connect.cursor() as cursor: cursor.execute(sql, ) return cursor.fetchone()[0]
def remove_admin(id: int): """ 删除管理员 :param id: :return: """ sql = '''DELETE FROM admins WHERE id=%s''' connect = get_connect() with connect.cursor() as cursor: cursor.execute(sql, (id, )) connect.commit()
def add(self): """ 添加管理员 :return: """ sql = '''INSERT INTO admins(uid,is_super,pwd) VALUES(%s, %s, %s)''' connect = get_connect() with connect.cursor() as cursor: cursor.execute(sql, (self.uid, self.is_super, self.pwd)) connect.commit() self.id = cursor.lastrowid
def get_admin_list() -> Tuple[tuple]: """ 获取管理员列表 :return: """ sql = '''SELECT admins.id, admins.uid, admins.is_super FROM admins''' connect = get_connect() with connect.cursor(cursor=pymysql.cursors.DictCursor) as cursor: cursor.execute(sql) rows = cursor.fetchall() return rows
def get_rank() -> Tuple[tuple]: """ 获取排行榜 :return: """ sql = '''SELECT users.id, users.uid, users.pwd, users.class_name, users.`name`, users.motto,users.account, \ users.solved_num, users.`status`, users.html FROM users ORDER BY users.solved_num DESC''' connect = get_connect() with connect.cursor(cursor=pymysql.cursors.DictCursor) as cursor: cursor.execute(sql) rows = cursor.fetchall() return rows
def exist_account(account: str) -> bool: """ 判断账号是否已经被占用 :param account: :return: 被占用返回True,否则返回False """ sql = '''SELECT 1 FROM `users` WHERE account = %s LIMIT 1''' connect = get_connect() with connect.cursor() as cursor: cursor.execute(sql, (account, )) if cursor.fetchone(): return True return False
def exist_uid(uid: str) -> bool: """ 判断用户名是否存在 :param uid: :return: """ sql = '''SELECT 1 FROM users WHERE uid = %s LIMIT 1''' connect = get_connect() with connect.cursor() as cursor: cursor.execute(sql, (uid, )) if cursor.fetchone(): return True else: return False
def add(self): """ 添加用户到数据库 :return: """ self.solved_num = 0 self.status = User.UNCHECKED_STATUS sql = '''INSERT INTO users(uid,pwd,class_name,`name`,account,motto,html) VALUES(%s,%s,%s,%s,%s,%s,%s)''' connect = get_connect() with connect.cursor() as cursor: cursor.execute(sql, (self.uid, self.pwd, self.class_name, self.name, self.account, self.motto, self.html)) connect.commit() self.id = cursor.lastrowid
def exist_uid(uid: str) -> bool: """ 判断uid是否已经存在 :param uid: :return: """ sql = '''SELECT 1 FROM admins WHERE uid=%s LIMIT 1''' connect = get_connect() with connect.cursor() as cursor: cursor.execute(sql, (uid, )) row = cursor.fetchone() if row: return True else: return False
def login(uid: str, pwd: str) -> Optional[Admin]: """ 登录 :return: """ sql = '''SELECT admins.id, admins.uid, admins.is_super FROM admins WHERE uid=%s AND pwd=%s LIMIT 1''' connect = get_connect() with connect.cursor() as cursor: cursor.execute(sql, (uid, pwd)) row = cursor.fetchone() if row: admin = Admin() admin.id, admin.uid, admin.is_super = row return admin else: return None
def update(self): """ 更新用户 """ parameters = [] sql_request_string = [] for filed in self.__dict__.items(): if filed[1] and filed[0] != 'id': sql_request_string.append(str.format("`{0}`=%s", filed[0])) parameters.append(filed[1]) sql = '''UPDATE admins SET ''' + ','.join( sql_request_string) + ''' WHERE id=%s''' parameters.append(self.id) connect = get_connect() with connect.cursor() as cursor: cursor.execute(sql, tuple(parameters)) connect.commit()
def get_fetching_list() -> List[User]: """ 所有等待获取的用户列表 :return: """ sql = '''SELECT id,`name`,account,motto,solved_num,`status` FROM `users` WHERE `status`!= 'unchecked' ''' connect = get_connect() with connect.cursor() as cursor: cursor.execute(sql) rows = cursor.fetchall() user_list = [] for row in rows: user = User() (user.id, user.name, user.account, user.motto, user.solved_num, user.status) = row user_list.append(user) return user_list
def login(uid: str, pwd: str) -> Optional[User]: """ 用户登录 :param uid: :param pwd: :return: """ sql = '''SELECT users.id, users.uid, users.class_name, users.`name`, users.motto, users.account, users.solved_num \ , users.`status`, users.html FROM users WHERE uid=%s AND pwd=%s LIMIT 1''' connect = get_connect() with connect.cursor() as cursor: cursor.execute(sql, (uid, pwd)) row = cursor.fetchone() if row: user = User() user.id, user.uid, user.class_name, user.name, user.motto, user.account, user.solved_num, user.status, user.html = row return user else: return None