def select_received(t_name="", t_cname=""):
sql = "select t_name,t_cname,user_name,submit_date,src_system from received_table "
sql_param = "where 1=1"
if t_name != "":
sql_param += " and t_name like '%" + t_name + "%'"
if t_cname != "":
sql_param += " and t_cname like '%" + t_cname + "%'"
sql = sql + sql_param + " order by submit_date desc"
conn = get_mysql_connect()
cur = conn.cursor()
cur.execute(sql)
data = cur.fetchall()
received_list: List[TableInfo] = list()
for d in data:
t_name = d[0]
t_cname = d[1]
user_name = d[2]
submit_date = d[3]
src_system = d[4]
t = TableInfo(t_name, t_cname, src_system, 0, list(), submit_date, user_name)
received_list.append(t)
cur.close()
conn.close()
return received_list
def execute_sql(sql):
conn = get_mysql_connect()
cur = conn.cursor()
cur.execute(sql)
data: tuple = cur.fetchall()
conn.commit()
cur.close()
conn.close()
return data
def mysql_connect_select(sql):
conn = get_mysql_connect()
cur = conn.cursor()
cur.execute(sql)
data = cur.fetchall()
received_list = []
for d in data:
received_list.append(d[0])
cur.close()
conn.close()
return received_list
def select_src_system(src_system):
sql = "select count(*) from src_system where app_short = '" + src_system + "'"
print(sql)
conn = get_mysql_connect()
cur = conn.cursor()
cur.execute(sql)
data = cur.fetchone()
count = data[0]
conn.commit()
cur.close()
conn.close()
return count
def generate_meta(t_name='', src_system=''):
conn = get_mysql_connect()
# db_info: DbInfo = get_info(src_table_en_name, src_system_en_name)
if t_name == '' and src_system == '':
received_list = get_table_info()
else:
received_list = get_table_info(t_name, src_system)
need_generate_count = len(received_list)
generate_count = 0
generate_list = list()
for item in received_list:
t_id = item[0]
t_name = item[1]
src_system = item[2].rstrip()
applicant = item[3]
print("源表:%s 源系统:%s 申请人:%s" % (t_name, src_system, applicant))
meta = MetaGenerate()
meta.t_id = t_id
meta.t_name = t_name
meta.src_system = src_system
meta.applicant = applicant
meta = query_src_table_info(meta, conn)
db_info: DbInfo = get_info(meta.src_table_en_name,
meta.src_system_en_name)
# meta.generate_state = '未生成'
res_tag, generate_state = generate_meta_data(db_info, conn, meta)
meta.generate_state = generate_state
generate_list.append(meta)
if res_tag == 'success':
generate_count += 1
return need_generate_count, generate_count, generate_list
def received_to_processed(insert_list: List[ProcessedInfo]):
conn = get_mysql_connect()
cur = conn.cursor()
for t in insert_list:
# 对更新标志为update的表进行更新
if t.update_tag == 'update':
delete_sql = "delete from etl_check.received_table where t_name='" + t.t_name + "' and src_system='" \
+ t.src_system + "'"
insert_sql = "insert into etl_check.processed_table (t_name,src_system,des_system) values ('" + t.t_name \
+ "','" + t.src_system + "','" + t.des_system + "')"
print(delete_sql)
cur.execute(delete_sql)
count = cur.rowcount
if count > 0:
print(insert_sql)
cur.execute(insert_sql)
t.update_tag = 'success'
conn.commit()
else:
t.update_tag = "error"
t.update_msg = "already updated"
cur.close()
conn.close()
return insert_list
def get_db_conn():
return get_mysql_connect()