def get_sectorconstituent(index_code, index_name, target_date) -> pd.DataFrame: """ 通过 wind 获取指数成分股及权重 :param index_code: :param index_name: :param target_date: :return: """ target_date_str = date_2_str(target_date) logger.info('获取 %s %s %s 板块信息', index_code, index_name, target_date) sec_df = invoker.wset( "indexconstituent", "date=%s;windcode=%s" % (target_date_str, index_code)) if sec_df is not None and sec_df.shape[0] > 0: # 发现部分情况下返回数据的日期与 target_date 日期不匹配 sec_df = sec_df[sec_df['date'].apply( lambda x: str_2_date(x) == target_date)] if sec_df is None or sec_df.shape[0] == 0: return None sec_df["index_code"] = index_code sec_df["index_name"] = index_name sec_df.rename(columns={ 'date': 'trade_date', 'sec_name': 'stock_name', 'i_weight': 'weight', }, inplace=True) return sec_df
def get_cb_set(date_fetch): date_fetch_str = date_fetch.strftime(STR_FORMAT_DATE) data_df = invoker.wset("sectorconstituent", "date=%s;sectorid=1000021892000000" % date_fetch_str) if data_df is None: logging.warning('%s 获取股票代码失败', date_fetch_str) return None data_count = data_df.shape[0] logging.info('get %d convertible bond on %s', data_count, date_fetch_str) return set(data_df['wind_code'])
def get_stock_code_set(date_fetch): """ :param date_fetch: :return: """ date_fetch_str = date_fetch.strftime(STR_FORMAT_DATE) stock_df = invoker.wset("sectorconstituent", "date=%s;sectorid=a002010100000000" % date_fetch_str) # 全部港股 if stock_df is None: logging.warning('%s 获取股票代码失败', date_fetch_str) return None stock_count = stock_df.shape[0] logging.info('get %d wind_code on %s', stock_count, date_fetch_str) return set(stock_df['wind_code'])
def get_wind_code_set(date_fetch): date_fetch_str = date_fetch.strftime(STR_FORMAT_DATE) # 纯股票型基金 混合型 sectorid_list = ['2001010101000000', '2001010200000000'] for sector_id in sectorid_list: data_df = invoker.wset("sectorconstituent", "date=%s;sectorid=%s" % (date_fetch_str, sector_id)) if data_df is None: logging.warning('%s 获取股票代码失败', date_fetch_str) return None data_count = data_df.shape[0] logging.info('get %d public offering fund on %s', data_count, date_fetch_str) wind_code_s = data_df['wind_code'] wind_code_s = wind_code_s[wind_code_s.apply(lambda x: x.find('!') == -1)] return set(wind_code_s)
def get_sectorconstituent(sector_code, sector_name, target_date) -> pd.DataFrame: """ 通过 wind 获取板块成分股 :param sector_code: :param sector_name: :param target_date: :return: """ target_date_str = date_2_str(target_date) logger.info('获取 %s %s %s 板块信息', sector_code, sector_name, target_date) sec_df = invoker.wset("sectorconstituent", "date=%s;sectorid=%s" % (target_date_str, sector_code)) sec_df["sector_code"] = sector_code sec_df["sector_name"] = sector_name sec_df.rename(columns={ 'date': 'trade_date', 'sec_name': 'stock_name', }, inplace=True) return sec_df
def import_smfund_info(chain_param=None): """ :param chain_param: 在celery 中將前面結果做爲參數傳給後面的任務 :return: """ table_name = "wind_smfund_info" has_table = engine_md.has_table(table_name) # w.start() types = { u'主动股票型分级母基金': 1000007766000000, u'被动股票型分级母基金': 1000007767000000, u'纯债券型分级母基金': 1000007768000000, u'混合债券型分级母基金': 1000007769000000, u'混合型分级母基金': 1000026143000000, u'QDII分级母基金': 1000019779000000 } col_name_param_list = [ ('wind_code', String(20)), ('fund_type', String(20)), ('sec_name', String(50)), ('class_a_code', String(20)), ('class_a_name', String(50)), ('class_b_code', String(20)), ('class_b_name', String(50)), ('track_indexcode', String(20)), ('track_indexname', String(50)), ('a_pct', DOUBLE), ('b_pct', DOUBLE), ('upcv_nav', DOUBLE), ('downcv_nav', DOUBLE), ('max_purchasefee', DOUBLE), ('max_redemptionfee', DOUBLE), ] col_name = ",".join([col_name for col_name, _ in col_name_param_list]) # 获取各个历史时段的分级基金列表,并汇总全部基金代码 dates = [ '2011-01-01', '2013-01-01', '2015-01-01', '2017-01-01', '2018-01-01' ] # 分三个时间点获取市场上所有分级基金产品 df = pd.DataFrame() # 获取接数据 for date_p in dates: temp_df = invoker.wset("sectorconstituent", "date=%s;sectorid=1000006545000000" % date_p) df = df.append(temp_df) wind_code_all = df['wind_code'].unique() # 设置dtype dtype = {key: val for key, val in col_name_param_list} dtype['wind_code'] = String(20) dtype["tradable"] = String(20) dtype["fund_setupdate"] = Date dtype["fund_maturitydate"] = Date if has_table: with with_db_session(engine_md) as session: table = session.execute("SELECT wind_code FROM wind_smfund_info") wind_code_existed = set( [content[0] for content in table.fetchall()]) wind_code_new = list(set(wind_code_all) - wind_code_existed) else: wind_code_new = list(set(wind_code_all)) # if len(wind_code_new) == 0: # print('no sm fund imported') # 查询数据库,剔除已存在的基金代码 wind_code_new = [code for code in wind_code_new if code.find('!') < 0] info_df = invoker.wss(wind_code_new, 'fund_setupdate, fund_maturitydate') if info_df is None: raise Exception('no data') info_df['FUND_SETUPDATE'] = info_df['FUND_SETUPDATE'].apply( lambda x: str_2_date(x)) info_df['FUND_MATURITYDATE'] = info_df['FUND_MATURITYDATE'].apply( lambda x: str_2_date(x)) info_df.rename(columns={ 'FUND_SETUPDATE': 'fund_setupdate', 'FUND_MATURITYDATE': 'fund_maturitydate' }, inplace=True) field = col_name # field = "fund_type,wind_code,sec_name,class_a_code,class_a_name,class_b_code,class_b_name,a_pct,b_pct,upcv_nav,downcv_nav,track_indexcode,track_indexname,max_purchasefee,max_redemptionfee" for code in info_df.index: beginDate = info_df.loc[code, 'fund_setupdate'].strftime('%Y-%m-%d') temp_df = invoker.wset("leveragedfundinfo", "date=%s;windcode=%s;field=%s" % (beginDate, code, field)) # ;field=%s , field df = df.append(temp_df) if DEBUG and len(df) > 10: break df.set_index('wind_code', inplace=True) df['tradable'] = df.index.map(lambda x: x if 'S' in x else None) # df.index = df.index.map(lambda x: x[:-2] + 'OF') info_df = info_df.join(df, how='outer') # TODO: 需要检查一下代码 info_df.rename( columns={ 'a_nav': 'nav_a', 'b_nav': 'nav_b', 'a_fs_inc': 'fs_inc_a', 'b_fs_inc': 'fs_inc_b' }) info_df.index.rename('wind_code', inplace=True) info_df.reset_index(inplace=True) bunch_insert_on_duplicate_update(info_df, table_name, engine_md, dtype=dtype) logging.info("更新 %s 完成 存量数据 %d 条", table_name, len(info_df)) if not has_table and engine_md.has_table(table_name): alter_table_2_myisam(engine_md, [table_name]) build_primary_key([table_name]) # 更新 code_mapping 表 update_from_info_table(table_name)
def import_smfund_daily(chain_param=None): """ :param chain_param: 在celery 中將前面結果做爲參數傳給後面的任務 :return: """ table_name = "wind_smfund_daily" has_table = engine_md.has_table(table_name) col_name_param_list = [ ('next_pcvdate', Date), ('a_nav', DOUBLE), ('b_nav', DOUBLE), ('a_fs_inc', DOUBLE), ('b_fs_inc', DOUBLE), ('cur_interest', DOUBLE), ('next_interest', DOUBLE), ('ptm_year', DOUBLE), ('anal_pricelever', DOUBLE), ('anal_navlevel', DOUBLE), ('t1_premium', DOUBLE), ('t2_premium', DOUBLE), ('dq_status', String(50)), ('tm_type', TEXT), ('code_p', String(20)), ('trade_date', Date), ('open', DOUBLE), ('high', DOUBLE), ('low', DOUBLE), ('close', DOUBLE), ('volume', DOUBLE), ('amt', DOUBLE), ('pct_chg', DOUBLE), ('open_a', DOUBLE), ('high_a', DOUBLE), ('low_a', DOUBLE), ('close_a', DOUBLE), ('volume_a', DOUBLE), ('amt_a', DOUBLE), ('pct_chg_a', DOUBLE), ('open_b', DOUBLE), ('high_b', DOUBLE), ('low_b', DOUBLE), ('close_b', DOUBLE), ('volume_b', DOUBLE), ('amt_b', DOUBLE), ('pct_chg_b', DOUBLE), ] # wset的调用参数 wind_indictor_str = ",".join([key for key, _ in col_name_param_list[:14]]) # 设置dtype类型 dtype = {key: val for key, val in col_name_param_list} date_ending = date.today() - ONE_DAY if datetime.now( ).hour < BASE_LINE_HOUR else date.today() date_ending_str = date_ending.strftime('%Y-%m-%d') # 对于 表格是否存在进行判断,取值 if has_table: sql_str = """ SELECT wind_code, ifnull(date, fund_setupdate) date_start, class_a_code, class_b_code FROM wind_smfund_info fi LEFT OUTER JOIN (SELECT code_p, adddate(max(trade_date), 1) trade_date_max FROM wind_smfund_daily GROUP BY code_p) smd ON fi.wind_code = smd.code_p WHERE fund_setupdate IS NOT NULL AND class_a_code IS NOT NULL AND class_b_code IS NOT NULL""" else: sql_str = """ SELECT wind_code, ifnull(date, fund_setupdate) date_start, class_a_code, class_b_code FROM wind_smfund_info WHERE fund_setupdate IS NOT NULL AND class_a_code IS NOT NULL AND class_b_code IS NOT NULL""" df = pd.read_sql(sql_str, engine_md) df.set_index('wind_code', inplace=True) data_len = df.shape[0] logger.info('分级基金数量: %d', data_len) index_start = 1 # 获取data_from for data_num, wind_code in enumerate( df.index, start=1): # 可调整 # [100:min([df_count, 200])] if data_num < index_start: continue logger.info('%d/%d) %s start to import', data_num, data_len, wind_code) date_from = df.loc[wind_code, 'date_start'] date_from = str_2_date(date_from) if type(date_from) not in (date, datetime, Timestamp): logger.info('%d/%d) %s has no fund_setupdate will be ignored', data_num, data_len, wind_code) # print(df.iloc[i, :]) continue date_from_str = date_from.strftime('%Y-%m-%d') if date_from > date_ending: logger.info('%d/%d) %s %s %s 跳过', data_num, data_len, wind_code, date_from_str, date_ending_str) continue # 设置wsd接口参数 field = "open,high,low,close,volume,amt,pct_chg" # wsd_cache(w, code, field, beginTime, today, "") try: df_p = invoker.wsd(wind_code, field, date_from_str, date_ending_str, "") except APIError as exp: logger.exception("%d/%d) %s 执行异常", data_num, data_len, wind_code) if exp.ret_dic.setdefault('error_code', 0) in ( -40520007, # 没有可用数据 -40521009, # 数据解码失败。检查输入参数是否正确,如:日期参数注意大小月月末及短二月 ): continue else: break if df_p is None: continue df_p.rename(columns=lambda x: x.swapcase(), inplace=True) df_p['code_p'] = wind_code code_a = df.loc[wind_code, 'class_a_code'] if code_a is None: print('%d %s has no code_a will be ignored' % (data_num, wind_code)) # print(df.iloc[i, :]) continue # 获得数据存储到df_a里面 # df_a = wsd_cache(w, code_a, field, beginTime, today, "") df_a = invoker.wsd(code_a, field, date_from_str, date_ending_str, "") df_a.rename(columns=lambda x: x.swapcase() + '_a', inplace=True) code_b = df.loc[wind_code, 'class_b_code'] # df_b = wsd_cache(w, code_b, field, beginTime, today, "") # 获取接口数据 获得df_b df_b = invoker.wsd(code_b, field, date_from_str, date_ending_str, "") df_b.columns = df_b.columns.map(lambda x: x.swapcase() + '_b') new_df = pd.DataFrame() for date_str in df_p.index: # time = date_str.date().strftime('%Y-%m-%d') field = "date=%s;windcode=%s;field=%s" % (date_str, wind_code, wind_indictor_str) # wset_cache(w, "leveragedfundinfo", field) temp = invoker.wset("leveragedfundinfo", field) temp['date'] = date_str new_df = new_df.append(temp) if DEBUG and len(new_df) > 8: break # 将获取信息进行表格联立 合并 new_df['next_pcvdate'] = new_df['next_pcvdate'].map( lambda x: str_2_date(x) if x is not None else x) new_df.set_index('date', inplace=True) one_df = pd.concat([df_p, df_a, df_b, new_df], axis=1) one_df.index.rename('trade_date', inplace=True) one_df.reset_index(inplace=True) # one_df['date'] = one_df['date'].map(lambda x: x.date()) one_df.rename(columns={'date': 'trade_date'}, inplace=True) # one_df.rename(columns={"index":'trade_date'},inplace=True) # one_df.set_index(['code_p', 'trade_date'], inplace=True) bunch_insert_on_duplicate_update(one_df, table_name, engine_md, dtype=dtype) logger.info('%d/%d) %s import success', data_num, data_len, wind_code) if not has_table and engine_md.has_table(table_name): alter_table_2_myisam(engine_md, [table_name]) # build_primary_key([table_name]) # 手动创建主键, 主键不是wind_code create_pk_str = """ALTER TABLE {table_name} CHANGE COLUMN `code_p` `code_p` VARCHAR(20) NOT NULL , CHANGE COLUMN `trade_date` `trade_date` DATE NOT NULL , ADD PRIMARY KEY (`code_p`, `trade_date`)""".format( table_name=table_name) with with_db_session(engine_md) as session: session.execute(create_pk_str)
def import_future_info(chain_param=None): """ 更新期货合约列表信息 :param chain_param: 在celery 中將前面結果做爲參數傳給後面的任務 :return: """ table_name = "wind_future_info" has_table = engine_md.has_table(table_name) logger.info("更新 %s 开始", table_name) # 获取已存在合约列表 if has_table: sql_str = 'select wind_code, ipo_date from {table_name}'.format( table_name=table_name) with with_db_session(engine_md) as session: table = session.execute(sql_str) wind_code_ipo_date_dic = dict(table.fetchall()) else: wind_code_ipo_date_dic = {} # 通过wind获取合约列表 # w.start() # 初始化服务器接口,用于下载万得数据 future_sectorid_dic_list = [ #中金所期货合约 # {'subject_name': 'CFE 沪深300', 'regex': r"IF\d{4}\.CFE", # 'sectorid': 'a599010102000000', 'date_establish': '2010-4-16'}, # {'subject_name': 'CFE 上证50', 'regex': r"IH\d{4}\.CFE", # 'sectorid': '1000014871000000', 'date_establish': '2015-4-16'}, # {'subject_name': 'CFE 中证500', 'regex': r"IC\d{4}\.CFE", # 'sectorid': '1000014872000000', 'date_establish': '2015-4-16'}, # {'subject_name': '2年期国债', 'regex': r"TS\d{4}\.CFE", # 'sectorid': '1000014880000000', 'date_establish': '2018-08-17'}, # {'subject_name': '5年期国债', 'regex': r"TF\d{4}\.CFE", # 'sectorid': '1000010299000000', 'date_establish': '2013-09-06'}, # {'subject_name': '10年期国债', 'regex': r"T\d{4}\.CFE", # 'sectorid': '1000014874000000', 'date_establish': '2015-03-20'}, #中金所指数 { 'subject_name': 'CFE 沪深300', 'regex': r"IF\.CFE", 'sectorid': 'a599010102000000', 'date_establish': '2010-4-16' }, { 'subject_name': 'CFE 上证50', 'regex': r"IH\.CFE", 'sectorid': '1000014871000000', 'date_establish': '2015-4-16' }, { 'subject_name': 'CFE 中证500', 'regex': r"IC\.CFE", 'sectorid': '1000014872000000', 'date_establish': '2015-4-16' }, { 'subject_name': '2年期国债', 'regex': r"TS\.CFE", 'sectorid': '1000014880000000', 'date_establish': '2018-08-17' }, { 'subject_name': '5年期国债', 'regex': r"TF\.CFE", 'sectorid': '1000010299000000', 'date_establish': '2013-09-06' }, { 'subject_name': '10年期国债', 'regex': r"T\.CFE", 'sectorid': '1000014874000000', 'date_establish': '2015-03-20' }, # 大连商品交易所合约 # {'subject_name': 'DCE 焦炭', 'regex': r"J\d{4}\.DCE", # 'sectorid': '1000002976000000', 'date_establish': '2011-04-15'}, # {'subject_name': 'DCE 焦煤', 'regex': r"JM\d{4}\.DCE", # 'sectorid': '1000009338000000', 'date_establish': '2013-03-22'}, # {'subject_name': '铁矿石', 'regex': r"I\d{4}\.DCE", # 'sectorid': '1000006502000000', 'date_establish': '2013-10-18'}, # {'subject_name': '豆粕', 'regex': r"M\d{4}\.DCE", # 'sectorid': 'a599010304000000', 'date_establish': '2000-07-17'}, # {'subject_name': '豆油', 'regex': r"Y\d{4}\.DCE", # 'sectorid': 'a599010306000000', 'date_establish': '2006-01-09'}, # {'subject_name': '棕榈油', 'regex': r"P\d{4}\.DCE", # 'sectorid': 'a599010307000000', 'date_establish': '2007-10-29'}, # {'subject_name': '豆一', 'regex': r"A\d{4}\.DCE", # 'sectorid': 'a599010302000000', 'date_establish': '2004-07-15'}, # {'subject_name': '豆二', 'regex': r"B\d{4}\.DCE", # 'sectorid': 'a599010303000000', 'date_establish': '2004-12-22'}, # {'subject_name': '玉米', 'regex': r"C\d{4}\.DCE", # 'sectorid': 'a599010305000000', 'date_establish': '2004-09-22'}, # {'subject_name': '玉米淀粉', 'regex': r"CS\d{4}\.DCE", # 'sectorid': '1000011469000000', 'date_establish': '2014-12-19'}, # {'subject_name': '鸡蛋', 'regex': r"JD\d{4}\.DCE", # 'sectorid': '1000011464000000', 'date_establish': '2013-11-08'}, # {'subject_name': '线型低密度聚乙烯', 'regex': r"L\d{4}\.DCE", # 'sectorid': 'a599010308000000', 'date_establish': '2007-07-31'}, # {'subject_name': '聚氯乙烯', 'regex': r"V\d{4}\.DCE", # 'sectorid': 'a599010309000000', 'date_establish': '2009-05-25'}, # {'subject_name': '聚丙烯', 'regex': r"PP\d{4}\.DCE", # 'sectorid': '1000011468000000', 'date_establish': '2014-02-28'}, #大连指数 { 'subject_name': 'DCE 焦炭', 'regex': r"J\.DCE", 'sectorid': '1000002976000000', 'date_establish': '2011-04-15' }, { 'subject_name': 'DCE 焦煤', 'regex': r"JM\.DCE", 'sectorid': '1000009338000000', 'date_establish': '2013-03-22' }, { 'subject_name': '铁矿石', 'regex': r"I\.DCE", 'sectorid': '1000006502000000', 'date_establish': '2013-10-18' }, { 'subject_name': '豆粕', 'regex': r"M\.DCE", 'sectorid': 'a599010304000000', 'date_establish': '2000-07-17' }, { 'subject_name': '豆油', 'regex': r"Y\.DCE", 'sectorid': 'a599010306000000', 'date_establish': '2006-01-09' }, { 'subject_name': '棕榈油', 'regex': r"P\.DCE", 'sectorid': 'a599010307000000', 'date_establish': '2007-10-29' }, { 'subject_name': '豆一', 'regex': r"A\.DCE", 'sectorid': 'a599010302000000', 'date_establish': '2004-07-15' }, { 'subject_name': '豆二', 'regex': r"B\.DCE", 'sectorid': 'a599010303000000', 'date_establish': '2004-12-22' }, { 'subject_name': '玉米', 'regex': r"C\.DCE", 'sectorid': 'a599010305000000', 'date_establish': '2004-09-22' }, { 'subject_name': '玉米淀粉', 'regex': r"CS\.DCE", 'sectorid': '1000011469000000', 'date_establish': '2014-12-19' }, { 'subject_name': '鸡蛋', 'regex': r"JD\.DCE", 'sectorid': '1000011464000000', 'date_establish': '2013-11-08' }, { 'subject_name': '线型低密度聚乙烯', 'regex': r"L\.DCE", 'sectorid': 'a599010308000000', 'date_establish': '2007-07-31' }, { 'subject_name': '聚氯乙烯', 'regex': r"V\.DCE", 'sectorid': 'a599010309000000', 'date_establish': '2009-05-25' }, { 'subject_name': '聚丙烯', 'regex': r"PP\.DCE", 'sectorid': '1000011468000000', 'date_establish': '2014-02-28' }, #上海期货交易所合约 # {'subject_name': '天然橡胶', 'regex': r"RU\d{4}\.SHF", # 'sectorid': 'a599010208000000', 'date_establish': '1995-06-01'}, # {'subject_name': '铜', 'regex': r"CU\d{4}\.SHF", # 'sectorid': 'a599010202000000', 'date_establish': '1995-05-01'}, # {'subject_name': '铝', 'regex': r"AL\d{4}\.SHF", # 'sectorid': 'a599010203000000', 'date_establish': '1995-05-01'}, # {'subject_name': '锌', 'regex': r"ZN\d{4}\.SHF", # 'sectorid': 'a599010204000000', 'date_establish': '2007-03-26'}, # {'subject_name': '铅', 'regex': r"PB\d{4}\.SHF", # 'sectorid': '1000002892000000', 'date_establish': '2011-03-24'}, # {'subject_name': '镍', 'regex': r"NI\d{4}\.SHF", # 'sectorid': '1000011457000000', 'date_establish': '2015-03-27'}, # {'subject_name': '锡', 'regex': r"SN\d{4}\.SHF", # 'sectorid': '1000011458000000', 'date_establish': '2015-03-27'}, # {'subject_name': 'SHFE 黄金', 'regex': r"AU\d{4}\.SHF", # 'sectorid': 'a599010205000000', 'date_establish': '2008-01-09'}, # {'subject_name': 'SHFE 沪银', 'regex': r"AG\d{4}\.SHF", # 'sectorid': '1000006502000000', 'date_establish': '2012-05-10'}, # {'subject_name': 'SHFE 螺纹钢', 'regex': r"RB\d{4}\.SHF", # 'sectorid': 'a599010206000000', 'date_establish': '2009-03-27'}, # {'subject_name': 'SHFE 热卷', 'regex': r"HC\d{4}\.SHF", # 'sectorid': '1000011455000000', 'date_establish': '2014-03-21'}, # {'subject_name': 'SHFE 沥青', 'regex': r"BU\d{4}\.SHF", # 'sectorid': '1000011013000000', 'date_establish': '2013-10-09'}, # {'subject_name': '原油', 'regex': r"SC\d{4}\.SHF", # 'sectorid': '1000011463000000', 'date_establish': '2018-03-26'}, #上海期货交易所指数 { 'subject_name': '天然橡胶', 'regex': r"RU\.SHF", 'sectorid': 'a599010208000000', 'date_establish': '1995-06-01' }, { 'subject_name': '铜', 'regex': r"CU\.SHF", 'sectorid': 'a599010202000000', 'date_establish': '1995-05-01' }, { 'subject_name': '铝', 'regex': r"AL\.SHF", 'sectorid': 'a599010203000000', 'date_establish': '1995-05-01' }, { 'subject_name': '锌', 'regex': r"ZN\.SHF", 'sectorid': 'a599010204000000', 'date_establish': '2007-03-26' }, { 'subject_name': '铅', 'regex': r"PB\.SHF", 'sectorid': '1000002892000000', 'date_establish': '2011-03-24' }, { 'subject_name': '镍', 'regex': r"NI\.SHF", 'sectorid': '1000011457000000', 'date_establish': '2015-03-27' }, { 'subject_name': '锡', 'regex': r"SN\.SHF", 'sectorid': '1000011458000000', 'date_establish': '2015-03-27' }, { 'subject_name': 'SHFE 黄金', 'regex': r"AU\.SHF", 'sectorid': 'a599010205000000', 'date_establish': '2008-01-09' }, { 'subject_name': 'SHFE 沪银', 'regex': r"AG\.SHF", 'sectorid': '1000006502000000', 'date_establish': '2012-05-10' }, { 'subject_name': 'SHFE 螺纹钢', 'regex': r"RB\.SHF", 'sectorid': 'a599010206000000', 'date_establish': '2009-03-27' }, { 'subject_name': 'SHFE 热卷', 'regex': r"HC\.SHF", 'sectorid': '1000011455000000', 'date_establish': '2014-03-21' }, { 'subject_name': 'SHFE 沥青', 'regex': r"BU\\.SHF", 'sectorid': '1000011013000000', 'date_establish': '2013-10-09' }, { 'subject_name': '原油', 'regex': r"SC\.SHF", 'sectorid': '1000011463000000', 'date_establish': '2018-03-26' }, #郑商所合约 # {'subject_name': '白糖', 'regex': r"SR\d{3,4}\.CZC", # 'sectorid': 'a599010405000000', 'date_establish': '2006-01-06'}, # {'subject_name': '棉花', 'regex': r"CF\d{3,4}\.CZC", # 'sectorid': 'a599010404000000', 'date_establish': '2004-06-01'}, # {'subject_name': '动力煤', 'regex': r"(ZC|TC)\d{3,4}\.CZC", # 'sectorid': '1000011012000000', 'date_establish': '2013-09-26'}, # {'subject_name': '玻璃', 'regex': r"FG\d{3,4}\.CZC", # 'sectorid': '1000008549000000', 'date_establish': '2013-12-03'}, # {'subject_name': '精对苯二甲酸', 'regex': r"TA\d{3,4}\.CZC", # 'sectorid': 'a599010407000000', 'date_establish': '2006-12-18'}, # {'subject_name': '甲醇', 'regex': r"(ME|MA)\d{3,4}\.CZC", # 'sectorid': '1000005981000000', 'date_establish': '2011-10-28'}, # {'subject_name': '菜籽油', 'regex': r"OI\d{3,4}\.CZC", # 'sectorid': 'a599010408000000', 'date_establish': '2007-06-08'}, # {'subject_name': '菜籽粕', 'regex': r"RM\d{3,4}\.CZC", # 'sectorid': '1000008622000000', 'date_establish': '2012-12-28'}, #郑商所指数 { 'subject_name': '白糖', 'regex': r"SR\.CZC", 'sectorid': 'a599010405000000', 'date_establish': '2006-01-06' }, { 'subject_name': '棉花', 'regex': r"CF\.CZC", 'sectorid': 'a599010404000000', 'date_establish': '2004-06-01' }, { 'subject_name': '动力煤', 'regex': r"(ZC|TC)\.CZC", 'sectorid': '1000011012000000', 'date_establish': '2013-09-26' }, { 'subject_name': '玻璃', 'regex': r"FG\.CZC", 'sectorid': '1000008549000000', 'date_establish': '2013-12-03' }, { 'subject_name': '精对苯二甲酸', 'regex': r"TA\.CZC", 'sectorid': 'a599010407000000', 'date_establish': '2006-12-18' }, { 'subject_name': '甲醇', 'regex': r"(ME|MA)\.CZC", 'sectorid': '1000005981000000', 'date_establish': '2011-10-28' }, { 'subject_name': '菜籽油', 'regex': r"OI\.CZC", 'sectorid': 'a599010408000000', 'date_establish': '2007-06-08' }, { 'subject_name': '菜籽粕', 'regex': r"RM\.CZC", 'sectorid': '1000008622000000', 'date_establish': '2012-12-28' }, ] wind_code_set = set() ndays_per_update = 60 # 获取接口参数以及参数列表 col_name_param_list = [ ("ipo_date", Date), ("sec_name", String(50)), ("sec_englishname", String(200)), ("exch_eng", String(200)), ("lasttrade_date", Date), ("lastdelivery_date", Date), ("dlmonth", String(20)), ("lprice", DOUBLE), ("sccode", String(20)), ("margin", DOUBLE), ("punit", String(200)), ("changelt", DOUBLE), ("mfprice", DOUBLE), ("contractmultiplier", DOUBLE), ("ftmargins", String(100)), ("trade_code", String(200)), ] wind_indictor_str = ",".join(col_name for col_name, _ in col_name_param_list) dtype = {key: val for key, val in col_name_param_list} dtype['wind_code'] = String(20) # 获取历史期货合约列表信息 logger.info("获取历史期货合约列表信息") for future_sectorid_dic in future_sectorid_dic_list: subject_name = future_sectorid_dic['subject_name'] sector_id = future_sectorid_dic['sectorid'] regex_str = future_sectorid_dic['regex'] date_establish = datetime.strptime( future_sectorid_dic['date_establish'], STR_FORMAT_DATE).date() date_since = get_date_since(wind_code_ipo_date_dic, regex_str, date_establish) date_yestoday = date.today() - timedelta(days=1) logger.info("%s[%s] %s ~ %s", subject_name, sector_id, date_since, date_yestoday) while date_since <= date_yestoday: date_since_str = date_since.strftime(STR_FORMAT_DATE) future_info_df = invoker.wset( "sectorconstituent", "date=%s;sectorid=%s" % (date_since_str, sector_id)) data_count = 0 if future_info_df is None else future_info_df.shape[ 0] logger.info("subject_name=%s[%s] %s 返回 %d 条数据", subject_name, sector_id, date_since_str, data_count) if data_count > 0: wind_code_set |= set(future_info_df['wind_code']) if date_since >= date_yestoday: break else: date_since += timedelta(days=ndays_per_update) if date_since > date_yestoday: date_since = date_yestoday # 获取合约列表 wind_code_list = [ wc for wc in wind_code_set if wc not in wind_code_ipo_date_dic ] # 获取合约基本信息 # w.wss("AU1706.SHF,AG1612.SHF,AU0806.SHF", "ipo_date,sec_name,sec_englishname,exch_eng,lasttrade_date,lastdelivery_date,dlmonth,lprice,sccode,margin,punit,changelt,mfprice,contractmultiplier,ftmargins,trade_code") if len(wind_code_list) > 0: logger.info("%d wind_code will be invoked by wss, wind_code_list=%s", len(wind_code_list), wind_code_list) future_info_df = invoker.wss(wind_code_list, wind_indictor_str) future_info_df['MFPRICE'] = future_info_df['MFPRICE'].apply( mfprice_2_num) future_info_count = future_info_df.shape[0] future_info_df.rename( columns={c: str.lower(c) for c in future_info_df.columns}, inplace=True) future_info_df.index.rename('wind_code', inplace=True) future_info_df.reset_index(inplace=True) data_count = bunch_insert_on_duplicate_update(future_info_df, table_name, engine_md, dtype=dtype) logging.info("更新 %s 结束 %d 条信息被更新", table_name, data_count) if not has_table and engine_md.has_table(table_name): alter_table_2_myisam(engine_md, [table_name]) build_primary_key([table_name]) logger.info("更新 wind_future_info 结束 %d 条记录被更新", future_info_count) update_from_info_table(table_name)
def import_private_fund_info(table_name, chain_param=None, get_df=False): # 初始化服务器接口,用于下载万得数据 # table_name = 'fund_info' has_table = engine_md.has_table(table_name) types = {u'股票多头策略': 1000023122000000, u'股票多空策略': 1000023123000000, u'其他股票策略': 1000023124000000, u'阿尔法策略': 1000023125000000, u'其他市场中性策略': 1000023126000000, u'事件驱动策略': 1000023113000000, u'债券策略': 1000023114000000, u'套利策略': 1000023115000000, u'宏观策略': 1000023116000000, u'管理期货': 1000023117000000, u'组合基金策略': 1000023118000000, u'货币市场策略': 1000023119000000, u'多策略': 100002312000000, u'其他策略': 1000023121000000} df = pd.DataFrame() today = date.today().strftime('%Y-%m-%d') param_list = [ ('FUND_SETUPDATE', Date), ('FUND_MATURITYDATE', Date), ('FUND_MGRCOMP', String(800)), ('FUND_EXISTINGYEAR', String(500)), ('FUND_PTMYEAR', String(30)), ('FUND_TYPE', String(20)), ('FUND_FUNDMANAGER', String(500)), ('SEC_NAME', String(2000)), ('STRATEGY_TYPE', String(200)), ('TRADE_DATE_LATEST', String(200)), ] col_name_dic = {col_name.upper(): col_name.lower() for col_name, _ in param_list} # 获取列表名 col_name_list = [col_name.lower() for col_name in col_name_dic.keys()] param_str = ",".join(col_name_list[:8]) # 设置dtype类型 dtype = {key.lower(): val for key, val in param_list} dtype['wind_code'] = String(20) for i in types.keys(): temp = invoker.wset("sectorconstituent", "date=%s;sectorid=%s" % (today, str(types[i]))) temp['strategy_type'] = i df = pd.concat([df, temp], axis=0) if DEBUG and len(df) > 1000: break # 插入数据库 # 初始化数据库engine # 整理数据 fund_types_df = df[['wind_code', 'sec_name', 'strategy_type']] fund_types_df.set_index('wind_code', inplace=True) # 获取基金基本面信息 code_list = list(fund_types_df.index) # df['wind_code'] code_count = len(code_list) seg_count = 5000 info_df = pd.DataFrame() for n in range(int(code_count / seg_count) + 1): num_start = n * seg_count num_end = (n + 1) * seg_count num_end = num_end if num_end <= code_count else code_count if num_start <= code_count: codes = ','.join(code_list[num_start:num_end]) # 分段获取基金成立日期数据 info2_df = invoker.wss(codes, param_str) logging.info('%05d ) [%d %d]' % (n, num_start, num_end)) info_df = info_df.append(info2_df) if DEBUG and len(info_df) > 1000: break else: break # 整理数据插入数据库) info_df['FUND_SETUPDATE'] = info_df['FUND_SETUPDATE'].apply(lambda x: str_2_date(x)) info_df['FUND_MATURITYDATE'] = info_df['FUND_MATURITYDATE'].apply(lambda x: str_2_date(x)) info_df = fund_types_df.join(info_df, how='right') info_df.rename(columns=col_name_dic, inplace=True) info_df['trade_date_latest'] = None info_df.index.names = ['wind_code'] info_df.reset_index(inplace=True) info_df.drop_duplicates(inplace=True) bunch_insert_on_duplicate_update(info_df, table_name, engine_md, dtype=dtype) logging.info('%d funds inserted' % len(info_df)) if not has_table and engine_md.has_table(table_name): alter_table_2_myisam(engine_md, [table_name]) build_primary_key([table_name]) # 更新 code_mapping 表 update_from_info_table(table_name) if get_df: return info_df
def import_private_fund_info(chain_param=None): """ :param chain_param: 在celery 中將前面結果做爲參數傳給後面的任務 :return: """ # 更新 基金信息 table_name = 'wind_fund_info' # 初始化服务器接口,用于下载万得数据 # 初始化数据库,并获取旧表信息 old = pd.read_sql_query('select wind_code from %s' % table_name, engine_md) old_set = set(old['wind_code']) # 从万得获取最新基金列表 types = { # u'股票多头策略': 1000023122000000, # u'股票多空策略': 1000023123000000, u'其他股票策略': 1000023124000000, # u'阿尔法策略': 1000023125000000, u'其他市场中性策略': 1000023126000000, # u'事件驱动策略': 1000023113000000, # u'债券策略': 1000023114000000, u'套利策略': 1000023115000000, # u'宏观策略': 1000023116000000, # u'管理期货策略': 1000023117000000, # u'组合基金策略': 1000023118000000, # u'货币市场策略': 1000023119000000, # u'多策略': 100002312000000, u'其他策略': 1000023121000000} param_list = [ ("sec_name", String(200)), ("fund_setupdate", Date), ("fund_maturitydate", Date), ("fund_mgrcomp", Date), ("fund_existingyear", Date), ("fund_ptmyear", Date), ("fund_type", Date), ("fund_fundmanager", Date), ] wind_indictor_str = ",".join([key for key, _ in param_list]) dtype = {col_name: val for col_name, val in param_list} dtype['wind_code'] = String(200) df = pd.DataFrame() yestday = (date.today() - timedelta(days=1)).strftime('%Y-%m-%d') for i in types.keys(): temp = invoker.wset("sectorconstituent", "date=%s;sectorid=%s" % (yestday, str(types[i]))) temp['strategy_type'] = i logging.info('%s sectorconstituent %s df.shape:%s', yestday, i, temp.shape) df = pd.concat([df, temp], axis=0) if DEBUG and len(df) > 1: break fund_types_df = df[['wind_code', 'strategy_type']] # , 'sec_name' 后续wss接口可以获得 new_set = set(fund_types_df['wind_code']) target_set = new_set.difference(old_set) # in new_set but not old_set # fund_types_df.set_index('wind_code', inplace=True) # fund_info_df.index.names = ['wind_code'] # # 获取新成立基金各项基本面信息 fund_info_df = get_fund_info_df_by_wind(list(target_set), wind_indictor_str) # 115 fund_info_df = fund_types_df.join(fund_info_df, how='right') return save_fund_info(fund_info_df, dtype)
def import_future_info(chain_param=None): """ 更新期货合约列表信息 :param chain_param: 在celery 中將前面結果做爲參數傳給後面的任務 :return: """ table_name = "wind_future_info" has_table = engine_md.has_table(table_name) logger.info("更新 %s 开始", table_name) # 获取已存在合约列表 if has_table: sql_str = 'select wind_code, ipo_date from {table_name}'.format( table_name=table_name) with with_db_session(engine_md) as session: table = session.execute(sql_str) wind_code_ipo_date_dic = dict(table.fetchall()) else: wind_code_ipo_date_dic = {} # 通过wind获取合约列表 # w.start() # 初始化服务器接口,用于下载万得数据 future_sectorid_dic_list = [ { 'subject_name': 'CFE 沪深300', 'regex': r"IF\d{4}\.CFE", 'sectorid': 'a599010102000000', 'date_establish': '2010-4-16' }, { 'subject_name': 'CFE 上证50', 'regex': r"IH\d{4}\.CFE", 'sectorid': '1000014871000000', 'date_establish': '2015-4-16' }, { 'subject_name': 'CFE 中证500', 'regex': r"IC\d{4}\.CFE", 'sectorid': '1000014872000000', 'date_establish': '2015-4-16' }, { 'subject_name': 'SHFE 黄金', 'regex': r"AU\d{4}\.SHF", 'sectorid': 'a599010205000000', 'date_establish': '2008-01-09' }, { 'subject_name': 'SHFE 沪银', 'regex': r"AG\d{4}\.SHF", 'sectorid': '1000006502000000', 'date_establish': '2012-05-10' }, { 'subject_name': 'SHFE 螺纹钢', 'regex': r"RB\d{4}\.SHF", 'sectorid': 'a599010206000000', 'date_establish': '2009-03-27' }, { 'subject_name': 'SHFE 热卷', 'regex': r"HC\d{4}\.SHF", 'sectorid': '1000011455000000', 'date_establish': '2014-03-21' }, { 'subject_name': 'DCE 焦炭', 'regex': r"J\d{4}\.SHF", 'sectorid': '1000002976000000', 'date_establish': '2011-04-15' }, { 'subject_name': 'DCE 焦煤', 'regex': r"JM\d{4}\.SHF", 'sectorid': '1000009338000000', 'date_establish': '2013-03-22' }, { 'subject_name': '铁矿石', 'regex': r"I\d{4}\.SHF", 'sectorid': '1000006502000000', 'date_establish': '2013-10-18' }, { 'subject_name': '天然橡胶', 'regex': r"RU\d{4}\.SHF", 'sectorid': 'a599010208000000', 'date_establish': '1995-06-01' }, { 'subject_name': '铜', 'regex': r"CU\d{4}\.SHF", 'sectorid': 'a599010202000000', 'date_establish': '1995-05-01' }, { 'subject_name': '铝', 'regex': r"AL\d{4}\.SHF", 'sectorid': 'a599010203000000', 'date_establish': '1995-05-01' }, { 'subject_name': '锌', 'regex': r"ZN\d{4}\.SHF", 'sectorid': 'a599010204000000', 'date_establish': '2007-03-26' }, { 'subject_name': '铅', 'regex': r"PB\d{4}\.SHF", 'sectorid': '1000002892000000', 'date_establish': '2011-03-24' }, { 'subject_name': '镍', 'regex': r"NI\d{4}\.SHF", 'sectorid': '1000011457000000', 'date_establish': '2015-03-27' }, { 'subject_name': '锡', 'regex': r"SN\d{4}\.SHF", 'sectorid': '1000011458000000', 'date_establish': '2015-03-27' }, { 'subject_name': '白糖', 'regex': r"SR\d{4}\.CZC", 'sectorid': 'a599010405000000', 'date_establish': '2006-01-06' }, { 'subject_name': '棉花', 'regex': r"CF\d{4}\.CZC", 'sectorid': 'a599010404000000', 'date_establish': '2004-06-01' }, { 'subject_name': '棉花', 'regex': r"CF\d{4}\.CZC", 'sectorid': 'a599010404000000', 'date_establish': '2004-06-01' }, ] wind_code_set = set() ndays_per_update = 60 # 获取接口参数以及参数列表 col_name_param_list = [ ("ipo_date", Date), ("sec_name", String(50)), ("sec_englishname", String(200)), ("exch_eng", String(200)), ("lasttrade_date", Date), ("lastdelivery_date", Date), ("dlmonth", String(20)), ("lprice", DOUBLE), ("sccode", String(20)), ("margin", DOUBLE), ("punit", String(200)), ("changelt", DOUBLE), ("mfprice", DOUBLE), ("contractmultiplier", DOUBLE), ("ftmargins", String(100)), ("trade_code", String(200)), ] wind_indictor_str = ",".join(col_name for col_name, _ in col_name_param_list) dtype = {key: val for key, val in col_name_param_list} dtype['wind_code'] = String(20) # 获取历史期货合约列表信息 for future_sectorid_dic in future_sectorid_dic_list: subject_name = future_sectorid_dic['subject_name'] sector_id = future_sectorid_dic['sectorid'] regex_str = future_sectorid_dic['regex'] date_establish = datetime.strptime( future_sectorid_dic['date_establish'], STR_FORMAT_DATE).date() date_since = get_date_since(wind_code_ipo_date_dic, regex_str, date_establish) date_yestoday = date.today() - timedelta(days=1) while date_since <= date_yestoday: date_since_str = date_since.strftime(STR_FORMAT_DATE) future_info_df = invoker.wset( "sectorconstituent", "date=%s;sectorid=%s" % (date_since_str, sector_id)) wind_code_set |= set(future_info_df['wind_code']) if date_since >= date_yestoday: break else: date_since += timedelta(days=ndays_per_update) if date_since > date_yestoday: date_since = date_yestoday # 获取合约列表 wind_code_list = [ wc for wc in wind_code_set if wc not in wind_code_ipo_date_dic ] # 获取合约基本信息 # w.wss("AU1706.SHF,AG1612.SHF,AU0806.SHF", "ipo_date,sec_name,sec_englishname,exch_eng,lasttrade_date,lastdelivery_date,dlmonth,lprice,sccode,margin,punit,changelt,mfprice,contractmultiplier,ftmargins,trade_code") if len(wind_code_list) > 0: future_info_df = invoker.wss(wind_code_list, wind_indictor_str) future_info_df['MFPRICE'] = future_info_df['MFPRICE'].apply( mfprice_2_num) future_info_count = future_info_df.shape[0] future_info_df.rename( columns={c: str.lower(c) for c in future_info_df.columns}, inplace=True) future_info_df.index.rename('wind_code', inplace=True) future_info_df.reset_index(inplace=True) data_count = bunch_insert_on_duplicate_update(future_info_df, table_name, engine_md, dtype=dtype) logging.info("更新 %s 结束 %d 条信息被更新", table_name, data_count) if not has_table and engine_md.has_table(table_name): alter_table_2_myisam(engine_md, [table_name]) build_primary_key([table_name]) logger.info("更新 wind_future_info_hk 结束 %d 条记录被更新", future_info_count) update_from_info_table(table_name)
def wind_CS_sector_update(chain_param=None): """ :param chain_param: 在celery 中將前面結果做爲參數傳給後面的任務 :return: """ dic = { u'CS石油石化': 'b101000000000000', u'CS煤炭': 'b102000000000000', u'CS有色金属': 'b103000000000000', u'CS电力及公用事业': 'b104000000000000', u'CS钢铁': 'b105000000000000', u'CS基础化工': 'b106000000000000', u'CS建筑': 'b107000000000000', u'CS建材': 'b108000000000000', u'CS轻工制造': 'b109000000000000', u'CS机械': 'b10a000000000000', u'CS电力设备': 'b10b000000000000', u'CS国防军工': 'b10c000000000000', u'CS汽车': 'b10d000000000000', u'CS商贸零售': 'b10e000000000000', u'CS餐饮旅游': 'b10f000000000000', u'CS家电': 'b10g000000000000', u'CS纺织服装': 'b10h000000000000', u'CS医药': 'b10i000000000000', u'CS食品饮料': 'b10j000000000000', u'CS农林牧鱼': 'b10k000000000000', u'CS银行': 'b10l000000000000', u'CS证券II': 'b10m010000000000', u'CS保险II': 'b10m020000000000', u'CS信托及其他': 'b10m030000000000', u'CS房地产': 'b10n000000000000', u'CS交通运输': 'b10o000000000000', u'CS电子元器件': 'b10p000000000000', u'CS通信': 'b10q000000000000', u'CS计算机': 'b10r000000000000', u'CS传媒': 'b10s000000000000', u'CS综合': 'b10t000000000000' } table_name = 'wind_CS_sector' has_table = engine_md.has_table(table_name) param_list = [ ('sector', String(20)), ('date', Date), ] dtype = {col_name: val for col_name, val in param_list} dtype['wind_code'] = String(20) info = pd.read_sql_query( 'select sector, max(date) as last_date from wind_CS_sector group by sector', engine_md) info.set_index('sector', inplace=True) for sector in info.index: begin_date = info.loc[sector, 'last_date'] + timedelta(days=1) week_ends = invoker.tdays(beginTime=begin_date, endTime=date.today(), options='Period=W').Times ## for week_end in week_ends: for sector in dic.keys(): df = invoker.wset( "sectorconstituent", "date=%s;sectorid=%s" % (week_end.date().strftime('%Y-%m-%d'), dic[sector])) if len(df) == 0: continue df['sector'] = sector df.set_index(['sector', 'date', 'wind_code'], inplace=True) bunch_insert_on_duplicate_update(df, table_name, engine_md, dtype=dtype) logging.info('Success import %s - %s' % (week_end, sector)) if not has_table and engine_md.has_table(table_name): alter_table_2_myisam(engine_md, [table_name]) build_primary_key([table_name])
def import_future_info(chain_param=None): """ 更新期货合约列表信息 :param chain_param: 在celery 中將前面結果做爲參數傳給後面的任務 :return: """ table_name = "wind_future_info" has_table = engine_md.has_table(table_name) logger.info("更新 %s 开始", table_name) # 获取已存在合约列表 if has_table: sql_str = 'select wind_code, ipo_date from {table_name}'.format( table_name=table_name) with with_db_session(engine_md) as session: table = session.execute(sql_str) wind_code_ipo_date_dic = dict(table.fetchall()) else: wind_code_ipo_date_dic = {} # 按交易所获取合约列表 # 上期所 # w.wset("sectorconstituent","date=1995-05-10;sectorid=a599010201000000") # 金交所 # w.wset("sectorconstituent","date=2013-09-10;sectorid=a599010101000000") # 大商所 # w.wset("sectorconstituent","date=1999-01-10;sectorid=a599010301000000") # 郑商所 # w.wset("sectorconstituent","date=1999-01-10;sectorid=a599010401000000") exchange_sectorid_dic_list = [ { 'exch_eng': 'SHFE', 'exchange_name': '上期所', 'sectorid': 'a599010201000000', 'date_establish': '1995-05-10' }, { 'exch_eng': 'CFFEX', 'exchange_name': '金交所', 'sectorid': 'a599010101000000', 'date_establish': '2013-09-10' }, { 'exch_eng': 'DCE', 'exchange_name': '大商所', 'sectorid': 'a599010301000000', 'date_establish': '1999-01-10' }, { 'exch_eng': 'CZCE', 'exchange_name': '郑商所', 'sectorid': 'a599010401000000', 'date_establish': '1999-01-10' }, ] exchange_latest_ipo_date_dic = get_exchange_latest_data() wind_code_set = set() ndays_per_update = 90 # 获取接口参数以及参数列表 col_name_param_list = [ ("ipo_date", Date), ("sec_name", String(50)), ("sec_englishname", String(200)), ("exch_eng", String(200)), ("lasttrade_date", Date), ("lastdelivery_date", Date), ("dlmonth", String(20)), ("lprice", DOUBLE), ("sccode", String(20)), ("margin", DOUBLE), ("punit", String(200)), ("changelt", DOUBLE), ("mfprice", DOUBLE), ("contractmultiplier", DOUBLE), ("ftmargins", String(100)), ("trade_code", String(200)), ] wind_indictor_str = ",".join(col_name for col_name, _ in col_name_param_list) dtype = {key: val for key, val in col_name_param_list} dtype['wind_code'] = String(20) # 获取历史期货合约列表信息 logger.info("获取历史期货合约列表信息") for exchange_sectorid_dic in exchange_sectorid_dic_list: exchange_name = exchange_sectorid_dic['exchange_name'] exch_eng = exchange_sectorid_dic['exch_eng'] sector_id = exchange_sectorid_dic['sectorid'] date_establish = exchange_sectorid_dic['date_establish'] date_since = str_2_date( exchange_latest_ipo_date_dic.setdefault(exch_eng, date_establish)) date_yestoday = date.today() - timedelta(days=1) logger.info("%s[%s][%s] %s ~ %s", exchange_name, exch_eng, sector_id, date_since, date_yestoday) while date_since <= date_yestoday: date_since_str = date_since.strftime(STR_FORMAT_DATE) future_info_df = invoker.wset( "sectorconstituent", "date=%s;sectorid=%s" % (date_since_str, sector_id)) data_count = 0 if future_info_df is None else future_info_df.shape[ 0] logger.info("subject_name=%s[%s] %s 返回 %d 条数据", exchange_name, sector_id, date_since_str, data_count) if data_count > 0: wind_code_set |= set(future_info_df['wind_code']) if date_since >= date_yestoday: break else: date_since += timedelta(days=ndays_per_update) if date_since > date_yestoday: date_since = date_yestoday # 获取合约列表 wind_code_list = [ wc for wc in wind_code_set if wc not in wind_code_ipo_date_dic ] # 获取合约基本信息 # w.wss("AU1706.SHF,AG1612.SHF,AU0806.SHF", "ipo_date,sec_name,sec_englishname,exch_eng,lasttrade_date,lastdelivery_date,dlmonth,lprice,sccode,margin,punit,changelt,mfprice,contractmultiplier,ftmargins,trade_code") if len(wind_code_list) > 0: logger.info("%d wind_code will be invoked by wss, wind_code_list=%s", len(wind_code_list), wind_code_list) future_info_df = invoker.wss(wind_code_list, wind_indictor_str) future_info_df['MFPRICE'] = future_info_df['MFPRICE'].apply( mfprice_2_num) future_info_count = future_info_df.shape[0] future_info_df.rename( columns={c: str.lower(c) for c in future_info_df.columns}, inplace=True) future_info_df.index.rename('wind_code', inplace=True) future_info_df.reset_index(inplace=True) data_count = bunch_insert_on_duplicate_update(future_info_df, table_name, engine_md, dtype=dtype) logging.info("更新 %s 结束 %d 条信息被更新", table_name, data_count) if not has_table and engine_md.has_table(table_name): # alter_table_2_myisam(engine_md, [table_name]) build_primary_key([table_name]) logger.info("更新 wind_future_info 结束 %d 条记录被更新", future_info_count) update_from_info_table(table_name)