def execute_py_shell(unusual_shell, unusual_id, mode='activate'):
"""
执行检查点的 python shell 算法
:param unusual_shell:
:return:
"""
try:
# eval("print(1+2)")
# exec('1/0')
#log.info(unusual_shell)
#exec("print('执行算法 shell 开始')")
log.info('执行算法 shell 开始')
daily_start_date = get_current_time()
daily_id = insert_finance_shell_daily(
daily_status='ok',
daily_start_date=daily_start_date,
daily_end_date='',
unusual_point=unusual_id,
daily_source='python shell',
operate_desc=f'正在执行检查点{unusual_id}的Python Shell',
unusual_infor='',
task_status='doing',
daily_type='稽查点')
exec(unusual_shell, globals())
#exec("print('执行算法 shell 结束')")
log.info('执行算法 shell 结束')
daily_end_date = get_current_time()
operate_desc = f'成功执行检查点{unusual_id} 的Python Shell'
update_finance_shell_daily(daily_id,
daily_end_date,
task_status='done',
operate_desc=operate_desc)
except BaseException as e:
#log.info('***1111 execute_py_shell throw BaseException ---')
# print(e)
error_info = str(e)
log.info(error_info)
traceback.print_exc()
daily_end_date = get_current_time()
update_finance_shell_daily(daily_id,
daily_end_date,
task_status='error',
operate_desc=error_info)
raise RuntimeError(error_info)
except SyntaxError as e2:
log.info('***2222 execute_py_shell throw Exception ---')
print(e2)
def demo1():
for i in range(1):
daily_status = 'ok'
daily_start_date = '2021-11-08 17:05'
daily_end_date = '2021-11-08 20:05'
unusual_point = '2'
daily_source = 'sql'
operate_desc = '1' + str(i)
unusual_infor = 'aaabbbccc'
task_status = 'done'
daily_id = insert_finance_shell_daily(daily_status, daily_start_date,
daily_end_date, unusual_point,
daily_source, operate_desc,
unusual_infor, task_status)
print(daily_id)
update_finance_shell_daily(daily_id,
task_status='done 222333',
daily_end_date='aaaaa')
# pagination_finance_shell_daily_records(unusual_point='1')
# unusual_id = '42'
# category_names = ['d'] # ['a' , 'b']
# category_classify = '001'
# update_finance_category_sign(unusual_id, category_names, category_classify)
# category_classify = '2'
# records = query_finance_category_sign(unusual_id=unusual_id, category_classify=category_classify)
# print(records)
# province_service = ProvinceService()
# area_name = '金湖县'
# province_service.query_province(area_name)
# area_id = '510000'
# area_id, area_name, parent_id, grade = province_service.query_previous_province(query_area_id=area_id)
# print(area_id, area_name, parent_id, grade)
# area_name = '南川区'
# province_name = province_service.query_belong_province(area_name)
# print('province_name=',province_name)
# city_name = province_service.query_receipt_city(area_name='房山区')
# print(f'city_name={city_name}')
print('--- ok ---')
def exec_temp_performance_bill(performance_ids, is_log=True):
try:
daily_start_date = get_current_time()
if is_log:
daily_id = insert_finance_shell_daily(
daily_status='ok',
daily_start_date=daily_start_date,
daily_end_date='',
unusual_point='',
daily_source='sql',
operate_desc=f'doing',
unusual_infor='',
task_status='doing',
daily_type='绩效')
records = query_temp_performance_bill(performance_ids)
# print(len(records), records)
for idx, record in enumerate(records):
performance_sql = record[0]
# print(performance_sql)
prod_execute_sql(conn_type=CONN_TYPE,
sqltype='insert',
sql=performance_sql)
if is_log:
operate_desc = f'成功执行绩效表中的SQL'
daily_end_date = get_current_time()
update_finance_shell_daily(daily_id,
daily_end_date,
task_status='done',
operate_desc=operate_desc)
except Exception as e:
print(e)
if is_log:
error_info = str(e)
daily_end_date = get_current_time()
update_finance_shell_daily(daily_id,
daily_end_date,
task_status='error',
operate_desc=error_info)
raise RuntimeError(e)
def execute_kudu_sql(unusual_shell, unusual_id):
try:
daily_start_date = get_current_time()
#print('*** begin execute_kudu_sql ')
#log.info(unusual_shell)
daily_id = insert_finance_shell_daily(
daily_status='ok',
daily_start_date=daily_start_date,
daily_end_date='',
unusual_point=unusual_id,
daily_source='sql',
operate_desc=f'正在执行检查点{unusual_id}的SQL',
unusual_infor='',
task_status='doing',
daily_type='稽查点')
prod_execute_sql(conn_type=CONN_TYPE,
sqltype='insert',
sql=unusual_shell)
daily_end_date = get_current_time()
operate_desc = f'成功执行检查点{unusual_id}的SQL'
#print('*** end execute_kudu_sql ***')
daily_end_date = get_current_time()
update_finance_shell_daily(daily_id,
daily_end_date,
task_status='done',
operate_desc=operate_desc)
except Exception as e:
print(e)
error_info = str(e)
daily_end_date = get_current_time()
update_finance_shell_daily(daily_id,
daily_end_date,
task_status='error',
operate_desc=error_info)
raise RuntimeError(error_info)
def exec_temp_api_bill_sql_by_target(target_classify, is_log=True):
"""
执行临时表API表,
:param target_classify : 目标分类，主要包括：差旅费、会议费、办公费、车辆使用费
:return:
"""
try:
log.info(
f'*** exec_temp_api_bill_sql_by_target, 执行 {target_classify} 的绩效SQL ***'
)
sql = f"""
select tem_api_id,target_classify,api_sql from 01_datamart_layer_007_h_cw_df.temp_api_bill
where target_classify="{target_classify}" order by order_number asc
"""
temp_api_sql_records = prod_execute_sql(conn_type=CONN_TYPE,
sqltype='select',
sql=sql)
#log.info(f'需要执行 {len(temp_api_sql_records)} 条SQL')
if is_log:
daily_start_date = get_current_time()
daily_id = insert_finance_shell_daily(
daily_status='ok',
daily_start_date=daily_start_date,
daily_end_date='',
unusual_point='',
daily_source='sql',
operate_desc=f'正在执行临时表API中类型为{target_classify}的SQL',
unusual_infor='',
task_status='doing',
daily_type='数据处理')
for idx, record in enumerate(temp_api_sql_records):
tem_api_id = str(record[0])
api_sql = str(record[2])
# log.info(api_sql)
prod_execute_sql(conn_type=CONN_TYPE,
sqltype='insert',
sql=api_sql)
log.info(
f'target_classify={target_classify},执行成功tem_api_id为 {tem_api_id} 的临时表的SQL,共有{len(temp_api_sql_records)}条SQL'
)
if is_log:
operate_desc = f'成功执行临时表API中类型为{target_classify}的SQL'
daily_end_date = get_current_time()
update_finance_shell_daily(daily_id,
daily_end_date,
task_status='done',
operate_desc=operate_desc)
except Exception as e:
log.error(
f'执行失败，target_classify={target_classify},tem_api_id为 {tem_api_id} 的临时表的SQL '
)
#print(e)
if is_log:
error_info = str(e)
daily_end_date = get_current_time()
update_finance_shell_daily(daily_id,
daily_end_date,
task_status='error',
operate_desc=error_info)
raise RuntimeError(e)