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
