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