def import2mysql():
sql_trun="truncate table gjyf_kpis.%s"%(tb_name)
print(sql_trun)
ai_cur.execute(sql_trun)
ai_cur.execute('commit')
sql="""load data local infile '%s.txt' replace into table gjyf_kpis.%s fields terminated by ',' enclosed by "'" (%s) """%(tb_name,tb_name,cols)
print(sql)
ai_cur.execute(sql)
ai_cur.execute('commit')
def gjyf_data(pro_name):
ai_cur.execute("delete from %s.%s where view_date='%s' and pro_name='%s'" %
(chk_db, chk_tb, dodate, pro_name))
sql = """
SELECT table_name FROM information_schema.tables WHERE table_schema='%s'
""" % (pro_name)
print(sql)
ai_cur.execute(sql)
for tables in ai_cur.fetchall():
td_flag = -1
try:
tb_name = tables[0]
print(tb_name)
sql1 = """
select count(*) from %s.%s
""" % (pro_name, tb_name)
ai_cur.execute(sql1)
tmp = ai_cur.fetchall()
table_rows = tmp[0][0]
if df[df['table_name'] == tb_name].empty:
td_flag = 0
else:
index = df[df['table_name'] == tb_name].index.tolist()
print(index)
print(df[df['table_name'] == tb_name]['trade_date_col'])
print('---------------')
print(df[df['table_name'] == tb_name]['trade_date_col'])
td_col = df[df['table_name'] ==
tb_name]['trade_date_col'][index].tolist()[0]
print('---------------')
print(td_col)
sql_ut_val = """
select max(%s) from %s.%s
""" % (td_col, pro_name, tb_name)
ai_cur.execute(sql_ut_val)
timedata = ai_cur.fetchall()
td_val = timedata[0][0]
sql_td = """
SELECT count(1) FROM information_schema.COLUMNS WHERE table_schema='%s' AND column_name='update_time' and TABLE_NAME = '%s'
""" % (pro_name, tb_name)
ai_cur.execute(sql_td)
flag_td = ai_cur.fetchall()
ftd = flag_td[0][0]
print(ftd)
if ftd == 1:
sql_td_val = """
select max(update_time) from %s.%s
""" % (pro_name, tb_name)
ai_cur.execute(sql_td_val)
ut_val = ai_cur.fetchall()[0][0]
if td_flag == 0:
sql2 = """
insert into %s.%s set table_name='%s',pro_name='%s',view_date='%s',table_rows='%s',last_update_time='%s'
""" % (chk_db, chk_tb, tb_name, pro_name,
dodate, table_rows, ut_val)
else:
sql2 = """
insert into %s.%s set table_name='%s',pro_name='%s',view_date='%s',table_rows='%s',last_update_time='%s',last_trade_date='%s'
""" % (chk_db, chk_tb, tb_name, pro_name, dodate,
table_rows, ut_val, td_val)
else:
if td_flag == 0:
sql2 = """
insert into %s.%s set table_name='%s',pro_name='%s',view_date='%s',table_rows='%s'
""" % (chk_db, chk_tb, tb_name,
pro_name, dodate, table_rows)
else:
sql2 = """
insert into %s.%s set table_name='%s',pro_name='%s',view_date='%s',table_rows='%s',last_trade_date='%s'
""" % (chk_db, chk_tb, tb_name, pro_name, dodate,
table_rows, td_val)
ai_cur.execute(sql2)
except:
pass
ai_cur.execute('commit')
from utils.conf import wd,wd_cur,ai, ai_cur
from utils.etl import ETL
warnings.filterwarnings('ignore')
os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8'
table_name = 'deposit_interest'
file_name = 'deposit_interest'
#此表每日全量更新
sql_trun="""truncate table gjyf.%s """%(table_name )
print(sql_trun)
ai_cur.execute(sql_trun)
ai_cur.execute('commit')
sql="""
select to_date(TRADE_DAYS,'yyyy-mm-dd') as cal_date,
B_INFO_RATE as year_interest,
src_opdate
from
(
select b.TRADE_DAYS,
a.B_INFO_RATE,
a.opdate as src_opdate,
row_number() over (partition by substr(b.TRADE_DAYS,1,6) order by TRADE_DAYS desc) rn
from eterminal.CBondBenchmark a,
def gjyf_data(pro_name):
ai_cur.execute("delete from %s.%s where view_date='%s' and pro_name='%s'" % (chk_db, chk_tb, dodate, pro_name))
sql = """
SELECT table_name FROM information_schema.COLUMNS WHERE table_schema='%s' AND column_name='src_opdate'
""" % (pro_name)
print(sql)
ai_cur.execute(sql)
for tables in ai_cur.fetchall():
try:
tb_name = tables[0]
print(tb_name)
sql1 = """
select count(*) from %s.%s
""" % (pro_name, tb_name)
ai_cur.execute(sql1)
tmp = ai_cur.fetchall()
table_rows = tmp[0][0]
sql_ut_val = """
select max(update_time),max(create_time),max(src_opdate) from %s.%s
""" % (pro_name, tb_name)
ai_cur.execute(sql_ut_val)
timedata = ai_cur.fetchall()
ut_val = timedata[0][0]
print(ut_val)
ct_val = timedata[0][1]
op_val = timedata[0][2]
print(ct_val, op_val)
#td_val = None
sql_td = """
SELECT count(1) FROM information_schema.COLUMNS WHERE table_schema='%s' AND column_name='trade_date' and TABLE_NAME = '%s'
""" % (pro_name, tb_name)
ai_cur.execute(sql_td)
flag_td = ai_cur.fetchall()
ftd = flag_td[0][0]
print(ftd)
if ftd == 1:
sql_td_val = """
select max(trade_date) from %s.%s
""" % (pro_name, tb_name)
ai_cur.execute(sql_td_val)
td_val = ai_cur.fetchall()[0][0]
sql2 = """
insert into %s.%s set table_name='%s',pro_name='%s',view_date='%s',table_rows='%s',last_src_opdate='%s',last_insert_time='%s',last_update_time='%s',last_trade_date='%s'
""" % (chk_db, chk_tb, tb_name, pro_name, dodate, table_rows, op_val, ct_val, ut_val, td_val)
else :
sql2 = """
insert into %s.%s set table_name='%s',pro_name='%s',view_date='%s',table_rows='%s',last_src_opdate='%s',last_insert_time='%s',last_update_time='%s'
""" % (chk_db, chk_tb, tb_name, pro_name, dodate, table_rows, op_val, ct_val, ut_val)
ai_cur.execute(sql2)
except:
pass
ai_cur.execute('commit')
warnings.filterwarnings('ignore')
os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8'
if len(sys.argv) == 1:
dotime = (datetime.datetime.now() -
datetime.timedelta(days=1)).strftime("%Y-%m-%d")
else:
dotime = sys.argv[1]
table_name = 't_entrust_deal_history'
file_name = 't_entrust_deal_history'
# 获取数据映射sql
src_sql = "SELECT src_sql, data_source,columns_list,unique_key FROM iadvisor.etl_src_tgt_rule WHERE tgt_table = '%s' AND is_use = 1" % table_name
ai_cur.execute(src_sql)
rows = ai_cur.fetchall()
print(rows)
print(rows[0][0])
sql = "select * from (%s) where to_char(CJDATE,'YYYY-MM-DD') >='%s'" % (
rows[0][0], dotime)
columns = rows[0][2]
unique_key = rows[0][3]
print(sql)
table_name2 = 'iadvisor.t_entrust_deal_history'
etl = ETL(src_cur=wd_cur,
src_conn=wd,
tgt_cur=ai_cur,
tgt_conn=ai,
sql=sql,
table_name=table_name2,