def _init_ts(self):
y_start = 1990
__pool = ThreadPoolExecutor(max_workers=MULTIPLE,
thread_name_prefix="test_")
fs = []
i = 0
for y_i in range(31)[::-1]:
y = y_start + y_i
first, last = dao.get_trade_date(y, 0)
if not first:
continue
print("{}-{}".format(y, 0))
first = first.strftime('%Y%m%d')
last = last.strftime('%Y%m%d')
f1 = __pool.submit(get_pro().index_weight,
index_code=self.biz_code,
start_date=first,
end_date=first)
f2 = __pool.submit(get_pro().index_weight,
index_code=self.biz_code,
start_date=last,
end_date=last)
fs.append(f1)
fs.append(f2)
i += 2
if i > 197:
print('198次后休息60秒')
time.sleep(60)
i = 0
df = None
for f2 in fs:
temp_df = f2.result()
if len(temp_df):
if df is None:
df = temp_df
else:
df = df.append(temp_df, ignore_index=True)
df_add_y_m(df, 'trade_date')
dtype = {
'index_code': VARCHAR(length=10),
'con_code': VARCHAR(length=10),
'y': INT,
'm': INT,
'trade_date': DATE(),
'weight': DECIMAL(precision=10, scale=6)
}
df = df.reindex(
columns='index_code,con_code,y,m,trade_date,weight'.split(','))
df.to_sql(self.get_table_name(),
get_engine(),
dtype=dtype,
index=False,
if_exists='append')
def init_stock_price_monthly(ts_code, force=None):
table_name = 'stock_price_monthly'
if not need_pull_check(ts_code, table_name, force):
print('need not 2 pull {} -> {}'.format(table_name, ts_code))
return
else:
print('start 2 pull {} -> {} .'.format(table_name, ts_code))
df = get_pro().monthly(
ts_code=ts_code,
fields='ts_code,trade_date,open,high,low,close,vol,amount')
if not len(df):
return
df_add_y_m(df, 'trade_date')
dtype = {
'ts_code': VARCHAR(length=10),
'trade_date': DATE(),
'y': INT,
'm': INT,
'open': DECIMAL(precision=8, scale=2),
'high': DECIMAL(precision=8, scale=2),
'low': DECIMAL(precision=8, scale=2),
'close': DECIMAL(precision=8, scale=2),
'vol': BIGINT(),
'amount': BIGINT()
}
df.to_sql(table_name,
get_engine(),
dtype=dtype,
index=False,
if_exists='append')
def _update_ts(self):
cal_date = self._get_nearest_cal_date()
if cal_date is None:
return
trade_date = cal_date.strftime('%Y%m%d')
df = get_pro().daily_basic(ts_code='', trade_date=trade_date)
dtype = {
'ts_code': VARCHAR(length=10),
'trade_date': DATE(),
'close': FLOAT(),
'y': INT(),
'm': INT(),
'turnover_rate': FLOAT(),
'turnover_rate_f': FLOAT(),
'volume_ratio': FLOAT(),
'pe': FLOAT(),
'pe_ttm': FLOAT(),
'pb': FLOAT(),
'ps': FLOAT(),
'ps_ttm': FLOAT(),
'dv_ratio': FLOAT(),
'dv_ttm': FLOAT(),
'total_share': FLOAT(),
'float_share': FLOAT(),
'free_share': FLOAT(),
'total_mv': FLOAT(),
'circ_mv': FLOAT()
}
df.to_sql(self.get_table_name(),
get_engine(),
dtype=dtype,
index=False,
if_exists='append')
def test_():
lst = ['MSCI', 'CSI', 'SSE', 'SZSE', 'CICC', 'SW', 'OTH']
target = '512800'
for code in lst:
print(f'loading {code}')
df = get_pro().index_basic(market=code)
d = df[df['ts_code'].str.contains(target)]
if len(d) > 0:
print('code:', code)
print(d)
def _init_ts(self):
print('start init list...')
fileds = 'ts_code,symbol,name,area,industry,fullname,market,exchange,curr_type,list_status,list_date,delist_date,is_hs'
d_l = get_pro().query(self.interface,
exchange='',
list_status='L',
fields=fileds)
print('L', len(d_l))
d_d = get_pro().query(self.interface,
exchange='',
list_status='D',
fields=fileds)
print('D', len(d_d))
d_p = get_pro().query(self.interface,
exchange='',
list_status='P',
fields=fileds)
print('P', len(d_p))
df = d_l.append(d_d).append(d_p)
print('all size:', len(df))
dtype = {
'ts_code': VARCHAR(length=10),
'symbol': VARCHAR(length=8),
'name': VARCHAR(length=20),
'area': VARCHAR(length=10),
'industry': VARCHAR(length=32),
'fullname': VARCHAR(length=32),
'market': VARCHAR(length=10),
'exchange': VARCHAR(length=10),
'curr_type': VARCHAR(length=5),
'list_status': VARCHAR(length=1),
'list_date': DATE(),
'delist_date': DATE(),
'is_hs': VARCHAR(length=1)
}
df.to_sql(self.get_table_name(),
get_engine(),
dtype=dtype,
index=False,
if_exists='replace')
def init_fund_nav(ts_code, force=None):
table_name = 'fund_nav'
if not need_pull_check(ts_code, table_name, force):
print('need not 2 pull {} -> {}'.format(table_name, ts_code))
return
else:
print('start 2 pull {} -> {} .'.format(table_name, ts_code))
df = get_pro().fund_nav(ts_code=ts_code)
if not len(df):
return
df_add_y_m(df, 'end_date')
df.to_sql(table_name, get_engine(), index=False, if_exists='append')
def _update_ts(self):
sql = 'select * from trade_cal where m != 0 ;'
yms = pd.read_sql_query(sql, get_engine())
df = None
for i, row in yms.iterrows():
first_trade_date_str = row['first'].strftime('%Y%m%d')
last_last_date_str = row['last'].strftime('%Y%m%d')
data = get_pro().daily_basic(ts_code='',
trade_date=last_last_date_str)
print(last_last_date_str)
if df is None:
df = data
else:
df = df.append(data)
df_add_y_m(df, 'trade_date')
df.reset_index(drop=True)
df = df.iloc[::-1]
dtype = {
'ts_code': VARCHAR(length=10),
'trade_date': DATE(),
'close': FLOAT(),
'y': INT(),
'm': INT(),
'turnover_rate': FLOAT(),
'turnover_rate_f': FLOAT(),
'volume_ratio': FLOAT(),
'pe': FLOAT(),
'pe_ttm': FLOAT(),
'pb': FLOAT(),
'ps': FLOAT(),
'ps_ttm': FLOAT(),
'dv_ratio': FLOAT(),
'dv_ttm': FLOAT(),
'total_share': FLOAT(),
'float_share': FLOAT(),
'free_share': FLOAT(),
'total_mv': FLOAT(),
'circ_mv': FLOAT()
}
df.to_sql(self.get_table_name(),
get_engine(),
dtype=dtype,
index=False,
if_exists='replace')
def test_method_2():
from dao.db_pool import get_pro
df = get_pro().query('balancesheet', ts_code='002027.SZ')
print(df)
def _init_ts(self):
template_start = '{}00101'
template_end = '{}91231'
data = None
for i in range(4):
print(i)
t = 199 + i
start, end = template_start.format(t), template_end.format(t)
df = get_pro().query(self.interface,
start_date=start,
end_date=end)
if data is not None:
data = data.append(df, ignore_index=True)
else:
data = df
print('start:{},date:{}'.format(start, len(data)))
# data.to_sql('trade_date_o', get_engine(), if_exists='replace', schema=db_name)
df = data
df_add_y_m(df, 'cal_date')
# df['y'] = df['cal_date'].apply(lambda s: int(s[:4]))
# df['m'] = df['cal_date'].apply(lambda s: int(s[4:6]))
df.set_index(['y', 'm', 'cal_date'])
df = df[df['is_open'] == 1]
df = df.reindex(columns=['y', 'm', 'cal_date', 'is_open', 'exchange'])
df.to_sql(
self.get_table_name() + '_detail',
get_engine(),
index=False,
dtype={
'cal_date': DATE(),
'y': Integer(),
'm': INT(),
'is_open': INT(),
'exchange': VARCHAR(8)
},
# dtype={'cal_date': 'M8[d]'},
if_exists='replace')
'''
分组插入扩展表
'''
grouped_m = df.groupby(['y', 'm'])
# for a, g in grouped_m:
# print(a)
# print(g)
r1 = grouped_m['cal_date'].agg([np.min, np.max])
r1 = r1.rename(columns={'amin': 'first', 'amax': 'last'})
r1['y'] = pd.Series(r1.index.get_level_values('y'), index=r1.index)
r1['m'] = pd.Series(r1.index.get_level_values('m'), index=r1.index)
grouped_m = df.groupby(['y'])
r2 = grouped_m['cal_date'].agg([np.min, np.max])
r2 = r2.rename(columns={'amin': 'first', 'amax': 'last'})
r2['y'] = pd.Series(r2.index.get_level_values('y'), index=r2.index)
r2['m'] = pd.Series(np.zeros(len(r2)), index=r2.index)
r = r1.append(r2, ignore_index=True)
r = r.data(columns=['y', 'm', 'first', 'last'])
r.to_sql(self.get_table_name(),
get_engine(),
index=False,
dtype={
'first': DATE(),
'last': DATE(),
'y': Integer(),
'm': INT()
},
if_exists='replace')