def writeExcel(result: list, backup_path: str):
"""写入数据到excel表格里面
:param result: 从数据库取的数据
:param backup_path: 需要备份的数据路径
:return:
"""
# 创建一个 workbook 设置编码
workbook = xlwt.Workbook(encoding='utf-8')
# 创建一个 worksheet, 添加名字
worksheet = workbook.add_sheet('Mysql_Backup')
# 创建style实例
style = initStyle(True, 2, 20 * 12, 2, xlwt.Pattern.SOLID_PATTERN, 5)
# 添加表格的字段名
column_number = (len(result[0]) if result else 0)
# 合并单元格并写入数据第一行日期名称
worksheet.write_merge(0, 1, 0, column_number-1, getlocaltime().decode(
'utf-8') + " -- 数据备份", style) # 第多少行合并几行，第多少列合并几列，写入数据数据，用什么样式
# 设置行高
tall_style = xlwt.easyxf('font:height 320')
# 数据库字段长度适配
worksheet.col(0).width = 256 * 30 # 适配USER_TYPE
worksheet.col(1).width = 256 * 30 # 适配USER_LOCK
worksheet.col(2).width = 256*30 # 适配USER_NAME
worksheet.col(3).width = 256*40 # 适配USER_PASSWD
worksheet.col(4).width = 256*80 # 适配USER_SALT
worksheet.col(5).width = 256*30 # 适配USER_MAIL
worksheet.col(6).width = 256*30 # 适配SUBBMISSION_DATE
# 写入数据库表的字段名
if column_number > 0:
i = 0
style2 = initStyle(True, 0, 20 * 12, 1, xlwt.Pattern.NO_PATTERN, 5)
worksheet.row(2).set_style(tall_style)
for name in (result[0]).keys():
worksheet.write(2, i, name, style2)
i += 1
else:
print("数据库表格为空")
style3 = initStyle(False, 0, 20 * 12, 1, xlwt.Pattern.NO_PATTERN, 5)
i = 3
for user in result:
j = 0
for k, v in user.items():
print('{0} --> {1}'.format(k, v))
if j < 7:
worksheet.write(i, j, v, style3)
print("this", i, j, v)
j += 1
i += 1
# r表示忽略转义字符,使用decode是因为返回的是byte要转为str
workbook.save(
backup_path + r'\db_Excel_{}.xls'.format((getlocaltime().decode('utf-8'))[:-9]))
def super_cleanLog():
""" 超级管理员清除服务器的所有用户行为日志errlogs.txt
:return: json结果
"""
username = request.form.get("username", type=str, default="")
if session.get("isLogin") and session.get("username") == username:
result = DB_Sync.query_redis(username)
usertype = result["data"]["USER_TYPE"]
userlock = result["data"]["USER_LOCK"]
# 超级用户且没有被人为锁定
if usertype == "super" and userlock == 0:
if cleanLogging():
code = "0"
msg = "文件清除成功"
logger.info("%s用户删除日志成功，具有权限为%s，锁定为%d" %
(username, usertype, userlock))
else:
code = "1"
msg = "文件异常，无法清除"
logger.error("%s用户删除日志失败,原因:文件异常，无法清除" % username)
else:
code = "-1"
msg = "您没有权限执行该操作"
else:
code = "-1"
msg = "您当前不处于登录状态，请先登录"
resp = jsonify(code=code, msg=msg)
resp.set_cookie("datetime", getlocaltime())
return resp
def register():
"""判断为新用户，新用户注册，需要结合传递过来的邮箱（邮箱要唯一）以及邮箱验证码，用户名（唯一）和密码
:return: json结果
"""
if request.method == "POST":
email = request.form.get("email", type=str, default="")
username = request.form.get("username", type=str, default="")
passwd = request.form.get("passwd", type=str, default="")
capture = request.form.get("capture", type=str, default="")
# 用户名，密码，验证码, 邮箱为空
if not (username and passwd and email and capture):
code = "-1"
msg = "用户输入了空参数"
# 查询本地是否有同名用户名和邮箱
else:
# 本地数据库不存在已注册相关用户名和邮箱信息,核实验证码注册
if not DB_Sync.exist_UserName_email(username, email):
captureCode = DB_Sync.query_redis_Capture(
email) # 如果email没找到,capturecode为None而不是""
if captureCode == capture:
code = "0"
msg = "注册成功"
# 新用户写入数据库
# 先对用户密码加密,需要盐
salt = create_Salt()
passwd = computePW(passwd, salt)
# 新用户入库
isOk = DB_Sync.sync_redis_insert(username, passwd, salt,
email)
if not isOk:
code = "-1"
msg = "新用户注册入库失败"
else:
# 刷新同步到redis
DB_Sync.refresh_redis(username)
session['isLogin'] = True
session['username'] = username
# 最后创建该用户的文件夹
iscreate, user_home_str = User_Action.create_user_homedir(
username)
if iscreate:
logger.info("为新注册用户%s创建文件夹成功" % username)
else:
logger.error("为新注册用户%s创建文件夹失败" % username)
else:
code = "-1"
msg = "验证码失效或错误"
# 本地已存在相关信息用户信息
else:
code = "-1"
msg = "用户名或邮箱已存在，请重新输入"
else:
code = '-1'
msg = '{} Method is not allowed !'.format(request.method)
resp = jsonify(code=code, msg=msg)
resp.set_cookie("datetime", getlocaltime())
return resp
def forgetPWD():
"""用户名和邮箱以及邮箱的验证码和新密码,查询本地用户名和邮箱是否对应，是发送验证码，然后检验验证码
:return: json结果
"""
email = request.form.get("email", type=str, default="")
username = request.form.get("username", type=str, default="")
captureCode = request.form.get("capture", type=str, default="")
new_passwd = request.form.get("new_passwd", type=str, default="")
# 本地数据库无对应数据，修改不了
if not DB_Sync.exist_UserName_email(username, email):
code = "-1"
msg = "用户名或者邮箱错误"
# 本地有数据可以修改,核对验证码
else:
# 判断根据邮箱查到的用户名是否一致,避免修改他人的数据
# 验证码一致,而且email和用户名也对应.(默认不考虑用户忘记用户名的情况)
isConsist = DB_Sync.is_consistent(email, username)
if captureCode == DB_Sync.query_redis_Capture(email) and isConsist:
result = DB_Sync.query_redis(username)
# 根据用户名查询到了结果(salt)
print(result)
salt = result["data"]['USER_SALT']
compute_new_pwd = computePW(new_passwd, salt)
isOK = DB_Sync.sync_redis_insert(username, compute_new_pwd, salt,
email)
print("is ok", isOK)
if isOK:
code = "0"
msg = "密码重置成功，请用新的密码登录"
logger.info("%s用户密码修改成功" % username)
# 修改密码后需要清除session数据
session["isLogin"] = False
session.clear()
if DB_Sync.refresh_redis(username):
print("redis刷新成功...")
else:
print("redis刷新失败...")
else:
code = "-1"
msg = "密码重置失败，请重试"
print("{0}用户重置密码失败".format(username))
logger.error("%s用户重置密码失败" % username)
else:
code = "-1"
msg = ("验证码失效" if isConsist else "用户名密码不一致")
logger.error("%s用户重置密码失败,原因:%s" % (username, msg))
resp = jsonify(code=code, msg=msg)
resp.set_cookie("datetime", getlocaltime())
return resp
def deleteUSER():
""" 管理员或者super用户在用户管理界面删除其他用户(低权限用户)
:return:
"""
tup = ("user", "admin", "super") # 用下标 0, 1, 2 来比较权限的大小！！
# 当前修改者属性
username = request.form.get("username", type=str, default="")
# 被修改者的属性
othername = request.form.get('othername', type=str, default="")
otheremail = request.form.get("otheremail", type=str, default="")
result = DB_Sync.query_redis(othername)
# print(result)
othertype = result["data"]["USER_TYPE"]
# 判断当前用户是否在线
if session.get("username") == username and session.get('isLogin'):
# 获取当前用户的权限和锁定情况
result = DB_Sync.query_redis(username)
usertype = result["data"]["USER_TYPE"]
userlock = result["data"]["USER_LOCK"]
# 判断当前用户是否有权限删除,自己删除不了自己
if userlock == 0 and tup.index(usertype) > tup.index(othertype):
is_delete = DB_Sync.sync_redis_delete(othername, otheremail)
if is_delete:
code = "0"
msg = "用户账号注销成功"
logger.info("%s管理员注销%s用户成功" % (username, othername))
# 用户删除成功时候，删除用户目录
if User_Action.remove_user_homedir(username):
logger.warning("注销删除%s用户文件目录数据成功" % username)
else:
logger.error("注销删除%s用户文件目录数据失败" % username)
else:
code = "-1"
msg = "用户账号注销失败"
logger.error("%s管理员注销%s用户失败" % (username, othername))
else:
code = "1"
msg = "您没有权限执行操作"
else:
code = "-1"
msg = "您当前不处于登录状态，请先登录"
resp = jsonify(code=code, msg=msg)
resp.set_cookie("datetime", getlocaltime())
return resp
def changeRole():
""" 改变用户的类型和锁定用户
:return: json结果
"""
tup = ("user", "admin", "super") # 用下标 0, 1, 2 来比较权限的大小！！
# 当前修改者属性
username = request.form.get("username", type=str, default="")
# 被修改者的新属性
othername = request.form.get("othername", type=str, default="")
new_othertype = request.form.get("new_othertype", type=str, default="user")
new_otherlock = request.form.get("new_otherlock", type=int, default=0)
# 如果当前用户处于登录状态
print(session["isLogin"], session.get("username"))
if session["isLogin"] and session.get("username") == username:
result = DB_Sync.query_redis(username)
usertype = result["data"]["USER_TYPE"]
userlock = result["data"]["USER_LOCK"]
result = DB_Sync.query_redis(othername)
othertype = result["data"]["USER_TYPE"]
# # userlock = int(userlock, encoding='utf-8')
# usertype = str(usertype, encoding='utf-8')
# othertype = str(othertype, encoding='utf-8')
# 当前用户不处于锁定状态，且当前用户的权限比被修改用户的权限高（能改）(这里就解决了自己修改自己的问题，自己只能降级自己)
# 且改动权限不能超过自己的当前权限（改的幅度）
if userlock == 0 and tup.index(othertype) < tup.index(usertype) and \
tup.index(new_othertype) <= tup.index(usertype):
ischanged = DB_Sync.change_user_permission(othername,
new_othertype,
new_otherlock)
if ischanged:
code = "0"
msg = "用户权限修改成功"
else:
code = "-1"
msg = "用户权限修改失败"
else:
code = "1"
msg = "您没有权限执行操作"
else:
code = "-1"
msg = "您当前不处于登录状态"
resp = jsonify(code=code, msg=msg)
resp.set_cookie("datetime", getlocaltime())
return resp
def deleteUserFile():
username = request.form.get("username", type=str, default="")
filename = request.form.get("filename", type=str, default="")
if username == "" or filename == "":
return jsonify(code="-1", msg="用户名或者文件名为空")
# 当前用户处于登陆状态
if session.get("username") == username and session.get("isLogin"):
if User_Action.remove_user_file(username, filename):
code = "0"
msg = "文件删除成功"
else:
code = "-1"
msg = "文件删除失败"
else:
code = "1"
msg = "当前处于非登陆状态，请先登录"
resp = jsonify(code=code, msg=msg)
resp.set_cookie("datetime", getlocaltime())
return resp
def eraseUSER():
""" 清空redis数据并删除本地数据库用户信息，同步redis
:return: json结果
"""
username = request.form.get("username", type=str, default="")
email = request.form.get("email", type=str, default="")
captureCode = request.form.get("capture", type=str, default="")
# 判断是否处于登录状态
if session["isLogin"] and session.get("username") == username:
# 判断验证码是否正确
isConsist = DB_Sync.is_consistent(email, username)
if captureCode == DB_Sync.query_redis_Capture(email) and isConsist:
# 删除redis数据，然后删除本地mysql数据
isDelete = DB_Sync.sync_redis_delete(username, email)
if isDelete:
code = "0"
msg = "用户账号注销成功"
session["isLogin"] = False
# 清除服务器数据
session.clear()
logger.info("%s用户账号注销成功" % username)
# 用户删除成功时候，删除用户目录
if User_Action.remove_user_homedir(username):
logger.warning("注销删除%s用户文件目录数据成功" % username)
else:
logger.error("注销删除%s用户文件目录数据失败" % username)
else:
code = "-1"
msg = "用户账号注销失败"
logger.warning("%s用户账户注销失败" % username)
else:
code = "-1"
msg = "验证码不一致"
logger.info("%s用户输入验证码不一致" % username)
# 当前不是处于登录状态
else:
code = "-1"
msg = "您当前不处于登录状态，请先登录"
logger.warning("%s用户删除数据失败,原因:当前处于非登录状态" % username)
resp = jsonify(code=code, msg=msg)
resp.set_cookie("datetime", getlocaltime())
return resp
def updatePWD():
"""Post方式用户更新密码,需要核实用户登录状态,用原来的salt加密后新密码入库,并要求重新登录。
:return: json结果
"""
username = session.get("username")
if session.get("isLogin") and username is not None:
# 处于登录的状态的时候，重新生成密码存储到mysql而且同步刷新Redis
old_pwd = request.form.get("old_passwd", type=str, default="")
new_pwd = request.form.get("new_passwd", type=str, default="")
# 下面获取该用户原始数据进行验证
result = DB_Sync.query_redis(username)
print("查询用户本地结果为:", result)
localPW = result["data"]["USER_PASSWD"]
salt = result["data"]["USER_SALT"]
# 用户原始密码输入正确，允许修改mysql然后同步redis
print('计算结果:', computePW(old_pwd, salt))
if checkPW(computePW(old_pwd, salt), localPW):
# 用原来的盐加密生成新的用户密码
compute_new_pwd = computePW(new_pwd, salt)
isOK = DB_Sync.sync_redis_insert(username, compute_new_pwd, salt)
if isOK:
resp = jsonify(code="0", msg="密码修改成功，请用新的密码登录")
resp.set_cookie("datetime", getlocaltime())
logger.warning("%s用户修改密码成功,并退出登录" % username)
# 修改了密码后,需要重新登录,应该清除session数据。然后刷新redis，不然会导致login时候的redis用之前的
DB_Sync.refresh_redis(username)
session["isLogin"] = False
session.clear()
else:
resp = jsonify(code="-1", msg="密码修改失败，请重试")
print("{0}用户修改密码失败,原密码:{1}|新密码:{2}".format(
username, localPW, compute_new_pwd))
logger.warning("%s用户修改密码失败" % username)
else:
# 传递过来的用户原始密码为假
resp = jsonify(code="-1", msg="您原始密码输入有误，请重新输入")
logger.error("%s用户修改密码失败,原始密码不正确" % username)
else:
# 该用户已经退出登录了，不处于登录状态
resp = jsonify(code="1", msg="您当前不处于登录状态，请先登录")
logger.warning("%s用户当前处于非登录状态,修改密码失败" % username)
return resp
def super_Handle_LogDB():
""" super清空errlogs.txt和删除db备份文件(正常来说应该保留最近一次备份的不允许删除,本项目不考虑此情况)
:return: json结果
"""
filename = request.form.get("filename", type=str, default="")
username = request.form.get("username", type=str, default="")
if session.get("isLogin") and session.get("username") == username:
result = DB_Sync.query_redis(username)
usertype = result["data"]["USER_TYPE"]
userlock = result["data"]["USER_LOCK"]
# 超级用户且没有被人为锁定
if usertype == "super" and userlock == 0:
# 如果是日志则清空
if filename == serverlogs_name:
if cleanLogging():
code = "0"
msg = "文件清空成功"
logger.info("%s用户删除日志成功，具有权限为%s，锁定为%d" %
(username, usertype, userlock))
else:
code = "1"
msg = "文件异常，无法清除"
logger.error("%s用户删除日志失败,原因:文件异常，无法清除" % username)
# 如果是备份文件则删除
else:
global serverlogs_path
User_Action.fileDelete(serverlogs_path +
r'\{}'.format(filename))
code = "0"
msg = "文件删除成功"
logger.info("%s用户删除文件%s成功，具有权限为%s，锁定为%d" %
(username, filename, usertype, userlock))
else:
code = "-1"
msg = "您没有权限执行该操作"
else:
code = "-1"
msg = "您当前不处于登录状态，请先登录"
resp = jsonify(code=code, msg=msg)
resp.set_cookie("datetime", getlocaltime())
return resp
def fetchDB():
"""查询mysql所有数据并返回
:return: 返回数据库表的结果
"""
db = pymysql.Connect(host=mysql_host, user='******', password=mysql_passwd,
database=mysql_base, charset='utf8')
# cursor 默认返回的结果是元组，可以指定返回list，里面是字典格式
cursor = db.cursor(cursor=pymysql.cursors.DictCursor)
sql = "SELECT * FROM {}".format(mysql_table)
try:
cursor.execute(sql)
db.commit()
result = cursor.fetchall()
except:
print("{} 备份数据库错误...", getlocaltime().decode('utf-8'))
db.rollback()
db.close()
print(type(result), result)
return result
def userList():
""" 获取数据库中用户列表除了password和salt
:return: json结果
"""
username = request.form.get("username", type=str, default="")
if session["isLogin"] and session.get("username") == username:
result = DB_Sync.query_redis(username)
userlock = result['data']['USER_LOCK']
if userlock == 0:
code = "0"
msg = "获取用户列表成功"
data = DB_Sync.user_list()
else:
return abort(401)
else:
code = "-1"
msg = "您当前不处于登录状态，请先登录"
data = ""
resp = jsonify(code=code, msg=msg, data=data)
resp.set_cookie("datetime", getlocaltime())
return resp
def first_request():
"""首次Get请求下发CSRF-TOKEN以及相关cookies数据
:return: json格式字符串
"""
# response = response()
# 调用函数生成 csrf_token; generate_csrf()里面在包里的session.py中
# hashlib.sha1(os.urandom(64)).hexdigest()
# s = URLSafeTimedSerializer(secret_key, salt='wtf-csrf-token')
csrf_token = generate_csrf()
# 通过 cookie 将值传给前端
result = {"code": 0, 'msg': 'success'}
resp = make_response(jsonify(result))
# 指定cookie放在/位置
resp.set_cookie("csrf_token", csrf_token, httponly=False, path='/')
resp.set_cookie('datetime', getlocaltime())
# 允许前端可以通过获取header的字段
resp.headers['Access-Control-Expose-Headers'] = 'csrf_token,Set-Cookie'
resp.headers['csrf_token'] = csrf_token
resp.headers['supportsCredentials'] = True
resp.headers['Access-Control-Allow-Origin'] = '*'
# resp.headers['Access-Control-Allow-Credentials'] = True
return resp
def myjob():
print('time: %s start to backup DataBase' % getlocaltime().decode('utf-8'))
importExcel()
def login():
""" 前端登录请求post请求，从数据库中验证params完整性和正确性,并设置相应的cookies和session
:return: json结果
"""
logintime = ""
if request.method == 'POST':
# 这里进行了折衷的处理为了实现登录顶掉他人登录的需求.(人性化的做法应该是提示在他处登录，询问要不要踢掉或者在其他地方先推出，这边在登陆)
quit()
print(request.form.get("username", type=str, default=""))
# 判断用户名密码是否正确
username = request.form.get("username", type=str, default="")
passwd = request.form.get("passwd", type=str, default="")
if username == "" or passwd == "":
code = "-1"
msg = "用户名或密码为空或不合法"
# 用户名不空查询后端数据
else:
result = DB_Sync.query_redis(username)
# 用户不存在
if result["code"] == "-1":
code = "-1"
msg = "用户名或密码错误"
# 用户存在 result["code"]返回0,1
else:
salt = ""
# 判断密码是否正确
if result["data"] != "":
salt = result["data"]["USER_SALT"]
localPW = result["data"]["USER_PASSWD"]
print("redis data", result["data"])
print("computePasswd", computePW(passwd, salt))
# 验算用户传递的密码是否和数据库中保存的一致
isConsistent = checkPW(computePW(passwd, salt), localPW)
# 密码输入正确
if isConsistent:
code = "0"
msg = "登录验证成功"
session["logintime"] = getlocaltime()
if session.get("isLogin"):
logger.warning("%s用户登录失败,原因是已在其他地方登录" % username)
return jsonify(code="-1", msg="您已在其他地方登录")
session["isLogin"] = True
session["username"] = username
logintime = getlocaltime()
logger.info("%s用户登录成功" % username)
# 密码输入错误
else:
code = "-1"
msg = "用户名或密码错误"
logger.warning("%s用户登录输入密码错误,登录失败" % username)
resp = jsonify(code=code, msg=msg)
resp.set_cookie('datetime', getlocaltime())
resp.set_cookie('logintime', logintime)
resp.set_cookie('username',
request.form.get("username", type=str, default="null"),
expires=max_ageTime) # max_age单位是秒，过期时间
return resp
# 如果是其他请求方法
else:
resp = jsonify(code='-1',
msg='{} Method is not allowed !'.format(request.method))
resp.set_cookie('datetime', getlocaltime())
resp.set_cookie('logintime', logintime)
resp.set_cookie('username',
request.form.get("username", type=str, default="null"),
expires=max_ageTime) # max_age单位是秒，过期时间
return resp
