def pro_get_k_data_daily_cached(self, code, index, start, end):
start = format_date_ts_pro(start)
end = format_date_ts_pro(end)
if index == True:
table = self.tables['index_trade_daily']
else:
table = self.tables['stock_trade_daily']
query_min_max = "select min(trade_date),max(trade_date) from {} where ts_code='{}'".format(
table, code)
DBSession = sessionmaker(self.conn)
session = DBSession()
try:
res = list(session.execute(query_min_max))
except:
res = [(None, None)]
#print(res)
session.close()
if res[0] == (None, None):
return None, None, None
cached_start, cached_end = res[0]
if end is None:
df = pd.read_sql_query(
"select * from {} where trade_date>='{}' and ts_code='{}';".
format(table, start, code), self.conn)
return df, cached_start, cached_end
elif end <= cached_end:
df = pd.read_sql_query(
"select * from {} where trade_date>='{}' and trade_date<='{}' and ts_code='{}';"
.format(table, start, end, code), self.conn)
return df, cached_start, cached_end
else:
return None, cached_start, cached_end
def pro_get_k_data_daily(self, code, index, start, end):
df, cached_start, cached_end = self.get_k_data_daily_cached(
code, index, start, end)
if df is None:
begin_date = START_DATE if cached_end is None else cached_end
#print('load from internet')
#print(cached_end)
#print(begin_date)
if index:
df = self.pro.index_daily(
ts_code=code, start_date=format_date_ts_pro(begin_date))
else:
df = ts.pro_bar(pro_api=self.pro,
ts_code=code,
adj='qfq',
start_date=format_date_ts_pro(begin_date))
if df is None:
return None
if df is not None:
self.pro_cache_data_daily(df, index, cached_end)
if df.shape[0] == 0:
return df
else:
df = df[(df.trade_date >= format_date_ts_pro(start))
& (df.trade_date <= format_date_ts_pro(end))]
return pro_opt_stock_k(df)
def pro_stock_basic_on_the_date(self, date):
table = self.tables['stock_basic_daily']
query = "select * from {} where trade_date='{}'".format(
table, format_date_ts_pro(date))
df = pd.read_sql_query(query, self.conn)
if df is None or df.shape[0] == 0:
df = self.pro.daily_basic(trade_date=format_date_ts_pro(date))
print('save stock basic on the date:{}'.format(date))
df.to_sql(self.tables['stock_basic_daily'],
con=self.conn,
if_exists='append',
index=False)
#gl_float = df.select_dtypes(include=['float'])
#df = gl_float.apply(pd.to_numeric,downcast='float')
return pro_opt_stock_basic(df)
def get_market_data_cached(self, date):
table = self.tables['stock_trade_daily']
date = format_date_ts_pro(date)
query = "select * from {} where trade_date='{}'".format(table, date)
k_df = pd.read_sql_query(query, self.conn)
table = self.tables['stock_basic_daily']
query_basic = "select * from {} where trade_date='{}';".format(
table, date)
basic_df = pd.read_sql_query(query_basic, self.conn)
return k_df.merge(basic_df, on=['ts_code'], how='inner')
def use_analyzer_on_stock_of_the_date(code,analyzer,date):
print(date)
start = utils.date_delta(date,-200)
df = prepare_stock_data(code,analyzer.get('data_config_file'),analyzer.get('feature_config_file'),start,date)
print(df)
df = df[(df.date==date)|(df.date==utils.format_date_ts_pro(date))]
print(len(list(df.columns)))
analyzer_config = load_analyzer_config(analyzer.get('analyzer_config_file'))
X = get_X(df,analyzer_config)
mod_features = analyzer.get('model').feature_names
print('parse columns:{},model columns:{}'.format(len(list(X.columns)),len(mod_features)))
print(X.shape)
y = analyzer.get('model').predict(X)
return (df,y)
def update_cache_stock_basic(self):
table = self.tables['stock_basic_daily']
dates = list(
self.pro.index_daily(
ts_code='000001.SH',
start_date=format_date_ts_pro(START_DATE)).trade_date)
query_dates = 'SELECT trade_date FROM {} group by trade_date'.format(
table)
DBSession = sessionmaker(self.conn)
session = DBSession()
cached_dates = list(map(lambda x: x[0], session.execute(query_dates)))
#print(res)
session.close()
uncached = list(set(dates).difference(set(cached_dates)))
print('stock of the dates need to be cached {}'.format(uncached))
for date in uncached:
print(date)
self.get_basic_on_the_date(date)
def get_trade_dates(self, start, end):
dates = list(
self.pro.index_daily(ts_code='000001.SH',
start_date=format_date_ts_pro(start),
end_date=format_date_ts_pro(end)).trade_date)
return dates
def get_market_data(self, date):
df = self.get_market_data_cached(date)
if df is None or df.shape[0] == 0:
df = self.pro.daily(trade_date=format_date_ts_pro(date))
return df