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