def setup_db_operation(sql, db_connect): """ 数据库-执行SQL语句 : sql :desc: 说明: 执行SQL语句 :param sql:sql: 需要执行的SQL语句,支持多条SQL按;分隔 :return: """ sql_execute(sql, db_connect=db_connect)
def get_random_member_id(db_connect): """ :desc: :return: """ random_sec = random.randint(0, 10000) new_last_gen_time = datetime.datetime.strptime("2017-11-14 17:46:06", "%Y-%m-%d %H:%M:%S") + datetime.timedelta(seconds=random_sec) new_last_gen_time = new_last_gen_time.strftime("%Y-%m-%d %H:%M:%S") while True: return_info = sql_execute("", db_connect=db_connect) max_member_id = return_info[0][0] + 1 last_gen_time = return_info[0][1].strftime("%Y-%m-%d %H:%M:%S") update_sequence = "" % (str(max_member_id), new_last_gen_time, last_gen_time) return_info = sql_execute(update_sequence, db_connect=db_connect) if return_info == 1: break return int(max_member_id)
def teardown_update_capital_no_list(order_no, capital_no_list, db_connect): """ 数据库-资方匹配列表中去掉给定的资方 : order_no, capital_no_list """ if isinstance(capital_no_list, basestring): capital_no_list = json.loads(capital_no_list) try: get_current_capital_no_list_sql = "SELECT FROM w WHERE " % (order_no) current_capital_no_list = json.loads(sql_execute(get_current_capital_no_list_sql, db_connect=db_connect)[0][0]) update_capital_no_list = [o for o in current_capital_no_list if o not in capital_no_list] set_capital_no_list_sql = "" % (json.dumps(update_capital_no_list), order_no) sql_execute(set_capital_no_list_sql, db_connect=db_connect) return True except Exception as err: raise Exception("更新资方匹配列表失败: {0}".format(repr(err)))
def setup_fund_order_conf(args, db_connect): """ 数据库-配置资金方匹配规则 : 规则参数 :desc: 说明: 写入capital.conf_order_fund表,设置资金方匹配规则 :param args: 规则参数: 例如 {"idNo":"$ID_NO","applyAmount":"$applyAmount","termNo":"$termValue","repaymentWay":"554","fund":"$fund_id"} :return: """ id_no = args['idNo'] order_amount = args['applyAmount'] term_no = args['termNo'] repayment_way = args['repaymentWay'] fund = args['fund'] industry_type = args.pop('industry_type', None) is_accept_unclear = args.pop('is_accept_unclear', None) fund_level = args.pop('fund_level', None) product_type = args.pop('productType', 0) sql_user_age = "".format( id_no=id_no) user_age = sql_execute(sql_user_age, db_connect=db_connect) max_user_age = user_age[0][0] min_user_age = user_age[0][1] # sql_min_id = "SELECT MIN(id)-1 FROM capital.`conf_order_fund`;" # min_id = sql_execute(sql_min_id, db_connect=db_connect)[0][0] min_id = "" now_time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S') industry_type = industry_type if industry_type else '100013' is_accept_unclear = is_accept_unclear if is_accept_unclear else '10' fund_level = fund_level if fund_level else '0' # 检查规则是否唯一 sql_check_unique = "".format(term_no=term_no, order_amount=order_amount, fund_level=fund_level, repayment_way=repayment_way, fund=fund) res = sql_execute(sql_check_unique, db_connect=db_connect) if res[0][0] > 0: return True, '已有相同资金方匹配规则, 未新写入规则' sql_fund_order_conf = "".format(min_id=min_id,order_amount=order_amount,min_user_age=min_user_age,max_user_age=max_user_age,term_no=term_no,repayment_way=repayment_way,fund=fund,create_time=now_time,industry_type=industry_type,is_accept_unclear=is_accept_unclear,fund_level=fund_level, product_type=product_type) sql_execute(sql_fund_order_conf, db_connect=db_connect)
def get_random_phone_no(top, db_connect): """ 生成-随机手机号码 : 前三位 :desc: 说明: 随机生成数据库中不存在的手机号码 :param top:前三位: 例如 139 :return: """ while True: rand_phone = generate_phone(top) return_info = sql_execute("" % rand_phone, db_connect=db_connect) if return_info[0][0] == 1: continue else: break return rand_phone
def teardown_wait_until_db_result_succeed(wait_time, sql, expect_value, db_connect): """ 数据库-轮询SQL等待结果为期望值 : wait_time, sql, expect_value :desc: 说明: 等待时间内,轮询查询SQL,直到SQL结果等于期望值 :param wait_time: wait_time:等待时间秒数,例如 15 :param sql: sql:需要执行的查询的SQL :param expect_value: expect_value:预期结果 :return: """ wait_time = int(wait_time) if wait_time > wait_max_time: wait_time = wait_max_time for time_no in range(wait_time): return_info = sql_execute(sql, db_connect=db_connect) if len(return_info) != 0: if str(return_info[0][0]) == str(expect_value): return True time.sleep(wait_time_sec) return False
def setup_wait_until_db_result_succeed(wait_time, sql, expect_value, db_connect): """ 数据库-轮询SQL等待结果为期望值 : wait_time, sql, expect_value :desc: 说明: 等待时间内,轮询查询SQL,直到SQL结果等于期望值 :param wait_time: wait_time:等待时间秒数,例如 15 :param sql: sql:需要执行的查询的SQL :param expect_value: expect_value:预期结果 :return: """ wait_time = int(wait_time) if wait_time > wait_max_time: wait_time = wait_max_time for time_no in range(wait_time): return_info = sql_execute(sql, db_connect=db_connect) if len(return_info) != 0: if str(return_info[0][0]) == str(expect_value): return True time.sleep(wait_time_sec) raise Exception('{0}秒内执行sql未返回期望结果{1}'.format(str(wait_time), str(expect_value)))
def variable_db_operation(sql, db_connect): res = sql_execute(sql, db_connect=db_connect) if res: # 支持查询结果是多条数据的情况 if len(res) == 1: if len(res[0]) == 1: return convert_mysql_datatype_to_py(res[0][0]) else: return db_operation_to_json(sql, db_connect=db_connect, return_info=res) else: sql_multi_result = [] for res_item in res: if len(res_item) == 1: sql_multi_result.append(convert_mysql_datatype_to_py(res_item[0])) else: sql_multi_result.append(db_operation_to_json(sql, db_connect=db_connect, return_info=res_item, multi=True)) return sql_multi_result else: return 'variable sql return no result!'
def get_access_token(phone, base_host, redis_connect, db_connect): """ 登录-手机号动码登录获取accessToken : 手机号 :desc: 说明: 指定手机号,依次调用获取验证码和动码登录接口,返回accessToken :param phone:手机号: 例如 18555555555 :return: """ # 根据手机号获取memberid # 根据memberid获取token try: _redis_connect = json.loads(redis_connect) except Exception as err: raise Exception('环境信息中redis连接配置错误') redis_host = _redis_connect['host'] redis_port = _redis_connect['port'] redis_password = _redis_connect['password'] get_member_id_sql = "" % phone ret = sql_execute(get_member_id_sql, db_connect=db_connect) if ret and ret[0][0]: member_id = ret[0][0] with RedisUtils(redis_host, redis_port, redis_password, 2) as r: user_info = r.get_str_value('user_token_%s' % (str(member_id))) if isinstance(user_info, dict): return user_info['accessToken'] hr_logger.log_info('【执行函数{0}】Redis中获取不到手机号【{1}】的accessToken数据,将调用动码登录接口获取' .format(inspect.stack()[0][3], phone)) current_timestamp = int((time.time() * 1000)) heard_info = {'Content-Type': 'application/json;charset=utf-8'} send_code_url = base_host + '/user/v2/common/sendMobileCode' send_code_input = { "appld": "6916984", "phone": str(phone), "sourceFlag": "1", "timestamp": current_timestamp, } # 加签 send_code_input = get_sign_common(send_code_input) # 获取验证码 hc = HttpClient() try: response = hc.http_post(send_code_url, heard_info, send_code_input) except Exception as err: raise err res_dic = json.loads(response.text) hr_logger.log_info('【执行函数{0}】获取验证码接口返回内容{1}'.format(inspect.stack()[0][3], response.text)) if 'content' not in res_dic: raise Exception('【执行函数{0}】调用获取验证码接口没有返回content字段,接口返回内容{1}' .format(inspect.stack()[0][3], response.text)) if 'serialNo' not in res_dic['content']: raise Exception('【执行函数{0}】调用获取验证码接口没有返回serialNo字段,接口返回内容{1}' .format(inspect.stack()[0][3], response.text)) serial_no = eval(res_dic['content'])['serialNo'] login_url = base_host + '/user/login/v3/phoneLogin' login_input = { "phone": str(phone), "smsSerialNo": serial_no, "timestamp": current_timestamp, "verifyCode": "8888", } # 加签 login_input = get_sign_common(login_input) # 动码登录 try: response = hc.http_post(login_url, heard_info, login_input) except Exception as err: raise err res_dic = json.loads(response.text) hr_logger.log_info('【执行函数{0}】动码登录接口返回内容{1}'.format(inspect.stack()[0][3], response.text)) if 'accessToken' not in res_dic: raise Exception('调用登录接口没有返回accessToken字段,接口返回内容{0}'.format(response.text)) access_token = res_dic['accessToken'] if not access_token: raise Exception('动码登录失败,具体原因请参考上面日志中动码登录接口返回内容') return access_token
def _extract_field_with_delimiter(self, field, context_obj=None): """ response content could be json or html text. @param (str) field should be string joined by delimiter. e.g. "status_code" "headers" "cookies" "content" "headers.content-type" "content.person.name.first_name" 含用例内变量 "123$phoneNo" 查询SQL "SELECT NEXT_VALUE FROM user_db.sequence WHERE SEQ_NAME='$MEMBER_ID';" """ # [:] sub_str_exp = None if field.endswith(']') and '[' in field and ':' in field.split( '[')[-1]: sub_str_exp = '[' + field.split('[')[-1] field = field.strip(sub_str_exp) # 支持提取变量步骤中写查询sql,查询结果保存为变量 if str(field).lower().startswith("select "): db_connect_content = '$DB_CONNECT' parsed_db_connect = context_obj.eval_content(db_connect_content) if parser.extract_variables(field): sql = context_obj.eval_content(field) else: sql = field from atp.api.mysql_sql_executor import sql_execute, db_operation_to_json from atp.utils.tools import convert_mysql_datatype_to_py try: res = sql_execute(sql, db_connect=parsed_db_connect) except Exception as err: raise if res: # 支持查询结果是多条数据的情况 if len(res) == 1: if len(res[0]) == 1: res_value = convert_mysql_datatype_to_py(res[0][0]) else: res_value = db_operation_to_json( sql, db_connect=parsed_db_connect, return_info=res) else: res_value = [] for res_item in res: if len(res_item) == 1: res_value.append( convert_mysql_datatype_to_py(res_item[0])) else: res_value.append( db_operation_to_json( sql, db_connect=parsed_db_connect, return_info=res_item, multi=True)) else: res_value = 'variable sql return no result!' # res_value = res[0][0] if res else "DB query returns EMPTY result!" # if isinstance(res_value, decimal.Decimal): # res_value = float(res_value) return res_value, sub_str_exp # string.split(sep=None, maxsplit=-1) -> list of strings # e.g. "content.person.name" => ["content", "person.name"] try: top_query, sub_query = field.split('.', 1) except ValueError: top_query = field sub_query = None # status_code if top_query in ["status_code", "encoding", "ok", "reason", "url"]: if sub_query: # status_code.XX err_msg = u"Failed to extract: {}\n".format(field) logger.log_error(err_msg) raise exceptions.ParamsError(err_msg) return getattr(self, top_query), sub_str_exp # cookies elif top_query == "cookies": cookies = self.cookies.get_dict() if not sub_query: # extract cookies return cookies, sub_str_exp try: return cookies[sub_query], sub_str_exp except KeyError: err_msg = u"Failed to extract cookie! => {}\n".format(field) err_msg += u"response cookies: {}\n".format(cookies) logger.log_error(err_msg) raise exceptions.ExtractFailure(err_msg) # elapsed elif top_query == "elapsed": available_attributes = u"available attributes: days, seconds, microseconds, total_seconds" if not sub_query: err_msg = u"elapsed is datetime.timedelta instance, attribute should also be specified!\n" err_msg += available_attributes logger.log_error(err_msg) raise exceptions.ParamsError(err_msg) elif sub_query in ["days", "seconds", "microseconds"]: return getattr(self.elapsed, sub_query), sub_str_exp elif sub_query == "total_seconds": return self.elapsed.total_seconds(), sub_str_exp else: err_msg = "{} is not valid datetime.timedelta attribute.\n".format( sub_query) err_msg += available_attributes logger.log_error(err_msg) raise exceptions.ParamsError(err_msg) # headers elif top_query == "headers": headers = self.headers if not sub_query: # extract headers return headers, sub_str_exp try: return headers[sub_query], sub_str_exp except KeyError: err_msg = u"Failed to extract header! => {}\n".format(field) err_msg += u"response headers: {}\n".format(headers) logger.log_error(err_msg) raise exceptions.ExtractFailure(err_msg) # response body elif top_query in ["content", "text", "json"]: try: body = self.json except exceptions.JSONDecodeError: body = self.text if not sub_query: # extract response body return body, sub_str_exp if isinstance(body, dict): # content = {"xxx": 123}, content.xxx '''如果body中content是字符串类型'content': "{'headImageUrl':'','isRegister':0,'nickName':''}" 转换成字典,然后'extract': [{'headImageUrl':"content.content.isRegister"}]可提取 ''' # if "content" in body.keys() and '{' in body["content"]: # 修复bug:如果body["content"]是NoneType,报错“TypeError: argument of type 'NoneType' is not iterable ” # if "content" in body.keys() and body["content"] and '{' in body["content"]: # body_content_dict=json.loads(body["content"].replace("'", "\"")) # body["content"]=body_content_dict # 修复bug:"[]"未被json.loads if "content" in body.keys() and body["content"] and isinstance( body["content"], str): try: body_content_dict = json.loads(body["content"].replace( ' style="text-align: center;text-indent: 0;"', '').replace("'", "\"")) body["content"] = body_content_dict except (TypeError, json.decoder.JSONDecodeError) as e: # logger.log_error(body["content"].replace("'", "\"")) logger.log_error('\n'.join([e, traceback.format_exc()])) return utils.query_json(body, sub_query), sub_str_exp elif sub_query.isdigit(): # content = "abcdefg", content.3 => d return utils.query_json(body, sub_query), sub_str_exp else: # content = "<html>abcdefg</html>", content.xxx err_msg = u"Failed to extract attribute from response body! => {}\n".format( field) err_msg += u"response body: {}\n".format(body) logger.log_error(err_msg) raise exceptions.ExtractFailure(err_msg) # new set response attributes in teardown_hooks elif top_query in self.__dict__: attributes = self.__dict__[top_query] if not sub_query: # extract response attributes return attributes, sub_str_exp if isinstance(attributes, (dict, list)): # attributes = {"xxx": 123}, content.xxx return utils.query_json(attributes, sub_query), sub_str_exp elif sub_query.isdigit(): # attributes = "abcdefg", attributes.3 => d return utils.query_json(attributes, sub_query), sub_str_exp else: # content = "attributes.new_attribute_not_exist" err_msg = u"Failed to extract cumstom set attribute from teardown hooks! => {}\n".format( field) err_msg += u"response set attributes: {}\n".format(attributes) logger.log_error(err_msg) raise exceptions.TeardownHooksFailure(err_msg) elif context_obj and parser.extract_variables(top_query): # 表达式带已知变量,保存为新的变量 # ha$phone => ha18551602992 return context_obj.eval_content(top_query), sub_str_exp # others else: err_msg = u"Failed to extract attribute from response! => {}\n".format( field) err_msg += u"available response attributes: status_code, cookies, elapsed, headers, content, text, json, encoding, ok, reason, url.\n\n" err_msg += u"If you want to set attribute in teardown_hooks, take the following example as reference:\n" err_msg += u"response.new_attribute = 'new_attribute_value'\n" logger.log_error(err_msg) raise exceptions.ParamsError(err_msg)
def __eval_check_item(self, validator, resp_obj): """ evaluate check item in validator. Args: validator (dict): validator {"check": "status_code", "comparator": "eq", "expect": 201} {"check": "$resp_body_success", "comparator": "eq", "expect": True} resp_obj (object): requests.Response() object Returns: dict: validator info { "check": "status_code", "check_value": 200, "expect": 201, "comparator": "eq" } """ check_item = validator["check"] expect_value = self.eval_content( transfer_json_string_to_dict(validator["expect"])) if validator['comparator'] == 'db_validate': # format 6 db_connect_content = '$DB_CONNECT' check_item = check_item.strip(db_connect_content) if parser.extract_variables(check_item): check_item = self.eval_content(check_item) res = mysql_sql_executor.sql_execute( check_item, db_connect=self.eval_content(db_connect_content)) check_value = res[0][0] if res else "DB query returns EMPTY result!" validator["check"] = check_item elif validator['comparator'] == 'db_json_validate': # format 6 db_connect_content = '$DB_CONNECT' check_item = check_item.strip(db_connect_content) if parser.extract_variables(check_item): check_item = self.eval_content(check_item) try: wait_time = int(check_item.split(';')[1]) check_item = check_item.split(';')[0] + ';' except (IndexError, ValueError): wait_time = 30 res = mysql_sql_executor.db_operation_to_json_cycle( check_item, db_connect=self.eval_content(db_connect_content), expect_value=expect_value, wait_time=wait_time) check_value = res if res is not None else "DB query returns EMPTY result!" validator["check"] = check_item elif validator['comparator'] == 'db_validate_cycle': # format 6 db_connect_content = '$DB_CONNECT' check_item = check_item.strip(db_connect_content) if parser.extract_variables(check_item): check_item = self.eval_content(check_item) try: wait_time = int(check_item.split(';')[1]) check_item = check_item.split(';')[0] + ';' except (IndexError, ValueError): wait_time = 30 res = mysql_sql_executor.sql_execute_cycle( check_item, db_connect=self.eval_content(db_connect_content), expect_value=expect_value, wait_time=wait_time) check_value = res if res is not None else "DB query returns EMPTY result!" validator["check"] = check_item # 增加redis校验 elif validator['comparator'] == 'redis_validate': db_num = self.eval_content( json.loads(validator['check'])['db_num']) redis_key = self.eval_content( json.loads(validator['check'])['redis_key']) redis_connect = self.eval_content('$REDIS_CONNECT') try: _redis_connect = json.loads(redis_connect) except Exception as err: raise Exception('环境信息中redis连接配置错误') redis_host = _redis_connect['host'] redis_port = _redis_connect['port'] redis_password = _redis_connect['password'] with RedisUtils(redis_host, redis_port, redis_password, db_num) as r: check_value = r.get_str_value(redis_key) # 增加mq校验 elif validator['comparator'] == 'mq_validate': topic = self.eval_content(json.loads(validator['check'])['topic'] ) + '_' + self.eval_content('$ENV_NAME') tag = self.eval_content(json.loads(validator['check'])['tag']) system_name = self.eval_content( json.loads(validator['check'])['system_name']) server_app_map = json.loads(self.eval_content('$SERVER_APP_MAP')) server_default_user = json.loads( self.eval_content('$SERVER_DEFAULT_USER')) server_host = None for k, v in server_app_map.items(): if system_name in v: server_host = k break if not server_host: raise Exception('环境配置中找不到指定的系统名称 {0}'.format(system_name)) server_info = [ server_host, "22", server_default_user['user'], server_default_user['password'] ] gc = GetContenFromLog(system_name, server_info) check_value = list(gc.get_mq_log(topic, tag)) elif isinstance(check_item, (dict, list)) \ or parser.extract_variables(check_item) \ or parser.extract_functions(check_item): # format 1/2/3 check_value = self.eval_content(check_item) else: # format 4/5 check_value = resp_obj.extract_field(check_item) from atp.utils.tools import convert_mysql_datatype_to_py validator["check_value"] = convert_mysql_datatype_to_py(check_value) validator["expect"] = expect_value validator["check_result"] = "unchecked" return validator