def import_jq_stock_info(chain_param=None, refresh=False): """ 获取全市场股票代码及名称 """ table_name = TABLE_NAME logging.info("更新 %s 开始", table_name) # has_table = engine_md.has_table(table_name) tushare_indicator_param_list = [ ('jq_code', String(20)), ('display_name', String(20)), ('name', String(20)), ('start_date', Date), ('end_date', Date), ] # 设置 dtype dtype = {key: val for key, val in tushare_indicator_param_list} # 数据提取 # types: list: 用来过滤securities的类型, list元素可选: # 'stock', 'fund', 'index', 'futures', 'etf', 'lof', 'fja', 'fjb'。types为空时返回所有股票, 不包括基金,指数和期货 # date: 日期, 一个字符串或者 [datetime.datetime]/[datetime.date] 对象, # 用于获取某日期还在上市的股票信息. 默认值为 None, 表示获取所有日期的股票信息 stock_info_all_df = get_all_securities() stock_info_all_df.index.rename('jq_code', inplace=True) stock_info_all_df.reset_index(inplace=True) logging.info('%s stock data will be import', stock_info_all_df.shape[0]) data_count = bunch_insert_on_duplicate_update( stock_info_all_df, table_name, engine_md, dtype=dtype, myisam_if_create_table=True, primary_keys=['jq_code'], schema=config.DB_SCHEMA_MD) logging.info("更新 %s 完成 存量数据 %d 条", table_name, data_count) # 更新 map 表 update_from_info_table(table_name)
def import_index_info(wind_codes, chain_param=None): """ 导入指数信息 :param wind_codes: :return: """ table_name = 'wind_index_info' has_table = engine_md.has_table(table_name) col_name_param_list = [ ('LAUNCHDATE', Date), ('BASEDATE', Date), ('BASEVALUE', DOUBLE), ('COUNTRY', String(20)), ('CRM_ISSUER', String(20)), ('SEC_NAME', String(20)), ] col_name_param = ",".join([key.lower() for key, _ in col_name_param_list]) col_name_param_dic = { col_name.upper(): col_name.lower() for col_name, _ in col_name_param_list } # 设置dtype类型 dtype = {key.lower(): val for key, val in col_name_param_list} dtype['wind_code'] = String(20) info_df = invoker.wss(wind_codes, col_name_param) if info_df is None or info_df.shape[0] == 0: logger.warning("没有数据可导入") return info_df.rename(columns=col_name_param_dic, inplace=True) 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) # info_df.to_sql(table_name, engine_md, if_exists='append', index=True, # dtype={ # 'wind_code': String(20), # 'null': Date, # 'basedate': Date, # 'basevalue': DOUBLE, # 'country': String(20), # 'crm_issuer': String(20), # 'sec_name': String(20), # }) logger.info('%d 条指数信息导入成功\n%s', info_df.shape[0], 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_index_info(chain_param=None, ths_code=None): """ 导入 info 表 :param chain_param: 该参数仅用于 task.chain 串行操作时,上下传递参数使用 :param ths_code: :param refresh: :return: """ table_name = 'ifind_index_info' has_table = engine_md.has_table(table_name) logging.info("更新 ifind_index_info 开始") if ths_code is None: # 获取全市场股票代码及名称 date_end = date.today() stock_code_set = set() stock_code_set_sub = get_stock_code_set(date_end) if stock_code_set_sub is not None: stock_code_set |= stock_code_set_sub ths_code = ','.join(stock_code_set) indicator_param_list = [ ('ths_index_short_name_index', '', String(20)), ('ths_index_code_index', '', String(10)), ('ths_index_category_index', '', String(20)), ('ths_index_base_period_index', '', Date), ('ths_index_base_point_index', '', DOUBLE), ('ths_publish_org_index', '', String(20)), ] # indicator' = 'ths_index_short_name_index;ths_index_code_index;ths_thscode_index;ths_index_category_index; # ths_index_base_period_index;ths_index_base_point_index;ths_publish_org_index', # param = ';;;;;;' indicator, param = unzip_join([(key, val) for key, val, _ in indicator_param_list], sep=';') data_df = invoker.THS_BasicData(ths_code, indicator, param) if data_df is None or data_df.shape[0] == 0: logging.info("没有可用的 index info 可以更新") return dtype = {key: val for key, _, val in indicator_param_list} dtype['ths_code'] = String(20) data_count = bunch_insert_on_duplicate_update(data_df, table_name, engine_md, 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]) # 更新 code_mapping 表 update_from_info_table(table_name)
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_stock_hk_info(chain_param=None, ths_code=None, refresh=False): """ 导入 info 表 :param chain_param: 该参数仅用于 task.chain 串行操作时,上下传递参数使用 :param ths_code: :param refresh: :return: """ table_name = 'ifind_stock_hk_info' logging.info("更新 %s 开始", table_name) if ths_code is None: # 获取全市场港股代码及名称 if refresh: date_fetch = datetime.strptime('1991-02-01', STR_FORMAT_DATE).date() else: date_fetch = date.today() date_end = date.today() stock_hk_code_set = set() while date_fetch < date_end: stock_hk_code_set_sub = get_stock_hk_code_set(date_fetch) if stock_hk_code_set_sub is not None: stock_hk_code_set |= stock_hk_code_set_sub date_fetch += timedelta(days=365) stock_hk_code_set_sub = get_stock_hk_code_set(date_end) if stock_hk_code_set_sub is not None: stock_hk_code_set |= stock_hk_code_set_sub if DEBUG: stock_hk_code_set = list(stock_hk_code_set)[:10] ths_code = ','.join(stock_hk_code_set) indicator_param_list = [ ('ths_stock_short_name_hks', '', String(40)), ('ths_stock_code_hks', '', String(20)), ('ths_isin_code_hks', '', String(40)), ('ths_corp_ashare_short_name_hks', '', String(10)), ('ths_corp_ashare_code_hks', '', String(60)), ('ths_stock_varieties_hks', '', String(40)), ('ths_ipo_date_hks', '', Date), ('ths_listed_exchange_hks', '', String(60)), ('ths_stop_listing_date_hks', '', Date), ('ths_corp_cn_name_hks', '', String(120)), ('ths_corp_name_en_hks', '', String(120)), ('ths_established_date_hks', '', Date), ('ths_accounting_date_hks', '', String(20)), ('ths_general_manager_hks', '', String(40)), ('ths_secretary_hks', '', String(40)), ('ths_operating_scope_hks', '', Text), ('ths_mo_product_name_hks', '', String(200)), ('ths_district_hks', '', String(60)), ('ths_reg_address_hks', '', String(200)), ('ths_office_address_hks', '', String(200)), ('ths_corp_tel_hks', '', String(200)), ('ths_corp_fax_hks', '', String(200)), ('ths_corp_website_hks', '', String(200)), ('ths_auditor_hks', '', String(60)), ('ths_legal_counsel_hks', '', String(300)), ('ths_hs_industry_hks', '', String(40)), ] # jsonIndicator='ths_stock_short_name_hks;ths_stock_code_hks;ths_thscode_hks;ths_isin_code_hks;ths_corp_ashare_short_name_hks;ths_corp_ashare_code_hks;ths_stock_varieties_hks;ths_ipo_date_hks;ths_listed_exchange_hks;ths_stop_listing_date_hks;ths_corp_cn_name_hks;ths_corp_name_en_hks;ths_established_date_hks;ths_accounting_date_hks;ths_general_manager_hks;ths_secretary_hks;ths_operating_scope_hks;ths_mo_product_name_hks;ths_district_hks;ths_reg_address_hks;ths_office_address_hks;ths_corp_tel_hks;ths_corp_fax_hks;ths_corp_website_hks;ths_auditor_hks;ths_legal_counsel_hks;ths_hs_industry_hks' # jsonparam=';;;;;;;;;;;' indicator, param = unzip_join([(key, val) for key, val, _ in indicator_param_list], sep=';') param += '100' data_df = invoker.THS_BasicData(ths_code, indicator, param) if data_df is None or data_df.shape[0] == 0: logging.info("没有可用的 stock_hk info 可以更新") return # 删除历史数据,更新数据 has_table = engine_md.has_table(table_name) if has_table: with with_db_session(engine_md) as session: session.execute( "DELETE FROM {table_name} WHERE ths_code IN (".format( table_name=table_name) + ','.join( [':code%d' % n for n in range(len(stock_hk_code_set))]) + ")", params={ 'code%d' % n: val for n, val in enumerate(stock_hk_code_set) }) session.commit() dtype = {key: val for key, _, val in indicator_param_list} dtype['ths_code'] = String(20) # data_count = data_df.shape[0] # data_df.to_sql(table_name, engine_md, if_exists='append', index=False, dtype=dtype) data_count = bunch_insert_on_duplicate_update(data_df, table_name, engine_md, 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]) # 更新 code_mapping 表 update_from_info_table(table_name)
def import_wind_stock_info(chain_param=None, refresh=False): """ :param chain_param: 在celery 中將前面結果做爲參數傳給後面的任務 :param refresh:获取全市场股票代码及名称 :return: """ table_name = 'wind_stock_info' logging.info("更新 %s 开始", table_name) has_table = engine_md.has_table(table_name) wind_indicator_param_list = [ ('sec_name', String(20)), ('trade_code', String(20)), ('ipo_date', Date), ('delist_date', Date), ('mkt', String(20)), ('exch_city', String(20)), ('exch_eng', String(20)), ('prename', String(2000)), ] # 获取列属性名,以逗号进行分割 "ipo_date,trade_code,mkt,exch_city,exch_eng" param = ",".join([key for key, _ in wind_indicator_param_list]) # 设置 dtype dtype = {key: val for key, val in wind_indicator_param_list} dtype['wind_code'] = String(20) if refresh: date_fetch = datetime.strptime('2005-1-1', STR_FORMAT_DATE).date() else: date_fetch = date.today() date_end = date.today() stock_code_set = set() # 对date_fetch 进行一个判断,获取stock_code_set while date_fetch < date_end: stock_code_set_sub = get_stock_code_set(date_fetch) if stock_code_set_sub is not None: stock_code_set |= stock_code_set_sub date_fetch += timedelta(days=365) stock_code_set_sub = get_stock_code_set(date_end) if stock_code_set_sub is not None: stock_code_set |= stock_code_set_sub # 获取股票对应上市日期,及摘牌日期 # w.wss("300005.SZ,300372.SZ,000003.SZ", "ipo_date,trade_code,mkt,exch_city,exch_eng") stock_code_list = list(stock_code_set) seg_count = 1000 stock_info_df_list = [] # 进行循环遍历获取stock_code_list_sub for stock_code_list_sub in split_chunk(stock_code_list, seg_count): # 尝试将 stock_code_list_sub 直接传递给wss,是否可行 stock_info_df = invoker.wss(stock_code_list_sub, param) stock_info_df_list.append(stock_info_df) if DEBUG: break # 对数据表进行规范整理.整合,索引重命名 stock_info_all_df = pd.concat(stock_info_df_list) stock_info_all_df.index.rename('wind_code', inplace=True) logging.info('%d data will be import', stock_info_all_df.shape[0]) stock_info_all_df.reset_index(inplace=True) # data_list = list(stock_info_all_df.T.to_dict().values()) # 对wind_stock_info表进行数据插入 # sql_str = "REPLACE INTO {table_name} (wind_code, trade_code, sec_name, ipo_date, delist_date, mkt, exch_city, exch_eng, prename) values (:WIND_CODE, :TRADE_CODE, :SEC_NAME, :IPO_DATE, :DELIST_DATE, :MKT, :EXCH_CITY, :EXCH_ENG, :PRENAME)" # 事物提交执行更新 # with with_db_session(engine_md) as session: # session.execute(sql_str, data_list) # data_count = session.execute('select count(*) from {table_name}').scalar() data_count = bunch_insert_on_duplicate_update(stock_info_all_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]) # 更新 code_mapping 表 update_from_info_table(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 = {} # 通过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_stock_info_hk(chain_param=None, refresh=False): """ 获取全市场股票代码及名称 导入 港股股票信息 到 wind_stock_info_hk :param chain_param: 在celery 中將前面結果做爲參數傳給後面的任務 :param refresh: 默认为False,True 则进行全部更新 :return: """ table_name = 'wind_stock_info_hk' logging.info("更新 %s 开始", table_name) has_table = engine_md.has_table(table_name) param_list = [ ('sec_name', String(20)), ('trade_code', String(20)), ('ipo_date', Date), ('delist_date', Date), ('mkt', String(20)), ('exch_city', String(20)), ('exch_eng', String(20)), ('prename', String(2000)), ] # 获取列属性名,以逗号进行分割 param = ",".join([key for key, _ in param_list]) rename_col_dic = {key.upper(): key.lower() for key, _ in param_list} # 设置 dtype dtype = {key: val for key, val in param_list} dtype['wind_code'] = String(20) if refresh: date_fetch = DATE_BASE else: date_fetch = date.today() date_end = date.today() stock_code_set = set() while date_fetch < date_end: stock_code_set_sub = get_stock_code_set(date_fetch) if stock_code_set_sub is not None: stock_code_set |= stock_code_set_sub date_fetch += timedelta(days=365) stock_code_set_sub = get_stock_code_set(date_end) if stock_code_set_sub is not None: stock_code_set |= stock_code_set_sub # 获取股票对应上市日期,及摘牌日期 # w.wss("300005.SZ,300372.SZ,000003.SZ", "ipo_date,trade_code,mkt,exch_city,exch_eng") stock_code_list = list(stock_code_set) seg_count = 1000 stock_info_df_list = [] for stock_code_list_sub in split_chunk(stock_code_list, seg_count): # 尝试将 stock_code_list_sub 直接传递给wss,是否可行 # stock_info_df = invoker.wss(stock_code_list_sub, # "sec_name,trade_code,ipo_date,delist_date,mkt,exch_city,exch_eng,prename") # 获取接口文档数据信息 stock_info_df = invoker.wss(stock_code_list_sub, param) stock_info_df_list.append(stock_info_df) if DEBUG: break stock_info_all_df = pd.concat(stock_info_df_list) stock_info_all_df.index.rename('wind_code', inplace=True) stock_info_all_df.rename(columns=rename_col_dic, inplace=True) logging.info('%d data will be import', stock_info_all_df.shape[0]) stock_info_all_df.reset_index(inplace=True) # data_list = list(stock_info_all_df.T.to_dict().values()) # sql_str = "REPLACE INTO {table_name} (wind_code, trade_code, sec_name, ipo_date, delist_date, mkt, exch_city, exch_eng, prename) values (:WIND_CODE, :TRADE_CODE, :SEC_NAME, :IPO_DATE, :DELIST_DATE, :MKT, :EXCH_CITY, :EXCH_ENG, :PRENAME)".format( # table_name=table_name # ) # # sql_str = "insert INTO wind_stock_info_hk (wind_code, trade_code, sec_name, ipo_date, delist_date, mkt, exch_city, exch_eng, prename) values (:WIND_CODE, :TRADE_CODE, :SEC_NAME, :IPO_DATE, :DELIST_DATE, :MKT, :EXCH_CITY, :EXCH_ENG, :PRENAME)" # with with_db_session(engine_md) as session: # session.execute(sql_str, data_list) # stock_count = session.execute('select count(*) from {table_name}'.format(table_name=table_name)).first()[0] # 创建表格数据 data_count = bunch_insert_on_duplicate_update(stock_info_all_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]) # 更新 code_mapping 表 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_pub_fund_info(chain_param=None, first_time=False): """ 获取全市场可转债基本信息 :param chain_param: 在celery 中將前面結果做爲參數傳給後面的任務 :param first_time: 第一次执行时将从2004年开始查找全部公募基金数据 :return: """ table_name = "wind_pub_fund_info" has_table = engine_md.has_table(table_name) if first_time: date_since = datetime.strptime('2004-01-01', STR_FORMAT_DATE).date() date_list = [] one_year = timedelta(days=365) while date_since < date.today() - ONE_DAY: date_list.append(date_since) date_since += one_year else: date_list.append(date.today() - ONE_DAY) else: date_list = [date.today() - ONE_DAY] # 获取 wind_code 集合 wind_code_set = set() for fetch_date in date_list: data_set = get_wind_code_set(fetch_date) if data_set is not None: wind_code_set |= data_set if DEBUG and len(wind_code_set) > 6: break if has_table: with with_db_session(engine_md) as session: sql_str = "select wind_code from %s" % table_name table = session.execute(sql_str) wind_code_set_existed = { content[0] for content in table.fetchall() } wind_code_set -= wind_code_set_existed else: wind_code_set = wind_code_set # 获取股票对应上市日期,及摘牌日期 # w.wss("300005.SZ,300372.SZ,000003.SZ", "ipo_date,trade_code,mkt,exch_city,exch_eng") wind_code_list = list(wind_code_set) wind_code_count = len(wind_code_list) seg_count = 1000 # loop_count = math.ceil(float(wind_code_count) / seg_count) data_info_df_list = [] fund_info_field_col_name_list = [ ('FUND_FULLNAME', String(200)), ('FUND_EXCHANGESHORTNAME', String(50)), ('FUND_BENCHMARK', String(200)), ('FUND_SETUPDATE', Date), ('FUND_MATURITYDATE', Date), ('FUND_FUNDMANAGER', String(500)), ('FUND_FUNDMANAGER', String(50)), ('FUND_MGRCOMP', String(200)), ('FUND_CUSTODIANBANK', String(50)), ('FUND_TYPE', String(50)), ('FUND_FIRSTINVESTTYPE', String(50)), ('FUND_INVESTTYPE', String(50)), ('FUND_STRUCTUREDFUNDORNOT', String(50)), ('FUND_BENCHINDEXCODE', String(50)), ] # col_name_dic = {col_name.upper(): col_name.lower() for col_name, _ in fund_info_field_col_name_list} # col_name_list = ",".join([col_name.lower() for col_name in col_name_dic.keys()]) dtype = {key.lower(): val for key, val in fund_info_field_col_name_list} dtype['wind_code'] = String(20) # dtype['invest_type_level1'] = String(50) for n in range(0, wind_code_count, seg_count): sub_list = wind_code_list[n:(n + seg_count)] # 尝试将 stock_code_list_sub 直接传递给wss,是否可行 # w.wss("000309.OF", "fund_fullname, fund_exchangeshortname,fund_benchmark,fund_benchindexcode,fund_setupdate, # fund_maturitydate,fund_fundmanager,fund_mgrcomp,fund_custodianbank,fund_type,fund_firstinvesttype, # fund_investtype,fund_structuredfundornot, # fund_investstyle") structuredfundornot field_str = ",".join([ col_name.lower() for col_name, _ in fund_info_field_col_name_list ]) stock_info_df = invoker.wss(sub_list, field_str) data_info_df_list.append(stock_info_df) if DEBUG and len(data_info_df_list) > 1000: break if len(data_info_df_list) == 0: logger.info("%s 没有数据可以导入", table_name) return data_info_all_df = pd.concat(data_info_df_list) data_info_all_df.index.rename('wind_code', inplace=True) data_info_all_df.rename( columns={col: col.lower() for col in data_info_all_df.columns}, inplace=True) logging.info('%d data will be import', data_info_all_df.shape[0]) data_info_all_df.reset_index(inplace=True) bunch_insert_on_duplicate_update(data_info_all_df, table_name, engine_md, dtype=dtype) if not has_table and engine_md.has_table(table_name): alter_table_2_myisam(engine_md, [table_name]) build_primary_key([table_name]) update_from_info_table(table_name)
def import_private_fund_info(chain_param=None, ths_code=None, refresh=False): """ 更新基础信息表 :param chain_param: 该参数仅用于 task.chain 串行操作时,上下传递参数使用 :param ths_code: :param refresh: :return: """ table_name = 'ifind_private_fund_info' has_table = engine_md.has_table(table_name) logging.info("更新 %s 开始", table_name) if ths_code is None: # 获取全市场私募基金代码及名称 date_end = date.today() private_fund_set = set() if not refresh and has_table: sql_str = "select ths_code, ths_maturity_date_sp from {table_name}".format(table_name=table_name) with with_db_session(engine_md) as session: code_in_db_dict = dict(session.execute(sql_str).fetchall()) code_in_db_set = set(code_in_db_dict.keys()) else: code_in_db_dict, code_in_db_set = {}, set() # 查找新增基金 code_set_exists = get_private_fund_set(date_end) if code_set_exists is not None: if not refresh and has_table: code_set_exists -= code_in_db_set private_fund_set |= code_set_exists # 查找已清盘基金 code_set_clear = get_private_fund_set(date_end, field='051010005') if code_set_clear is not None: if not refresh and has_table: code_set_clear -= set([key for key, val in code_in_db_dict.items() if val is not None]) private_fund_set |= code_set_clear ths_code = list(private_fund_set) if DEBUG: ths_code = ths_code[:10] indicator_param_list = [ ('ths_product_short_name_sp', '', String(80)), ('ths_product_full_name_sp', '', String(80)), ('ths_trust_category_sp', '', String(40)), ('ths_is_structured_product_sp', '', String(10)), ('ths_threshold_amt_sp', '', Integer), ('ths_low_add_amt_sp', '', Integer), ('ths_fore_max_issue_scale_sp', '', String(40)), ('ths_actual_issue_scale_sp', '', String(40)), ('ths_invest_manager_current_sp', '', String(60)), ('ths_mendator_sp', '', String(20)), ('ths_recommend_sd_sp', '', Date), ('ths_introduction_ed_sp', '', Date), ('ths_established_date_sp', '', Date), ('ths_maturity_date_sp', '', Date), ('ths_found_years_sp', '', Date), ('ths_duration_y_sp', '', Integer), ('ths_remain_duration_d_sp', '', Integer), ('ths_float_manage_rate_sp', '', DOUBLE), ('ths_mandate_fee_rate_sp', '', DOUBLE), ('ths_subscription_rate_explain_sp', '', String(300)), ('ths_redemp_rate_explain_sp', '', String(300)), ('ths_opening_period_explain_sp', '', String(300)), ('ths_close_period_explain_sp', '', String(300)), ('ths_trustee_sp', '', String(100)), ('ths_secbroker_sp', '', String(40)) ] # jsonIndicator='THS_BasicData('SM000008.XT','ths_product_short_name_sp;ths_product_full_name_sp;ths_trust_category_sp;ths_is_structured_product_sp;ths_threshold_amt_sp;ths_low_add_amt_sp;ths_fore_max_issue_scale_sp;ths_actual_issue_scale_sp;ths_invest_manager_current_sp;ths_invest_advisor_sp;ths_mendator_sp;ths_recommend_sd_sp;ths_introduction_ed_sp;ths_established_date_sp;ths_maturity_date_sp;ths_found_years_sp;ths_duration_y_sp;ths_remain_duration_d_sp;ths_float_manage_rate_sp;ths_mandate_fee_rate_sp;ths_subscription_rate_explain_sp;ths_redemp_rate_explain_sp;ths_opening_period_explain_sp;ths_close_period_explain_sp;ths_trustee_sp;ths_secbroker_sp' # jsonparam=';;;;;;;;;' indicator, param = unzip_join([(key, val) for key, val, _ in indicator_param_list], sep=';') data_df = invoker.THS_BasicData(ths_code, indicator, param, max_code_num=8000) if data_df is None or data_df.shape[0] == 0: logging.info("没有可用的数据可以更新") return dtype = {key: val for key, _, val in indicator_param_list} dtype['ths_code'] = String(20) data_count = bunch_insert_on_duplicate_update(data_df, table_name, engine_md, dtype) logging.info("更新 %s 完成 存量数据 %d 条", table_name, data_count) if not has_table: alter_table_2_myisam(engine_md, [table_name]) build_primary_key([table_name]) # 更新 code_mapping 表 update_from_info_table(table_name)
def import_cb_info(chain_param=None, first_time=False): """ 获取全市场可转债数据 :param chain_param: 在celery 中將前面結果做爲參數傳給後面的任務 :param first_time: 第一次执行时将从 1999 年开始查找全部基本信息 :return: """ table_name = 'wind_convertible_bond_info' has_table = engine_md.has_table(table_name) name_param_list = [ ('trade_code', DOUBLE), ('fullname', String(45)), ('sec_name', String(45)), ('issue_announcement', Date), ('ipo_date', Date), ('clause_conversion_2_swapsharestartdate', Date), ('clause_conversion_2_swapshareenddate', Date), ('clause_conversion_code', DOUBLE), ('clause_interest_5', String(8)), ('clause_interest_8', String(8)), ('clause_interest_6', String(200)), ('clause_interest_compensationinterest', DOUBLE), ('clause_interest_compensationinterest', DOUBLE), ('issueamount', DOUBLE), ('term', DOUBLE), ('underlyingcode', String(20)), ('underlyingname', String(20)), ('redemption_beginning', Date), ] param = ",".join([key for key, _ in name_param_list]) # 设置dtype类型 dtype = {key: val for key, val in name_param_list} dtype['wind_code'] = String(20) # if first_time: date_since = datetime.strptime('1999-01-01', STR_FORMAT_DATE).date() date_list = [] one_year = timedelta(days=365) while date_since < date.today() - ONE_DAY: date_list.append(date_since) date_since += one_year else: date_list.append(date.today() - ONE_DAY) else: date_list = [date.today() - ONE_DAY] # 获取 wind_code 集合 wind_code_set = set() for fetch_date in date_list: data_set = get_cb_set(fetch_date) if data_set is not None: wind_code_set |= data_set # 获取股票对应上市日期,及摘牌日期 # w.wss("300005.SZ,300372.SZ,000003.SZ", "ipo_date,trade_code,mkt,exch_city,exch_eng") wind_code_list = list(wind_code_set) # wind_code_count = len(wind_code_list) # seg_count = 1000 # loop_count = math.ceil(float(wind_code_count) / seg_count) data_info_df_list = [] try: for sub_list in split_chunk(wind_code_list, 1000): # num_start = n * seg_count # num_end = (n + 1) * seg_count # # num_end = num_end if num_end <= wind_code_count else wind_code_count # sub_list = wind_code_list[n:(n + seg_count)] # 尝试将 stock_code_list_sub 直接传递给wss,是否可行 data_df = invoker.wss(sub_list, param, "unit=1") if data_df is not None and data_df.shape[0] > 0: data_info_df_list.append(data_df) # 仅仅调试时使用 if DEBUG and len(data_info_df_list) > 1: break finally: if len(data_info_df_list) > 0: data_info_all_df = pd.concat(data_info_df_list) data_info_all_df.index.rename('wind_code', inplace=True) data_info_all_df.rename( columns={col: col.lower() for col in data_info_all_df.columns}, inplace=True) logging.info('%d data will be import', data_info_all_df.shape[0]) data_info_all_df.reset_index(inplace=True) data_count = bunch_insert_on_duplicate_update(data_info_all_df, table_name, engine_md, dtype=dtype) # logging.info("%d stocks have been in %s", len(data_info_all_df), table_name) 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]) # 更新 code_mapping 表 if engine_md.has_table(table_name): update_from_info_table(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 = {} # 通过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 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)
def import_pub_fund_info(chain_param=None, ths_code=None, refresh=False): """ 导入 info 表 :param chain_param: 该参数仅用于 task.chain 串行操作时,上下传递参数使用 :param ths_code: :param refresh: :return: """ table_name = 'ifind_pub_fund_info' has_table = engine_md.has_table(table_name) logging.info("更新 %s 开始", table_name) if ths_code is None: # 获取全市场公募代码及名称 if refresh: date_fetch = datetime.strptime('1991-02-01', STR_FORMAT_DATE).date() else: date_fetch = date.today() date_end = date.today() pub_fund_code_set = set() # 直接获取全部基金包含已成立,已到期的基金,因此不再需要循环 # while date_fetch < date_end: # pub_fund_code_set_sub = get_pub_fund_code_set(date_fetch) # if pub_fund_code_set_sub is not None: # pub_fund_code_set |= pub_fund_code_set_sub # date_fetch += timedelta(days=365) pub_fund_code_set_sub = get_pub_fund_code_set(date_end) if pub_fund_code_set_sub is not None: pub_fund_code_set |= pub_fund_code_set_sub ths_code = list(pub_fund_code_set) if DEBUG: # 该变量仅用于调试期间使用 ths_code_old = ths_code ths_code = ths_code[:40] indicator_param_list = [ ('ths_fund_short_name_fund', '', String(40)), ('ths_fund_code_fund', '', String(40)), ('ths_fund_thscode_fund', '', String(40)), ('ths_fund_full_name_fund', '', String(80)), ('ths_invest_objective_fund', '', String(500)), ('ths_invest_socpe_fund', '', Text), ('ths_perf_comparative_benchmark_fund', '', Text), ('ths_fund_listed_exchange_fund', '', String(40)), ('ths_fund_td_currency_fund', '', String(60)), ('ths_coupon_value_fund', '', String(40)), ('ths_fund_manager_current_fund', '', String(40)), ('ths_fund_manager_his_fund', '', String(400)), ('ths_fund_supervisor_fund', '', String(40)), ('ths_fund_mandator_fund', '', String(20)), ('ths_fund_sponsor_related_org_fund', '', String(40)), ('ths_fund_type_fund', '', String(10)), ('ths_fund_invest_type_fund', '', String(10)), ('ths_invest_type_first_classi_fund', '', String(40)), ('ths_invest_type_second_classi_fund', '', String(40)), ('ths_galaxy_classi_fund', '', String(300)), ('ths_hts_classi_fund', '', String(100)), ('ths_invest_style_fund', '', String(100)), ('ths_fund_duration_fund', '', String(40)), ('ths_fund_establishment_date_fund', '', Date), ('ths_fund_expiry_date_fund', '', Date), ('ths_redemp_sd_fund', '', String(40)), ('ths_mandate_sd_fund', '', String(40)), ('ths_manage_fee_rate_fund', '', String(40)), ('ths_mandate_fee_rate_fund', '', String(40)), ('ths_sales_service_fee_fund', '', String(40)), ('ths_high_pur_fee_rate_fund', '', String(20)), ('ths_high_redemp_fee_rate_fund', '', String(40)), ('ths_lof_listed_date_fund', '', Date), ('ths_lof_listed_td_share_fund', '', String(40)), ('ths_pm_fund_code_fund', '', String(40)), ('ths_par_short_name_fund', '', String(40)), ('ths_online_cash_sell_code_fund', '', String(40)), ('ths_online_cash_pur_sd_fund', '', String(40)), ('ths_online_cash_pur_ed_fund', '', String(40)), ('ths_online_cash_buy_share_ul_fund', '', String(40)), ('ths_online_cash_buy_share_dl_fund', '', String(40)), ('ths_offline_cash_pur_sd_fund', '', String(40)), ('ths_offline_cash_pur_ed_fund', '', String(40)), ('ths_offline_stock_pur_sd_fund', '', String(40)), ('ths_offline_stock_pur_ed_fund', '', String(40)), ('ths_offline_stock_pur_vol_dl_fund', '', String(40)), ('ths_fund_shares_convert_date_fund', '', String(40)), ('ths_fund_shares_convert_ratio_fund', '', String(40)), ('ths_issue_date_fund', '', Date), ('ths_issue_object_fund', '', String(100)), ('ths_issue_method_fund', '', String(80)), ('ths_fund_reg_and_registrant_fund', '', String(40)), ('ths_fund_main_underwrite_fund', '', String(40)), ('ths_fund_issue_coordinator_fund', '', String(500)), ('ths_fund_sales_agent_fund', '', Text), ('ths_fund_listing_recommended_fund', '', String(40)) ] # jsonIndicator='ths_fund_short_name_fund;ths_fund_code_fund;ths_fund_thscode_fund;ths_fund_full_name_fund;ths_invest_objective_fund;ths_invest_socpe_fund;ths_perf_comparative_benchmark_fund;ths_fund_listed_exchange_fund;ths_fund_td_currency_fund;ths_coupon_value_fund;ths_fund_manager_current_fund;ths_fund_manager_his_fund;ths_fund_supervisor_fund;ths_fund_mandator_fund;ths_fund_sponsor_related_org_fund;ths_fund_type_fund;ths_fund_invest_type_fund;ths_invest_type_first_classi_fund;ths_invest_type_second_classi_fund;ths_galaxy_classi_fund;ths_hts_classi_fund;ths_invest_style_fund;ths_fund_duration_fund;ths_fund_establishment_date_fund;ths_fund_expiry_date_fund;ths_redemp_sd_fund;ths_mandate_sd_fund;ths_mandate_ed_fund;ths_manage_fee_rate_fund;ths_mandate_fee_rate_fund;ths_sales_service_fee_fund;ths_high_pur_fee_rate_fund;ths_high_redemp_fee_rate_fund;ths_lof_listed_date_fund;ths_lof_listed_td_share_fund;ths_pm_fund_code_fund;ths_par_short_name_fund;ths_online_cash_sell_code_fund;ths_online_cash_pur_sd_fund;ths_online_cash_pur_ed_fund;ths_online_cash_buy_share_ul_fund;ths_online_cash_buy_share_dl_fund;ths_offline_cash_pur_sd_fund;ths_offline_cash_pur_ed_fund;ths_offline_cash_pur_share_dl_fund;ths_offline_stock_pur_sd_fund;ths_offline_stock_pur_ed_fund;ths_offline_stock_pur_vol_dl_fund;ths_fund_shares_convert_date_fund;ths_fund_shares_convert_ratio_fund;ths_issue_date_fund;ths_issue_object_fund;ths_issue_method_fund;ths_fund_reg_and_registrant_fund;ths_fund_main_underwrite_fund;ths_fund_issue_coordinator_fund;ths_fund_sales_agent_fund;ths_fund_listing_recommended_fund' # jsonparam=';;;;;;;;;;;' indicator, param = unzip_join([(key, val) for key, val, _ in indicator_param_list], sep=';') data_df = invoker.THS_BasicData(ths_code, indicator, param, max_code_num=3000) if data_df is None or data_df.shape[0] == 0: logging.info("没有可用的 pub_fund info 可以更新") return # 删除历史数据,更新数据 # table_name_list = engine_md.table_names() # if table_name in table_name_list: # with with_db_session(engine_md) as session: # session.execute( # "DELETE FROM {table_name} WHERE ths_code IN (".format(table_name=table_name) + ','.join( # [':code%d' % n for n in range(len(pub_fund_code_set))] # ) + ")", # params={'code%d' % n: val for n, val in enumerate(pub_fund_code_set)}) # session.commit() dtype = {key: val for key, _, val in indicator_param_list} dtype['ths_code'] = String(20) # data_count = data_df.shape[0] # data_df.to_sql(table_name, engine_md, if_exists='append', index=False, dtype=dtype) data_count = bunch_insert_on_duplicate_update(data_df, table_name, engine_md, 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]) # 更新 code_mapping 表 update_from_info_table(table_name)
def import_stock_info(chain_param=None, ths_code=None, refresh=False): """ :param chain_param: 该参数仅用于 task.chain 串行操作时,上下传递参数使用 :param ths_code: :param refresh: :return: """ table_name = 'ifind_stock_info' has_table = engine_md.has_table(table_name) logging.info("更新 wind_stock_info 开始") if ths_code is None: # 获取全市场股票代码及名称 if refresh: date_fetch = datetime.strptime('1991-02-01', STR_FORMAT_DATE).date() else: date_fetch = date.today() date_end = date.today() stock_code_set = set() while date_fetch < date_end: stock_code_set_sub = get_stock_code_set(date_fetch) if stock_code_set_sub is not None: stock_code_set |= stock_code_set_sub date_fetch += timedelta(days=365) stock_code_set_sub = get_stock_code_set(date_end) if stock_code_set_sub is not None: stock_code_set |= stock_code_set_sub ths_code = ','.join(stock_code_set) indicator_param_list = [ ('ths_stock_short_name_stock', '', String(10)), ('ths_stock_code_stock', '', String(10)), ('ths_stock_varieties_stock', '', String(10)), ('ths_ipo_date_stock', '', Date), ('ths_listing_exchange_stock', '', String(10)), ('ths_delist_date_stock', '', Date), ('ths_corp_cn_name_stock', '', String(40)), ('ths_corp_name_en_stock', '', String(100)), ('ths_established_date_stock', '', Date), ] # jsonIndicator='ths_stock_short_name_stock;ths_stock_code_stock;ths_thscode_stock;ths_stock_varieties_stock;ths_ipo_date_stock;ths_listing_exchange_stock;ths_delist_date_stock;ths_corp_cn_name_stock;ths_corp_name_en_stock;ths_established_date_stock' # jsonparam=';;;;;;;;;' indicator, param = unzip_join([(key, val) for key, val, _ in indicator_param_list], sep=';') data_df = invoker.THS_BasicData(ths_code, indicator, param) if data_df is None or data_df.shape[0] == 0: logging.info("没有可用的 stock info 可以更新") return # 删除历史数据,更新数据 # with with_db_session(engine_md) as session: # session.execute( # "DELETE FROM {table_name} WHERE ths_code IN (".format(table_name=table_name) + ','.join( # [':code%d' % n for n in range(len(stock_code_set))] # ) + ")", # params={'code%d' % n: val for n, val in enumerate(stock_code_set)}) # session.commit() dtype = {key: val for key, _, val in indicator_param_list} dtype['ths_code'] = String(20) # data_count = data_df.shape[0] # data_df.to_sql(table_name, engine_md, if_exists='append', index=False, dtype=dtype) data_count = bunch_insert_on_duplicate_update(data_df, table_name, engine_md, 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]) # 更新 code_mapping 表 update_from_info_table(table_name)