def execute_insert_to_biaojiandatacalculation(dt):
try:
connection = sql.connect(
server_hostname=dbutils.widgets.get("sql_warehouse_server_hostname"),
http_path=dbutils.widgets.get("sql_warehouse_http_path_dm_urp"),
access_token=dbutils.secrets.get(scope="lakehouse", key="cdlurpdctoken"),
catalog=dbutils.widgets.get("databricks_catalog")
# catalog='hive_metastore'
)
print(dt)
print(f"def execute_insert_to_biaojiandatacalculation(dt):成功建立与数据库的连接，连接对象: {connection}")
cursor = connection.cursor()
# 获取批次开始日期
startdate = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
current_time = datetime.now()
print("分公司E类规则开始,当前时间:", current_time)
current_sql = ""
# # 查询规则中有效的上报类型规则清单 rulestatus=0是有效 runstates='0'是自动跑批
# select_query = f"select distinct eastsql from dm_urp_bui_dgs.rulecodetoeast2"
# select_query = f"select t1.rulesql from dm_urp_bui_dgs.ruleTypeConfig t1 inner join dm_urp_bui_dgs.rulecodetosys t3 on t1.rulecode=t3.rulecode where t1.rulestatus ='0' and t1.runstates = '0' and t1.module = 'EAST2' and t1.rulesource = '01' union all select distinct eastsql from dm_urp_bui_dgs.rulecodetoeast2 "
# select_query = f"select t1.rulesql from dm_urp_bui_dgs.ruleTypeConfig t1 inner join dm_urp_bui_dgs.rulecodetosys t3 on t1.rulecode=t3.rulecode where t1.rulestatus ='0' and t1.runstates = '0' and t1.module = 'EAST2' and t1.rulesource = '01' "
select_query = f"select distinct eastsql from dm_urp_bui_dgs.rulecodetoeast2 where {rulesource_fgs}"
cursor.execute(select_query)
print(f"执行查询分公司规则脚本的SQL为：{select_query}")
# 从查询结果中获取数据
query_results = cursor.fetchall()
# 创建一个用于并发执行的 ThreadPoolExecutor <根据需要调整 max_workers>
get_checkbatchno = ""
finalcheckbatchno='c_'+system_name_low+'_'+checkbatchno
select_batchno = f"select batchno from dm_urp_bui_east2.rds_reportflow where dt = '{dt}'"
print(select_batchno)
current_sql = select_batchno
cursor.execute(current_sql)
result = cursor.fetchone()
batchno = result[0]
futures = []
with ThreadPoolExecutor(max_workers=50) as executor:
# 为每个 SQL 语句提交任务
for row in query_results:
sql_statement = row[0]
sql_statement = sql_statement.replace(
"1 = 1 ?", f"1=1 and dt = '{dt}' and gsjgdm<>'000028'"
)
sql_statement = sql_statement.replace(
"'checkbatchno'",
f"concat('c_','{system_name_low}','_','{checkbatchno}')",
)
sql_statement = sql_statement.replace(
" t.BATCHNO = '{batchno}'", f" t.BATCHNO = '{batchno}'"
)
sql_statement = sql_statement.replace("'' as dt", f"'{dt}' as dt")
sql_statement = sql_statement.replace("t.BATCHNO = '{dt}'", f" BATCHNO = '{batchno}'" )
print("sql_statement: " + sql_statement)
try:
future = executor.submit(execute_query_and_insert, sql_statement)
print("检核执行成功的SQL语句: " + sql_statement)
futures.append(future)
except Exception as e:
err = ''
err = remove_special_characters(str(e))
errsql = "insert into dm_urp_bui_dgs.databrickserror values ('dm_urp_bui_all_datacheck_east2','" + err + "','358'," + '"' + sql_statement + '"' + ",'','" + formatted_date_minus_one + "')"
execute_query_and_insert1(errsql)
print(err)
continue
# concurrent.futures.wait(futures)
for future in as_completed(futures):
try:
future.result()
except Exception as e:
err = ''
err = remove_special_characters(str(e))
errsql = f"insert into dm_urp_bui_dgs.databrickserror values ('dm_urp_bui_all_datacheck_prip','{err}','355','','','{formatted_date_minus_one}')"
execute_query_and_insert1(errsql)
print(err)
print(f"检核执行错误的任务")
# updategljg = "insert OVERWRITE table dm_urp_bui_dgs.errordatadetail_east partition(dt='{}') select concat(a.id,';N') ,a.checkbatchno , a.module , a.ruleCode, a.state, a.problemcode , a.problemdessou , a.detaildata , a.issueid, a.innerdes, a.modifydate , a.checkDate , a.remark, a.datasrc , a.transno , a.valueno from dm_urp_bui_dgs.errordatadetail_east a where a.module='EAST2' and a.checkbatchno='{}' and a.dt='{}' and a.id not like '%;Y' union all select id ,checkbatchno , module , ruleCode, state, problemcode , problemdessou , detaildata , issueid, innerdes, modifydate , checkDate , remark, datasrc , transno , valueno from dm_urp_bui_dgs.errordatadetail_east a where a.checkbatchno<>'{}' and a.dt='{}' union all select id ,checkbatchno , module , ruleCode, state, problemcode , problemdessou , detaildata , issueid, innerdes, modifydate , checkDate , remark, datasrc , transno , valueno from dm_urp_bui_dgs.errordatadetail_east a where a.module='EAST2' and a.checkbatchno='{}' and a.dt='{}' and a.id like '%;Y' ".format(dt,finalcheckbatchno,dt,finalcheckbatchno,dt,finalcheckbatchno,dt)
# execute_query_and_insert(updategljg)
# 执行任务
current_time = datetime.now()
print("分公司规则加工结束,当前时间:", current_time)
# connection.commit()
# 关闭查询用的游标
cursor.close()
# 关闭数据库连接
connection.close()
except Exception as e:
err = ''
err = remove_special_characters(str(e))
errsql = "insert into dm_urp_bui_dgs.databrickserror values ('dm_urp_bui_all_datacheck_east2','" + err + "','379'," + '"' + current_sql + '"' + ",'','" + formatted_date_minus_one + "')"
execute_query_and_insert1(errsql)
print(err)
raise Exception() 这一块代码什么意思
最新发布
解决死循环的方法
本文讨论了一个常见的编程问题——死循环，并提供了解决方案。通过在continue之前加入fetchnextfrom操作，可以避免循环陷入无限循环的状态。
扫一扫
4585
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
