def pu_all():
df = pd.read_sql("SELECT * FROM (SELECT fund_id,fund_name,statistic_date,nav,added_nav FROM fund_nv\
where fund_id NOT IN ('A80001','A80002','A80003','A80004','B90001','B90002','BE0001','BE0002','BE0051','C60001')\
ORDER BY statistic_date DESC ) AS T GROUP BY T.fund_id", engine_pu)
df["version"] = now
to_sql("fund_nv", engine5, df, type="update")
return print("公募库最新提取到本地库")
def pu_all2():
df = pd.read_sql("SELECT * FROM (SELECT fund_id,fund_name,statistic_date,return_10k,d7_return_a FROM fund_yield\
ORDER BY statistic_date DESC ) AS T GROUP BY T.fund_id", engine_pu)
df["version"] = now
df.rename(columns={"return_10k": "nav", "d7_return_a": "added_nav"}, inplace=True)
to_sql("fund_nv", engine5, df, type="update")
return print("公募库货币型提取到本地库")
def wind_pull():
df = pd.read_excel("C:\\Users\\63220\\Desktop\\wind净值每日.xlsx")
# df["fund_id"]=df["fund_id"].apply(lambda x: "%06d" % x)
df["nav_wind"] = df["nav_wind"].apply(lambda x: '%.4f' % x)
df["added_nav_wind"] = df["added_nav_wind"].apply(lambda x: '%.4f' % x)
df["version"] = now
df2 = df[(df.nav_wind != '0.0000') & (df.added_nav_wind != '0.0000')]
to_sql("fund_nv", engine5, df2, type="update")
return print("wind公募全量提取到本地库")
def sanfang():
df_limit = pd.read_sql(
"select fund_id,fund_name from zhaoyang WHERE version='{}'".format(
now), engine5)
df_JR = df_limit["fund_id"].tolist()
JR = '\'' + "','".join(df_JR) + '\''
# df_name = df_limit["fund_name"].tolist()
df_source = pd.read_sql(
"SELECT * FROM (SELECT fund_id,statistic_date FROM fund_nv_data_source WHERE \
fund_id in ({}) ORDER BY statistic_date DESC ) AS T GROUP BY T.fund_id ".
format(JR), engine_base)
df_source.rename(columns={"statistic_date": "nv_source"}, inplace=True)
df_source["version"] = now
to_sql("zhaoyang", engine5, df_source, type="update")
df_source_copy2 = pd.read_sql(
"SELECT * FROM (SELECT fund_id,statistic_date FROM fund_nv_data_source_copy2 WHERE \
fund_id in ({}) ORDER BY statistic_date DESC ) AS T GROUP BY T.fund_id ".
format(JR), engine_base)
df_source_copy2.rename(columns={"statistic_date": "nv_source_copy2"},
inplace=True)
df_source_copy2["version"] = now
to_sql("zhaoyang", engine5, df_source_copy2, type="update")
df_standard_copy2 = pd.read_sql(
"SELECT * FROM (SELECT fund_id,statistic_date FROM fund_nv_data_standard_copy2 WHERE \
fund_id in ({}) ORDER BY statistic_date DESC ) AS T GROUP BY T.fund_id ".
format(JR), engine_base)
df_standard_copy2.rename(columns={"statistic_date": "nv_standard_copy2"},
inplace=True)
df_standard_copy2["version"] = now
to_sql("zhaoyang", engine5, df_standard_copy2, type="update")
df_jfz = pd.read_sql(
"SELECT * FROM (SELECT fund_id,statistic_date FROM fund_nv_data_source_copy2 WHERE source_id='020002'\
and fund_id in ({}) ORDER BY statistic_date DESC ) AS T GROUP BY T.fund_id "
.format(JR), engine_base)
df_jfz.rename(columns={"statistic_date": "jinfuzi"}, inplace=True)
df_jfz["version"] = now
to_sql("zhaoyang", engine5, df_jfz, type="update")
df_haomai = pd.read_sql(
"SELECT * FROM (SELECT fund_id,statistic_date FROM fund_nv_data_source_copy2 WHERE source_id='020001'\
and fund_id in ({}) ORDER BY statistic_date DESC ) AS T GROUP BY T.fund_id "
.format(JR), engine_base)
df_haomai.rename(columns={"statistic_date": "haomai"}, inplace=True)
df_haomai["version"] = now
to_sql("zhaoyang", engine5, df_haomai, type="update")
print("三方日期导入")
def pu_all3():
df = pd.read_sql("SELECT * FROM (SELECT fund_id,fund_name,statistic_date,return_10k,d7_return_a FROM fund_yield\
ORDER BY statistic_date DESC ) AS T GROUP BY T.fund_id", engine_pu)
df["version"] = now
df.rename(columns={"return_10k": "nav", "d7_return_a": "added_nav"}, inplace=True)
df2 = pd.read_sql("select fund_id,statistic_date from fund_nv where version='{}'".format(now), engine5)
df2.rename(columns={"statistic_date": "y_date"}, inplace=True)
DF = pd.merge(df, df2, how='left')
dif1 = DF.loc[~DF["y_date"].notnull()]
del dif1["y_date"]
dif2 = DF.loc[DF["y_date"] < DF["statistic_date"]]
del dif2["y_date"]
to_sql("fund_nv", engine5, dif1, type="update")
to_sql("fund_nv", engine5, dif2, type="update")
return print("公募库货币型提取到本地库")
def crawl_benchmark(id):
wind.start() # 启动wind
all_id = str(id) + '.OF'
tmp = wind.wsd(all_id, "NAV_date,nav,NAV_acc", "ED0D", now, "") # 万得API用法
df = pd.DataFrame(tmp.Data)
df = df.T
if df[0][0] == None:
pass
else:
df.columns = ["statistic_date_wind", "nav_wind", "added_nav_wind"]
df["statistic_date_wind"] = df["statistic_date_wind"].apply(lambda x: x.strftime('%Y-%m-%d'))
df["fund_id"] = id
df["version"] = now
to_sql("fund_nv", engine5, df, type="update")
print(df)
def to_ku():
df_limit = pd.read_sql(
"SELECT * FROM (SELECT fund_id,fund_name,statistic_date FROM fund_nv_data_standard WHERE statistic_date > '2017-10-01'\
ORDER BY statistic_date DESC ) AS T GROUP BY T.fund_id ORDER BY RAND() LIMIT 1000",
engine_base)
df_limit["version"] = now
df_limit.rename(columns={"statistic_date": "standard"}, inplace=True)
c = df_limit.iloc[:, [0, 1, 2, 3]]
to_sql("zhaoyang", engine5, c, type="update")
df_JR = df_limit["fund_id"].tolist()
JR = '\'' + "','".join(df_JR) + '\''
full_name = pd.read_sql(
"select fund_id,fund_full_name from fund_info WHERE fund_id in ({})".
format(JR), engine_base)
full_name.rename(columns={"fund_full_name": "fund_name"}, inplace=True)
full_name["version"] = now
to_sql("zhaoyang", engine5, full_name, type="update")
print("样本1000只固定")
def to_ku(DF):
if DF.empty:
print("df为空")
else:
to_sql("fund_org_mapping", engine_base, DF, type="update")
DF.append(y)
else:
date2 = df_max.iloc[0, 2]
# print(type(date2))
str_date = date2.strftime("%Y-%m-%d")
str_date2 = str_date + ' 00:00:00'
# print(str_date2)
# print(type(str_date2))
timeArray = time.strptime(str_date2, "%Y-%m-%d %H:%M:%S")
timeStamp = int(time.mktime(timeArray))
# print(timeStamp)
if cl_timeStamp>=timeStamp:
print('采用新源')
x=(fund_ID,'3','1')
DF.append(x)
else:
pass
df_all=pd.DataFrame(DF)
df_all.columns =["fund_id","data_source","is_updata"]
print(df_all)
is_checked = input("输入1来确认入库\n")
if is_checked == "1":
engine_TEST = create_engine("mysql+pymysql://{}:{}@{}:{}/{}".format('root','','localhost',3306,'test', ), connect_args={"charset": "utf8"},echo=True,)
to_sql("fund_nv_updata_source", engine_TEST, df_all, type="update")
else:
pass
bb = '按投资策略分类'
dd2.append(aa)
dd2.append(bb)
dataframe2.append(dd2)
print(dataframe1)
print(dataframe2)
df2 = pd.DataFrame(dataframe2)
if dataframe1 == []:
print("没有605")
else:
df1 = pd.DataFrame(dataframe1)
df1.columns = ["fund_id", "type_code", "type_name", "confirmed", "classified_by", "typestandard_code",
"typestandard_name"]
df2.columns = ["fund_id", "type_code", "type_name", "confirmed", "classified_by", "stype_code", "stype_name",
"typestandard_code", "typestandard_name"]
print(df2)
is_checked = input("输入1来确认入库,输入2确认605策略\n")
if is_checked == "1":
to_sql("fund_type_mapping_import", engine_base, df2, type="update")
elif is_checked == "2":
to_sql("fund_type_mapping_import", engine_base, df1, type="update")
else:
pass
cols_used = [
"fund_id",
"fund_name_1",
]
result = df_cl[cols_used]
#
# aa=df_cl["fund_status"].tolist()
# del df_cl["fund_status"]
# for i in range(len(aa)):
# if aa[i] == '正常':
# aa[i] = '运行中'
#
# print(aa)
# # bb=df_cl["fund_id"].tolist()
# # BB=pd.DataFrame(bb)
#
# AA=pd.DataFrame(aa)
# AA.columns = ["fund_status"]
# print(AA)
# print(df_cl)
#
# # frames=[BB,AA]
# #
# # result=pd.concat(frames)
# #
# # print(result)
# # #
# # result.to_csv('C:\\Users\\63220\\Desktop\\11-19.csv')
# # df_cl.to_csv('C:\\Users\\63220\\Desktop\\11-17.csv')
to_sql("fund_info", engine3, df_cl, type="update")