def import_wind_index_daily():
"""导入指数数据"""
engine = get_db_engine()
yesterday = date.today() - timedelta(days=1)
query = pd.read_sql_query(
'select wind_code,index_name, max(trade_date) as latest_date from wind_index_daily group by wind_code',
engine)
query.set_index('wind_code', inplace=True)
with get_db_session(engine) as session:
# 获取市场有效交易日数据
sql_str = "select trade_date from wind_trade_date where trade_date > '2005-1-1'"
table = session.execute(sql_str)
trade_date_sorted_list = [t[0] for t in table.fetchall()]
trade_date_sorted_list.sort()
date_to = get_last(trade_date_sorted_list, lambda x: x <= yesterday)
logger.info('%d indexes will been import', query.shape[0])
for code in query.index:
date_from = (query.loc[code, 'latest_date'] + timedelta(days=1))
date_from = get_first(trade_date_sorted_list, lambda x: x >= date_from)
if date_from is None or date_to is None or date_from > date_to:
continue
index_name = query.loc[code, 'index_name']
temp = rest.wsd(code, "open,high,low,close,volume,amt,turn,free_turn",
date_from, date_to)
temp.reset_index(inplace=True)
temp.rename(columns={'index': 'trade_date'}, inplace=True)
temp.trade_date = pd.to_datetime(temp.trade_date)
temp.trade_date = temp.trade_date.map(lambda x: x.date())
temp['wind_code'] = code
temp['index_name'] = index_name
temp.set_index(['wind_code', 'trade_date'], inplace=True)
temp.to_sql('wind_index_daily',
engine,
if_exists='append',
index_label=['wind_code', 'trade_date'],
dtype={
'wind_code': String(20),
'trade_date': Date,
})
logger.info('Success update %s - %s' % (code, index_name))
def import_stock_daily():
w = WindRest(WIND_REST_URL)
engine = get_db_engine()
with get_db_session(engine) as session:
# 获取每只股票最新交易日数据
sql_str = 'select wind_code, max(Trade_date) from wind_stock_daily group by wind_code'
table = session.execute(sql_str)
stock_trade_date_latest_dic = dict(table.fetchall())
# 获取市场有效交易日数据
sql_str = "select trade_date from wind_trade_date where trade_date > '2005-1-1'"
table = session.execute(sql_str)
trade_date_sorted_list = [t[0] for t in table.fetchall()]
trade_date_sorted_list.sort()
# 获取每只股票上市日期、退市日期
table = session.execute('SELECT wind_code, ipo_date, delist_date FROM wind_stock_info')
stock_date_dic = {wind_code: (ipo_date, delist_date if delist_date is None or delist_date > UN_AVAILABLE_DATE else None) for
wind_code, ipo_date, delist_date in table.fetchall()}
today_t_1 = date.today() - ONE_DAY
data_df_list = []
try:
for wind_code, date_pair in stock_date_dic.items():
date_ipo, date_delist = date_pair
# 获取 date_from
if wind_code in stock_trade_date_latest_dic:
date_latest_t1 = stock_trade_date_latest_dic[wind_code] + ONE_DAY
date_from = max([date_latest_t1, DATE_BASE, date_ipo])
else:
date_from = max([DATE_BASE, date_ipo])
date_from = get_first(trade_date_sorted_list, lambda x: x >= date_from)
# 获取 date_to
if date_delist is None:
date_to = today_t_1
else:
date_to = min([date_delist, today_t_1])
date_to = get_last(trade_date_sorted_list, lambda x: x <= date_to)
if date_from is None or date_to is None or date_from > date_to:
continue
# 获取股票量价等行情数据
wind_indictor_str = "open,high,low,close,adjfactor,volume,amt,pct_chg,maxupordown," + \
"swing,turn,free_turn,trade_status,susp_days"
data_df = w.wsd(wind_code, wind_indictor_str, date_from, date_to)
if data_df is None:
logging.warning('%s has no data during %s %s', wind_code, date_from, date_to)
continue
logging.info('%d data of %s', data_df.shape[0], wind_code)
data_df['wind_code'] = wind_code
data_df_list.append(data_df)
finally:
# 导入数据库
if len(data_df_list) > 0:
data_df_all = pd.concat(data_df_list)
data_df_all.index.rename('trade_date', inplace=True)
data_df_all.reset_index(inplace=True)
data_df_all.set_index(['wind_code', 'trade_date'], inplace=True)
data_df_all.to_sql('wind_stock_daily', engine, if_exists='append',
dtype={
'wind_code': String(20),
'trade_date': Date,
'open': Float,
'high': Float,
'low': Float,
'close': Float,
'adjfactor': Float,
'volume': Float,
'amt': Float,
'pct_chg': Float,
'maxupordown': Integer,
'swing': Float,
'turn': Float,
'free_turn': Float,
'trade_status': String(20),
'susp_days': Integer,
}
)
logging.info('%d data imported', data_df_all.shape[0])
def update_fundnav_by_file(wind_code,
file_path,
mode='delete_insert',
skip_rows=0,
sheet_name=0):
"""
支持 csv xls xlsx 文件格式导入 fund_nav表
:param wind_code:
:param file_path:
:param mode: 'replace_insert' 更新或插入；
'delete_insert' 删除时期段内的数据并插入；
'remove_insert' 移除全部历史数据并插入；
:return:
"""
_, file_extension = os.path.splitext(file_path)
if file_extension == '.csv':
fund_nav_df = pd.read_csv(file_path)
elif file_extension in ('.xls', '.xlsx'):
fund_nav_df = pd.read_excel(file_path,
skiprows=skip_rows,
sheetname=sheet_name)
else:
raise ValueError('不支持 %s 净值文件类型' % file_extension)
col_name_list = list(fund_nav_df.columns)
if len(col_name_list) == 2:
fund_nav_df['nav_acc'] = fund_nav_df[col_name_list[1]]
col_name_list = list(fund_nav_df.columns)
fund_nav_df.rename(columns={
col_name_list[0]: 'nav_date',
col_name_list[1]: 'nav',
col_name_list[2]: 'nav_acc'
},
inplace=True)
fund_nav_df = fund_nav_df_fillna(fund_nav_df)
fund_nav_df['wind_code'] = wind_code
fund_nav_df['source_mark'] = 2
# lambda x: datetime.strptime(x, STR_FORMAT_DATE)
data_str = get_first(fund_nav_df['nav_date'], lambda x: type(x) == str)
if data_str is not None:
date_str_format = pattern_data_format(data_str)
fund_nav_df['nav_date'] = fund_nav_df['nav_date'].apply(
lambda x: str_2_date(x, date_str_format=date_str_format))
else:
fund_nav_df['nav_date'] = fund_nav_df['nav_date'].apply(try_2_date)
# 更新数据库
engine = get_db_engine()
if mode == 'delete_insert':
nav_date_s = fund_nav_df['nav_date']
if nav_date_s.shape[0] > 0:
date_min, date_max = try_2_date(nav_date_s.min()), try_2_date(
nav_date_s.max())
sql_str = 'delete from fund_nav where wind_code = :wind_code and nav_date between :date_frm and :date_to'
with get_db_session(engine) as session:
session.execute(sql_str, [{
'wind_code': wind_code,
'date_frm': date_min,
'date_to': date_max
}])
table_name = 'fund_nav'
fund_nav_df.set_index(['wind_code', 'nav_date'], inplace=True)
fund_nav_df.to_sql(table_name,
engine,
if_exists='append',
dtype={
'wind_code': String(20),
'nav_date': Date,
'nav': FLOAT,
'nav_acc': FLOAT,
'source_mark': Integer
})
elif mode == 'remove_insert':
sql_str = 'delete from fund_nav where wind_code = :wind_code'
with get_db_session(engine) as session:
session.execute(sql_str, [{'wind_code': wind_code}])
table_name = 'fund_nav'
fund_nav_df.set_index(['wind_code', 'nav_date'], inplace=True)
fund_nav_df.to_sql(table_name,
engine,
if_exists='append',
dtype={
'wind_code': String(20),
'nav_date': Date,
'nav': FLOAT,
'nav_acc': FLOAT,
'source_mark': Integer
})
elif mode == 'replace_insert':
data_list = list(fund_nav_df.T.to_dict().values())
sql_str = "REPLACE INTO fund_nav (wind_code, nav_date, nav, nav_acc, source_mark) values (:wind_code, :nav_date, :nav, :nav_acc, :source_mark)"
with get_db_session() as session:
session.execute(sql_str, data_list)
else:
raise ValueError('mode="%s" is not available' % mode)
sql_str = "call proc_update_fund_info_by_wind_code2(:wind_code, :force_update)"
with get_db_session(engine) as session:
session.execute(sql_str, {
'wind_code': wind_code,
'force_update': True
})
logger.info('import fund_nav %d data on %s' %
(fund_nav_df.shape[0], wind_code))