def update_schema(db_name, sub_name): """ 更新schema相关的begin date,end date, last update 适用于非factor_return相关的数据库 @db_name (str): db的名称 eg. FACTOR 排除factor_return @sub_name (str): db中各子数据库的名称 eg. VALUE GROWTH """ schema = json2dict(os.path.join(DB_PATH_LIB[db_name], 'schema')) assert sub_name date_list = get_date_lists_in_table(DB_PATH_LIB[db_name], sub_name) schema[sub_name]['begin date'] = date_list[0] schema[sub_name]['end date'] = date_list[-1] schema[sub_name]['last update'] = datetime.now().strftime( '%Y-%m-%d %H:%M:%S') Logger.info("schema updated: {}".format(sub_name)) dict2json(schema, os.path.join(DB_PATH_LIB[db_name], 'schema'), log=False) a = pd.DataFrame(schema).T col_names = [ 'aspect', 'type', 'begin date', 'end date', 'last update', 'col_names', 'field', 'kwargs', 'explanation' ] b = a.reindex(columns=col_names).reset_index().rename(columns={ 'index': 'indicator' }).sort_values(['type', 'aspect', 'field']) b.to_csv(os.path.join(DB_PATH_LIB[db_name], 'schema.csv'), index=False)
def update_industry_to_json(industry, trading_days): try: date = trading_days[-1] index_code, loader = INDEX_LOADER_MAP[industry] info = loader(index_code, date, level=1) except Exception: Logger.error("Error occurred when loading {} on {}".format(industry, date)) raise ValueError try: path = os.path.join(DB_INDUSTRY, '{}.json'.format(industry)) copy_to = os.path.join(DB_INDUSTRY, '{}_backup.json'.format(industry)) shutil.copy(path, copy_to) # 保存副本,以防数据损坏 dict2json(info, path, log=False) Logger.info("{} on {} is updated successfully".format(industry, date)) except Exception: Logger.error("Error occurred when writing {} on {}".format(industry, date)) raise ValueError # json files are different from sql, cannot use update_schema() # therefore update schema information explicitly try: now = datetime.now() schema = get_schema('industry') schema[industry]["begin date"] = "" schema[industry]["end date"] = now.strftime('%Y-%m-%d') schema[industry]['last update'] = now.strftime('%Y-%m-%d %H:%M:%S') save_schema(schema, 'industry') Logger.info("schema updated: {}".format(industry)) except Exception: Logger.error("Error occurred when updating schema of {}".format(industry)) traceback.print_exc() raise ValueError
def get_index_contents(index_code, date="", approx=False, log=False): """ 读取单个日期指数成分股列表 @index_code (str): 指数代码,目前支持 ['A', 'H', '000905.SH', '000300.SH', '000016.SH', 'HSI.HI'] @date ('%Y-%m-%d'): 单个日期 @log (Bool): 是否打印log :return (list): 股票代码列表 """ if log: Logger.info( "Reading index contents of {} on {}".format(index_code, date), "green") if not date: Logger.error("Empty date") raise ValueError # approx 用于保证更新 indicator 财报数据时财报日非交易日的情况 if approx: date = get_nearest_trading_day(date=date, direction='left', self_included=True) if index_code in IDXCONT_AS_SQL: output = get_index_contents_from_sql(index_code, date, log=log) elif index_code in IDXCONT_AS_CSV: output = get_index_contents_from_csv(index_code) else: Logger.error("Unrecognized index code: {}".format(index_code)) raise ValueError return output
def get_secs_factor_on_multidays(factor, sec_ids=[], trading_days=[], log=False): """ 从本地数据库中获取一段日期的单个factor的值,并返回 dict of DataFrame @factor (str): 单个factor @sec_ids (list): 支持多个股票查询,默认为[],表示查询范围是全A股 @trading_days (["%Y-%m-%d"]): 日期列表 @log (Bool): 是否打印log :return: {date: Dataframe},其中 DataFrame 列为factor名,index为sec_id """ if log: Logger.info( "Reading {} from {} to {}".format(factor, trading_days[0], trading_days[-1]), "green") if factor not in get_schema("factor"): Logger.error("Unrecognized factor: {}".format(factor)) raise ValueError if not isinstance(sec_ids, list): Logger.error("sec_ids must be list!") raise ValueError if not trading_days: Logger.error("Empty date") raise ValueError # 长连接效率更高,所以这里不是复用 get_secs_factor 而是重新写 with SqliteProxy(log=log) as proxy: output = {} for year, date_list in classify_dates_by_year(trading_days).items(): path = os.path.join(DB_FACTOR, '{}.db'.format(year)) proxy.connect(path) for date in date_list: if len(sec_ids) == 0: # 为空默认全A股 conds = "" elif len(sec_ids) == 1: conds = "AND sec_id = '{}'".format(sec_ids[0]) else: conds = "AND sec_id IN {}".format(tuple(sec_ids)) query = "SELECT sec_id, {} FROM [{}] WHERE date = '{}' {}".format( factor, factor, date, conds) try: df = proxy.query_as_dataframe(query) except Exception: Logger.error("Error occurred when reading {} at {}".format( factor, date)) traceback.print_exc() raise ValueError output[date] = df return output
def update_index_contents_to_sql(index_code, trading_days, override, log=False): with SqliteProxy(log=log) as proxy: date_classfier = classify_dates_by_year(trading_days) for year, date_list in date_classfier.items(): path = os.path.join(DB_INDEX_CONTENTS, '{}.db'.format(year)) proxy.connect(path) if index_code not in proxy.list_tables: create_table(proxy, "index_contents", index_code) # 判断已有数据 query = "SELECT DISTINCT(date) FROM [{}]".format(index_code) lookup = proxy.query_as_dataframe(query) lookup = set(lookup['date'].tolist()) for date in date_list: if date in lookup and not override: # 更新的日期已经存在于数据库时,不覆盖则跳过 if log: Logger.warn("{} records on {} is existed.".format( index_code, date)) continue try: loader = LOADER_MAP[index_code] df = loader(index_code, date) df['date'] = date except Exception: Logger.error("Error occurred when loading {} on {}".format( index_code, date)) raise ValueError if df is not None: # 从Wind下载数据成功时 try: if date in lookup and override: # 覆盖时删除原记录 proxy.execute( "DELETE FROM [{}] WHERE date = '{}'".format( index_code, date)) proxy.write_from_dataframe(df, index_code) except Exception: Logger.error( "Error occurred when writing {} on {}".format( index_code, date)) traceback.print_exc() raise ValueError Logger.info("{} on {} is updated successfully".format( index_code, date)) else: # 从wind提取数据失败时 Logger.error("Fail to fetch {} data on {}".format( index_code, date)) raise ValueError update_schema('index_contents', index_code)
def get_index_contents_on_multidays(index_code, trading_days=[], log=False): """ 读取多个日期某指数全部股票列表 @index_code (str): 指数代码,目前支持 ['A', '000905.SH', '000300.SH', '000016.SH'] @trading_days (['%Y-%m-%d']): 日期列表 @log (Bool): 是否打印log :return: ({date: list}), key为date value为 股票代码列表 """ if log: Logger.info( "Reading all {} records between trading_days ...".format( index_code), "green") if len(trading_days) == 0: Logger.error("Empty date") raise ValueError elif len(trading_days) == 1: date = trading_days[0] return {date: get_index_contents(index_code, date, log=False)} output = {} if index_code in IDXCONT_AS_SQL: with SqliteProxy(log=log) as proxy: for year, date_list in classify_dates_by_year( trading_days).items(): path = os.path.join(DB_INDEX_CONTENTS, '{}.db'.format(year)) proxy.connect(path) query = "SELECT date, sec_id FROM [{}] WHERE date IN {}".format( index_code, tuple(date_list)) try: df = proxy.query_as_dataframe(query) except Exception: Logger.error( "Empty result when reading {} from {} to {}".format( index_code, trading_days[0], trading_days[-1])) traceback.print_exc() raise ValueError if len(df) == 0: Logger.warn( "Empty result when reading {} from {} to {}".format( index_code, trading_days[0], trading_days[-1])) for date in date_list: output[date] = df[df.date == date]['sec_id'].tolist() elif index_code in IDXCONT_AS_CSV: info = get_index_contents_from_csv(index_code) output = {date: info for date in trading_days} else: Logger.error("Unrecognized index code: {}".format(index_code)) raise ValueError return output
def get_secs_index_std(index_std, sec_ids=[], trading_days=[], log=False): """ 从本地数据库中获取一段日期的单个index_std的值,并返回 DataFrame @index_std (str): 单个index_std @sec_ids (list): 支持多个股票查询,默认为[],表示查询范围是全A股 @trading_days (["%Y-%m-%d"]): 日期列表 @log (Bool): 是否打印log :return: {date: Dataframe},其中 DataFrame 列为index_std名,index_std为sec_id """ if log: Logger.info("Reading {} from {} to {}".format(index_std, trading_days[0], trading_days[-1]), "green") # if index_std not in get_schema("index_std"): # Logger.error("Unrecognized index_std: {}".format(index_std)) # raise ValueError if not isinstance(sec_ids, list): Logger.error("sec_ids must be list!") raise ValueError if not trading_days: Logger.error("Empty date") raise ValueError with MySQLProxy(log=log) as proxy: output={} proxy.connect(USER, PASSWORD, "index_std") # 注: 单个值用=,需要加上引号,多个值用tuple if len(sec_ids) == 0: if len(trading_days) == 1: query="SELECT * FROM {} WHERE date = '{}' ".format(index_std, trading_days[0]) else: query="SELECT * FROM {} WHERE date in {}".format(index_std, tuple(trading_days)) elif len(sec_ids) == 1: if len(trading_days) == 1: query="SELECT * FROM {} WHERE date = '{}' AND sec_id = '{}' ".format(index_std, trading_days[0], sec_ids[0]) else: query="SELECT * FROM {} WHERE date in {} AND sec_id = '{}' ".format(index_std, tuple(trading_days), sec_ids[0]) else: if len(trading_days) == 1: query="SELECT * FROM {} WHERE date = '{}' AND sec_id in {}".format(index_std, trading_days[0], tuple(sec_ids)) else: query="SELECT * FROM {} WHERE date in {} AND sec_id in {}".format(index_std, tuple(trading_days), tuple(sec_ids)) try: df=proxy.query_as_dataframe(query) except Exception: Logger.error("Error occurred when reading {} ".format(inde)) traceback.print_exc() raise ValueError df['date']=df['date'].apply(lambda x: str(x)) return df
def get_secs_factor(factor, sec_ids=[], date="", log=False): """ 从本地数据库中获取单个日期的单个factor的值,并返回 DataFrame @factor (str): 单个factor @sec_ids (list): 支持多个股票查询,默认为[],表示查询范围是全A股 @date ('%Y-%m-%d'): 单个日期 @log (Bool): 是否打印log :return: Dataframe 列为factor名,index为sec_id """ if log: Logger.info("Reading {} at {}".format(factor, date), "green") if factor not in get_schema("factor"): Logger.error("Unrecognized factor: {}".format(factor)) raise ValueError if not isinstance(sec_ids, list): Logger.error("sec_ids must be list!") raise ValueError if not date: Logger.error("Empty date") raise ValueError with SqliteProxy(log=log) as proxy: path = os.path.join(DB_FACTOR, '{}.db'.format(date[:4])) proxy.connect(path) if len(sec_ids) == 0: # 为空默认全A股 conds = "" elif len(sec_ids) == 1: conds = "AND sec_id = '{}'".format(sec_ids[0]) else: conds = "AND sec_id IN {}".format(tuple(sec_ids)) query = "SELECT sec_id, {} FROM [{}] WHERE date = '{}' {}".format( factor, factor, date, conds) try: df = proxy.query_as_dataframe(query) except Exception: Logger.error("Error occurred when reading {} at {}".format( factor, date)) traceback.print_exc() raise ValueError return df.sort_values(by=['sec_id']).set_index(['sec_id'])
def update_index_std(index, cp=3, log=False): """ 更新index_std 更新原理: 无需指定trading_days 更新全部index中有的日期但在index_std中没有的日期 @index <str>: index名称 不是index_std名称 @cp <int>: winsorize的临界值 """ trading_days = get_unique_datelist_from_table("index", index) existed_days = get_unique_datelist_from_table("index_std", "{}_std".format(index)) update_days = sorted(list(set(trading_days) - set(existed_days))) if len(update_days) == 0: Logger.warn("All given dates has existed. No need to update!!") return output = process_ts_index(index, update_days, cp) if len(output) == 0: Logger.error("Fail to process {} on given dates".format(index)) df2mysql(USER, PASSWORD, "index_std", index + '_std', output) del output, trading_days, update_days gc.collect() Logger.info("Updated successfully!!")
def update_industry(industry, trading_days=[], override=False, log=False): """ 从Wind更新某指数成分股申万一级行业数据 @industry (str): 行业数据库名称 @trading_days (['%Y-%m-%d']): 日期列表 @override (Bool): 是否覆盖原记录 默认为False 表示不覆盖 @log (Bool): 是否打印日志信息 """ Logger.info("Updating industry {}".format(industry), "green") if industry not in get_schema('industry'): Logger.error("Unrecognized industry: {}".format(industry)) return if not trading_days: Logger.error("Empty date") raise ValueError if industry in INDUSTRY_AS_SQL: update_industry_to_sql(industry, trading_days, override, log) elif industry in INDUSTRY_AS_JSON: # 非sql数据强制更新,原有的会自动保存副本 update_industry_to_json(industry, trading_days) else: Logger.error("Unrecognized industry: {}".format(industry)) raise ValueError if log: Logger.info("industry/{} is updated.".format(industry), color="green") Logger.info("------------------------------------------")
def update_calendar(start_date, end_date, log=False): """ 从Wind更新calendar相关数据 每次更新将删除原有所有数据 更新到当前区间 @start_date ("%Y-%m-%d"): 开始日日期 必须是月初日期 @end_date ("%Y-%m-%d"): 结束日日期 必须是月月末日期 @log (Bool): 是否打印log """ Logger.info("Updating calendar ...", "green") max_existed_date = get_trading_days with SqliteProxy(log=log) as proxy: proxy.connect(os.path.join(DB_CALENDAR_PATH, "calendar.db")) proxy.execute("DELETE FROM calendar") try: df = load_calendar_from_wind(start_date, end_date) except Exception: Logger.error("Error occurred when loading") raise ValueError try: proxy.write_from_dataframe(df, "calendar") except Exception: Logger.error( "Error occurred when writing dataframe into sqlite db") traceback.print_exc() raise ValueError if log: Logger.info("calendar was updated from {} to {}".format( start_date, end_date), color="green") Logger.info("------------------------------------------")
def update_index_contents_to_csv(index_code, trading_days, override): try: date = trading_days[-1] df = loader(index_code, date) loader = LOADER_MAP[index_code] except Exception: Logger.error("Error occurred when loading {}".format(index_code)) raise ValueError try: path = os.path.join(DB_INDEX_CONTENTS, '{}.csv'.format(index_code)) copy_to = os.path.join(DB_INDEX_CONTENTS, '{}_backup.csv'.format(index_code)) shutil.copy(path, copy_to) # 保存副本,以防数据损坏 df.to_csv(path, encoding="utf-8", index=False) Logger.info("{} on {} is updated successfully".format( index_code, date)) except Exception: Logger.error("Error occurred when writing {}".format(index_code)) traceback.print_exc() raise ValueError # csv files are different from sql, cannot use update_schema() # therefore update schema information explicitly try: now = datetime.now() schema = get_schema('index_contents') schema[index_code]["begin date"] = "" schema[index_code]["end date"] = now.strftime('%Y-%m-%d') schema[index_code]['last update'] = now.strftime('%Y-%m-%d %H:%M:%S') save_schema(schema, 'index_contents') Logger.info("schema updated: {}".format(index_code)) except Exception: Logger.error( "Error occurred when updating schema of {}".format(index_code)) traceback.print_exc() raise ValueError
def get_secs_IC(ic_code, trading_days=[], log=False): """ 从本地数据库中获取一段日期的单个IC的值,并返回 dict of DataFrame @ic_code (str): 单个IC stocks_info: 全A股 @trading_days (["%Y-%m-%d"]): 日期列表 @log (Bool): 是否打印log :return: {date: Dataframe},date, sec_id, sec_name, is_st, is_trade, ... """ if log: Logger.info( "Reading {} from {} to {}".format(ic_code, trading_days[0], trading_days[-1]), "green") if not trading_days: Logger.error("Empty date") raise ValueError with MySQLProxy(log=log) as proxy: output = {} proxy.connect(USER, PASSWORD, 'index') # 注: 单个值用=,需要加上引号,多个值用tuple if len(trading_days) == 1: query = "SELECT * FROM {} WHERE date = '{}' ".format( ic_code, trading_days[0]) else: query = "SELECT * FROM {} WHERE date in {}".format( ic_code, tuple(trading_days)) try: df = proxy.query_as_dataframe(query) except Exception: Logger.error("Error occurred when reading {} at {}".format( ic_code, date)) traceback.print_exc() raise ValueError return df
def update_factors(factors=[], trading_days=[], override=False, log=False): """ 更新多个factor的指定日期列表的数据 @factors (<list>):factor名称构成的列表 @trading_days ([%Y-%m-%d]): 日期列表 @override (<Bool>): 是否覆盖原记录 默认为False 表示不覆盖 @log (<Bool>): 是否打印log """ SCHEMA = get_schema("factor") if not factors: factors = sorted(SCHEMA, key=lambda x: SCHEMA[x]["level"]) for fac in factors: if fac in SCHEMA: update_single_factor(factor=fac, trading_days=trading_days, override=override, log=log) else: Logger.error("Unrecognized factor: {}".format(fac)) Logger.info("------------------------------------------")
def update_factor_return_schema(factor): """ 更新factor_return的schema相关的begin date,end date, last update @factor (str): factor的名称 """ schema = json2dict(os.path.join(DB_PATH_LIB['factor_return'], 'schema')) filepath = os.path.join(DB_PATH_LIB['factor_return'], "{}.csv".format(factor)) df = pd.read_csv(filepath, encoding="utf-8")["date"] schema[factor]['begin date'] = df.min() schema[factor]['end date'] = df.max() schema[factor]['last update'] = \ datetime.now().strftime('%Y-%m-%d %H:%M:%S') Logger.info("schema updated: {}".format(factor)) dict2json(schema, os.path.join(DB_PATH_LIB['factor_return'], 'schema'), log=False)
def sqlize_db(db_name, subdb_list=[]): """将数据库sql化""" if not subdb_list: subdb_list = list(get_schema(db_name).keys()) else: subdb_list = [s for s in subdb_list if s in get_schema(db_name)] db_path = os.path.join(DB_PATH, db_name) with SqliteProxy(log=False) as proxy: for subdb in subdb_list: Logger.info("SQLing {}/{}".format(db_name, subdb), "green") subdb_path = os.path.join(db_path, subdb) trading_days = listdir_advanced(subdb_path, 'csv', strip_suffix=True) for year, dates in classify_dates_by_year(trading_days).items(): path = os.path.join(db_path, '{}.db'.format(year)) proxy.connect(path) if subdb not in proxy.list_tables: creator = DB_CREATOR_MAP[db_name] creator(proxy, subdb) for date in dates: df = pd.read_csv( os.path.join(subdb_path, '{}.csv'.format(date))) df['date'] = date try: proxy.write_from_dataframe(df, subdb) except Exception: Logger.error( "Error occurred when sqlizing {} on {}.".format( subdb, date)) traceback.print_exc()
def update_index_contents(index_code, trading_days=[], override=False, log=False): """ 从Wind更新index_contents相关数据 @index_code (str): 要更新的指标 @trading_days (['%Y-%m-%d']): 传入的日期列表 @override (Bool): 是否覆盖旧数据,默认为False,表示不覆盖 @log (Bool): 是否打印log """ Logger.info("Updating index_contents {}".format(index_code), "green") if index_code not in get_schema('index_contents'): Logger.error("Unrecognized index: {}".format(index_code)) return if not trading_days: Logger.error("Empty date") raise ValueError if index_code in IDXCONT_AS_SQL: update_index_contents_to_sql(index_code, trading_days, override, log) elif index_code in IDXCONT_AS_CSV: # 非sql数据强制更新,原有的会自动保存副本 update_index_contents_to_csv(index_code, trading_days) else: Logger.error("Unrecognized index code: {}".format(index_code)) raise ValueError if log: Logger.info("index_content/{} is updated.".format(index_code), color="green") Logger.info("------------------------------------------")
def update_single_factor_return(factor_return, trading_days=[], group_num=10, log=True): """ 根据trading_days更新factor_return数据 @factor_return (<str>): factor的名称 @trading_days (<[%Y-%m-%d]>) : 日期列表 @group_num (<int>): 分组个数 """ if log: Logger.info("Updating factor_return {}...".format(factor_return)) if factor_return not in get_schema("factor_return"): Logger.error("Unrecognized factor_return: {}".format(factor_return)) return factor_path = DB_PATH_LIB['factor'] factor_exist_dates = get_date_lists_in_table(factor_path, factor_return) not_found_date = list(set(trading_days) - set(factor_exist_dates)) if len(not_found_date) != 0 and log: Logger.warn( "Fail to update these factor returns on following dates due to lack factor:{}" .format(not_found_date)) trading_days = list(set(trading_days) - set(not_found_date)) if len(trading_days) == 0: Logger.error("No valid date to update") return trading_days = sorted(trading_days) db_factor_return_path = os.path.join(DB_PATH, "factor_return") filepath = os.path.join(db_factor_return_path, '{}.csv'.format(factor_return)) df_new = load_single_factor_return_on_multidays(factor_return, trading_days, group_num) _n_updated_date = len(df_new) if not os.path.exists(filepath): # 没有已经更新过的记录 Logger.info("首次更新 {}数据".format(factor_return)) output = df_new.copy() output.to_csv(filepath, encoding="utf-8") else: df_old = pd.read_csv(filepath, encoding="utf-8") # 已经存在的所有return数据 min_exist_date = normalize(df_old['date'].min(), "%Y-%m-%d") max_exist_date = normalize(df_old['date'].max(), "%Y-%m-%d") max_update_date = trading_days[-1] min_update_date = trading_days[1] # 因为只能第二个日期才能计算收益 if (max(min_update_date, max_update_date) < min_exist_date) or \ (min(min_update_date, max_update_date) > max_exist_date): Logger.error("非法更新:待更新时间段孤立于现有的时间段") Logger.error("开始更新日期:{} 结束更新日期:{}".format(min_update_date, max_update_date)) Logger.error("原有开始日期:{} 原有结束日期:{}".format(min_exist_date, max_exist_date)) return if (min_update_date < min_exist_date) and \ (max_update_date <= max_exist_date) and \ (max_update_date >= min_exist_date): Logger.info("左更新:更新之前记录") if (min_update_date >= min_exist_date) and (max_update_date <= max_exist_date): Logger.info("存量更新:更新当前已经有的记录") if (min_update_date >= min_exist_date) and \ (min_update_date <= max_exist_date) and \ (max_update_date > max_exist_date): Logger.info("右更新:更新未来的记录") if (min_update_date < min_exist_date) and \ (max_update_date > max_exist_date): Logger.info("全更新:当前已经存在的日期是待更新日期的子集") df_old['date'] = df_old['date'].apply( lambda x: normalize(x, "%Y-%m-%d")) df_new['date'] = df_new['date'].apply( lambda x: normalize(x, "%Y-%m-%d")) bool_list = df_old['date'].isin(df_new['date']).apply( lambda x: not x) # 旧数据不在更新日期中为True # 取出那些不在本次更新范围内但原数据已经存在的日期列表 这些日期直接copy 无需计算 df_old = df_old[bool_list] output = df_old.append(df_new).sort_values(by=['date']) output = output.set_index(['date']) format_var_name_list = [ 'group{:0>2}'.format(i) for i in range(1, group_num + 1) ] format_var_name_list.append('{}'.format(factor_return)) output = output.reindex(columns=format_var_name_list) output.to_csv(filepath, encoding="utf-8") update_factor_return_schema(factor_return) if log: _n_all_date = len(output) _n_existed_date = _n_all_date - _n_updated_date Logger.info("传入日期数:{} 已经存在个数:{} 实际写入次数:{}".format( _n_all_date, _n_existed_date, _n_updated_date)) Logger.info("factor_return {} is updated.".format(factor_return), color="green") Logger.info("------------------------------------------")
def update_single_factor(factor, trading_days=[], override=False, log=False): """ 更新单个factor的指定日期列表的数据 @factor (str): factor名称 @trading_days ([%Y-%m-%d]): 日期列表 @override (Bool): 是否覆盖原记录,默认为False,表示不覆盖 @log (Bool): 是否打印log """ Logger.info("Updating factor {}".format(factor), "green") _n_updated_date = 0 if factor not in get_schema('factor'): Logger.error("Unrecognized factor: {}".format(factor)) raise ValueError if not trading_days: Logger.error("Empty date") raise ValueError with SqliteProxy(log=log) as proxy: date_classfier = classify_dates_by_year(trading_days) for year, date_list in date_classfier.items(): path = os.path.join(DB_FACTOR, '{}.db'.format(year)) proxy.connect(path) if factor not in proxy.list_tables: create_table(proxy, "factor", factor) # 判断已有数据 if len(date_list) == 1: query = "SELECT DISTINCT(date) FROM {} WHERE date = '{}'".format( factor, date_list[0]) else: query = "SELECT DISTINCT(date) FROM {} WHERE date in {}".format( factor, tuple(date_list)) lookup = proxy.query_as_dataframe(query) lookup = set(lookup['date'].tolist()) for date in date_list: if date in lookup and not override: # 更新的日期已经存在于数据库时,不覆盖则跳过 if log: Logger.warn("{} records on {} is existed.".format( factor, date)) continue try: df = load_single_factor_on_single_day(factor=factor, date=date) except Exception: Logger.error("Error occurred when loading {} on {}".format( factor, date)) traceback.print_exc() continue if df is not None: # 成功取得indicator if date in lookup and override: # 覆盖时删除原记录 proxy.execute( "DELETE FROM [{}] WHERE date = '{}'".format( factor, date)) df['date'] = date try: proxy.write_from_dataframe(df, factor) except Exception: Logger.error( "Error occurred when writing {} on {}".format( factor, date)) traceback.print_exc() raise ValueError if log: Logger.info("{} on {} is updated successfully".format( factor, date)) _n_updated_date += 1 else: # 从wind提取数据失败时 Logger.error("Fail to fetch {} data on {}".format( factor, date)) raise ValueError update_schema(db_name="factor", sub_name=factor) if log: _n_all_date = len(trading_days) _n_existed_date = _n_all_date - _n_updated_date Logger.info("传入日期数:{} 已经存在个数:{} 实际写入次数:{}".format( _n_all_date, _n_existed_date, _n_updated_date)) Logger.info("factor {} is updated.".format(factor), color="green") Logger.info("------------------------------------------")
def update_single_indicator(indicator, sec_ids=[], trading_days=[], override=False, log=False): """ 更新单个indicator的指定日期列表的数据 @indicator (str): 单个indicator的名称 @sec_ids<list> : 股票代码列表 @trading_days ([%Y-%m-%d]): 日期列表 @override (Bool): 是否覆盖原记录 默认为False 表示不覆盖 @log (Bool): 是否打印log """ if log: Logger.info("Updating indicator {}".format(indicator), "green") if indicator not in get_schema('indicator'): Logger.error("Unrecognized indicator: {}".format(indicator)) raise ValueError if not trading_days: Logger.error("Empty date") raise ValueError with SqliteProxy(log=log) as proxy: date_classfier = classify_dates_by_year(trading_days) for year, date_list in date_classfier.items(): path = os.path.join(DB_INDICATOR, '{}.db'.format(year)) proxy.connect(path) if indicator not in proxy.list_tables: create_table(proxy, "indicator", indicator) # 判断已有数据 if len(date_list) == 1: query = "SELECT DISTINCT(date) FROM {} WHERE date = '{}'".format( indicator, date_list[0]) else: query = "SELECT DISTINCT(date) FROM {} WHERE date in {}".format( indicator, tuple(date_list)) lookup = proxy.query_as_dataframe(query) lookup = set(lookup['date'].tolist()) for date in date_list: if date in lookup and not override: # 更新的日期已经存在于数据库时,不覆盖则跳过 if log: Logger.warn("{} records on {} is existed.".format( indicator, date)) continue try: df = load_single_indicator_on_single_day_from_wind( indicator=indicator, sec_ids=sec_ids, date=date) except Exception: Logger.error("Error occurred when loading {} on {}".format( indicator, date)) raise ValueError if df is not None: # 从Wind下载数据成功时 if date in lookup and override: # 覆盖时删除原记录 if len(sec_ids) == 0: proxy.execute( "DELETE FROM [{}] WHERE date = '{}'".format( indicator, date)) if len(sec_ids) == 1: proxy.execute( "DELETE FROM [{}] WHERE date = '{}' and sec_id = '{}'" .format(indicator, date, sec_ids[0])) else: proxy.execute( "DELETE FROM [{}] WHERE date = '{}' and sec_id in {}" .format(indicator, date, tuple(sec_ids))) df['date'] = date try: proxy.write_from_dataframe(df, indicator) except Exception: Logger.error( "Error occurred when writing {} on {}".format( indicator, date)) traceback.print_exc() raise ValueError if log: Logger.info("{} on {} is updated successfully".format( indicator, date)) else: # 从wind提取数据失败时 Logger.error("Fail to fetch {} data on {}".format( indicator, date)) raise ValueError update_schema(db_name="indicator", sub_name=indicator) if log: Logger.info("indicator {} is updated.".format(indicator), color="green") Logger.info("------------------------------------------")