def fhdatas(kf, kfv, tabn, tabc):
tuapi = tabc['tuapi']
dfield = tabc['dfield']
GLOGGER.debug('==S==> table:%s tc:%s tuapi:%s dfield:%s' %
(tabn, kfv, tuapi, dfield))
conn = tuh.getMysqlConn()
cursor = conn.cursor()
rdf = None
maxdate = tuh.getmaxdate(TABMAXVAL[tabn], kfv,
tabc.get('sdate', tuh.TUSDATE))
if tabc['loop']:
sdate = cd.ymd2date(maxdate)
edate = cd.preday()
edatestr = tabc.get('edate', '')
if edatestr:
edate = cd.ymd2date(edatestr)
while edate >= sdate:
kwargs = {
kf: kfv,
'start_date': sdate.strftime('%Y%m%d'),
'end_date': edate.strftime('%Y%m%d')
}
cut = tabc.get('cut', False)
cfield = tabc.get('cfield', '')
clen = tabc.get('clen', tuh.CUTMAXLEN)
df = tuh.gettudf(tuapi,
tabc['fields'],
kwargs,
spm=tabc['spm'],
cut=cut,
cfield=cfield,
clen=clen)
GLOGGER.debug('====LOOP DATAS==> sdate:%s edate:%s dflen:%s' %
(sdate, edate, len(df)))
if len(df) > 0:
if rdf is None:
rdf = df
else:
rdf = rdf.append(df)
if len(df) < 1 or pd.isnull(df[dfield].min()):
edate = cd.preday(sdate)
else:
edate = cd.preday(cd.ymd2date(df[dfield].min()))
else:
kwargs = {kf: kfv}
df = tuh.gettudf(tuapi, tabc['fields'], kwargs, spm=tabc['spm'])
df = df[df[dfield] >= maxdate]
if len(df) > 0:
rdf = df
GLOGGER.debug('====DATAS==> dflen:%s' % (len(df)))
if rdf is None:
rdf = pd.DataFrame(columns=(kf, ))
emsgs = tuh.savedialydf(tabn, rdf)
for emsg in emsgs:
GLOGGER.error(emsg)
GLOGGER.debug('==E==> table:%s tc:%s tuapi:%s rdflen:%s' %
(tabn, kfv, tuapi, len(rdf)))
cursor.close()
conn.close()
return rdf
def fetchstk(cursor, logger, table, tumethod, isql, stktc, sdate, edate,
batch):
while edate >= sdate:
df = fetchtudata(table, tumethod, stktc, sdate.strftime('%Y%m%d'),
edate.strftime('%Y%m%d'))
logger.debug('====SSSS====> %s ===>sd:%s ed:%s len(df):%s' %
(stktc, sdate, edate, len(df)))
if len(df) > 0:
cols = df.columns
isql = tuh.geninssql(cols, table, isql)
rowvs = []
for index, row in df.iterrows():
rowv = []
for col in cols:
rowv.append(row[col])
rowvs.append(rowv)
emsgs = tuh.saveorupdate({
'sql': isql,
'vals': rowvs
}, batch, cursor)
for m in emsgs:
logger.error(m)
logger.debug(
'====SAVEEND====> %s ===>sd:%s ed:%s ldf:%d mtd:%s emc:%d' %
(stktc, sdate, edate, len(df), df['trade_date'].min(),
len(emsgs)))
if len(df) < 1 or pd.isnull(df['trade_date'].min()):
edate = cd.preday(sdate)
else:
edate = cd.preday(cd.ymd2date(df['trade_date'].min()))
def fetchdataone(ind):
logger = __logmap[ind[0:1] + 'log']
conn = tuh.getMysqlConn()
cursor = conn.cursor(pymysql.cursors.DictCursor)
if __fav == 0 or __fav == 1:
cursor.execute(
"select t.ts_code from stk_basic t where t.del<>1 and t.fav=" +
str(__fav) + " order by t.ts_code;")
else:
cursor.execute(
"select t.ts_code from stk_basic t where t.del<>1 order by t.ts_code;"
)
stkbs = cursor.fetchall()
cursor.close()
cursor = conn.cursor(pymysql.cursors.DictCursor)
cursor.execute(
"select t.ts_code as stkc,max(t.trade_date) as md from stk_" + ind +
" t group by t.ts_code;")
stkmtdd = tuh.listToDict(cursor.fetchall(), 'stkc', 'md')
cursor.close()
tuapi = tuh.tuApi
isql = ''
cursor = conn.cursor()
for stk in stkbs:
stkc = stk['ts_code']
sdate = cd.ymd2date(tuh.getsdate(stkmtdd, stkc, __force))
edate = cd.preday()
while edate >= sdate:
df = fetchtudata(tuapi, ind, sdate.strftime('%Y%m%d'),
edate.strftime('%Y%m%d'), stkc)
logger.debug('====SSSS====> %s %s===>sd:%s ed:%s len(df):%s' %
(stkc, ind, sdate, edate, len(df)))
if len(df) > 0:
cols = df.columns
isql = tuh.geninssql(cols, 'stk_' + ind, isql)
rowvs = []
for index, row in df.iterrows():
rowv = []
for col in cols:
rowv.append(row[col])
rowvs.append(rowv)
emsgs = tuh.saveorupdate({'sql': isql, 'vals': rowvs})
for m in emsgs:
logger.error(m)
logger.debug(
'====SAVEEND====> %s %s===>sd:%s ed:%s ldf:%d mtd:%s emc:%d'
% (stkc, ind, sdate, edate, len(df),
df['trade_date'].min(), len(emsgs)))
if df is None or pd.isnull(df['trade_date'].min()):
edate = cd.preday(sdate)
else:
edate = cd.preday(cd.ymd2date(df['trade_date'].min()))
cursor.close()
conn.close()
def getsptudf(apin, fdf, tc=''):
if apin == 'daily' or apin == 'weekly' or apin == 'monthly':
fdf.rename(columns=STKSPOCOLMAP, inplace=True)
freq = apin[0:1].upper()
qdf = tspro.pro_bar(api=GTSAPI,
adj='hfq',
freq=freq,
ts_code=tc,
start_date=fdf['trade_date'].min(),
end_date=fdf['trade_date'].max())
qdfcl = cdatas.SPMDROPCOLS[apin[0:1]]
if not qdfcl:
fcols = fdf.columns.values
qcols = qdf.columns.values
for qcol in qcols:
if qcol in fcols and qcol != 'trade_date':
qdfcl.append(qcol)
qdf = qdf.drop(qdfcl, axis=1)
pdate = cd.preday().strftime('%Y%m%d')
fcts = GTSAPI.adj_factor(ts_code=tc, start_date=pdate, end_date=pdate)
if fcts is None or len(fcts) < 1:
fcts = GTSAPI.adj_factor(ts_code=tc)
for col in PRICE_COLS:
qdf[col] = qdf[col] / float(fcts['adj_factor'][0])
qdf[col] = qdf[col].map(FORMAT)
qdf[col] = qdf[col].astype(float)
qdf['change'] = qdf['close'] - qdf['pre_close']
qdf['pct_chg'] = qdf['change'] / qdf['pre_close'] * 100
fdf = fdf.set_index('trade_date',
drop=False).merge(qdf.set_index('trade_date'),
left_index=True,
right_index=True,
how='left')
return fdf
def fetchdata(table):
if table not in LOGGERMAP:
logger = tul.TuLog('fetch_' + table + '_x', '/log', True).getlog()
LOGGERMAP[table] = logger
logger = LOGGERMAP.get(table)
force = FORCEMAP.get(table, FORCE)
fav = FAVMAP.get(table, FAV)
batch = BATCHMAP.get(table, BATCH)
conn = tuh.getMysqlConn()
stktcs = tuh.getstktcs(fav)
stkmtdm = tuh.getstkmtdm(table)
tumethod = TUMETHODMAP[table]
logger.debug(
'====S====> table:%s tumethod:%s ===>force:%s fav:%s batch:%s' %
(table, tumethod, force, fav, batch))
cursor = conn.cursor()
isql = ''
for stktcm in stktcs:
stktc = stktcm['ts_code']
sdate = cd.ymd2date(tuh.getsdate(stkmtdm, stktc, force))
edate = cd.preday()
logger.debug('====SS====> %s ==== init ===>sd:%s ed:%s' %
(stktc, sdate, edate))
fetchstk(cursor, logger, table, tumethod, isql, stktc, sdate, edate,
batch)
cursor.close()
conn.close()
logger.debug('====E====> %s ============================' % (table, ))
def getIdxSdate(idxsdd, idxc, force=False):
sdate = idxsdd.get(idxc, tuh.tuSdate)
if sdate > tuh.tuSdate:
dsdate = cd.ymd2date(sdate)
dsdate = cd.preday(dsdate, -1)
sdate = dsdate.strftime('%Y%m%d')
if force or sdate < tuh.tuSdate:
sdate = tuh.tuSdate
return sdate
def fetchdataone():
logger = LOGGER
conn = tuh.getMysqlConn()
stktcs = tuh.getstktcs(FAV)
stkmtdm = tuh.getstkmtdm('stk_daily_basic')
tuapi = tuh.tuApi
isql = ''
cursor = conn.cursor()
for stktcm in stktcs:
stktc = stktcm['ts_code']
sdate = cd.ymd2date(tuh.getsdate(stkmtdm, stktc, FORCE))
edate = cd.preday()
while edate >= sdate:
df = fetchtudata(tuapi, stktc, sdate.strftime('%Y%m%d'),
edate.strftime('%Y%m%d'))
logger.debug('====SSSS====> %s ===>sd:%s ed:%s len(df):%s' %
(stktc, sdate, edate, len(df)))
if df is not None and len(df) > 0:
cols = df.columns
isql = tuh.geninssql(cols, TABLE, isql)
rowvs = []
for index, row in df.iterrows():
rowv = []
for col in cols:
rowv.append(row[col])
rowvs.append(rowv)
emsgs = tuh.saveorupdate({'sql': isql, 'vals': rowvs}, BATCH)
for m in emsgs:
logger.error(m)
logger.debug(
'====SAVEEND====> %s ===>sd:%s ed:%s ldf:%d mtd:%s emc:%d'
% (stktc, sdate, edate, len(df), df['trade_date'].min(),
len(emsgs)))
if df is None or pd.isnull(df['trade_date'].min()):
edate = cd.preday(sdate)
else:
edate = cd.preday(cd.ymd2date(df['trade_date'].min()))
cursor.close()
conn.close()
def getmaxdate(maxvmap, gfield, defv=TUSDATE, force=False):
if not maxvmap:
maxd = defv
else:
maxd = maxvmap.get(gfield, defv)
if maxd > defv:
dmaxd = cd.preday(cd.ymd2date(maxd), -1)
maxd = dmaxd.strftime('%Y%m%d')
if force or maxd < defv:
maxd = defv
return maxd
def getsdate(dmap, stkc, defsdate=TUSDATE, force=False):
if not dmap:
sdate = defsdate
else:
sdate = dmap.get(stkc, defsdate)
if sdate > defsdate:
dsdate = cd.ymd2date(sdate)
dsdate = cd.preday(dsdate, -1)
sdate = dsdate.strftime('%Y%m%d')
if force or sdate < defsdate:
sdate = defsdate
return sdate
def fetchtudata(api, ind, sdate, edate, stkc):
excnt = 0
while excnt < tuh.tumaxexcnt:
try:
if ind == 'm' or ind == 'monthly':
fdf = api.monthly(ts_code=stkc,
start_date=sdate,
end_date=edate)
elif ind == 'w' or ind == 'weekly':
fdf = api.weekly(ts_code=stkc,
start_date=sdate,
end_date=edate)
else:
fdf = api.daily(ts_code=stkc, start_date=sdate, end_date=edate)
print(fdf)
if fdf is not None and len(fdf) > 0:
fdf.rename(columns=__ocolmap, inplace=True)
freq = ind[0:1].upper()
qdf = tuh.tspro.pro_bar(api=api,
adj='hfq',
freq=freq,
ts_code=stkc,
start_date=fdf['trade_date'].min(),
end_date=fdf['trade_date'].max())
qdfcl = __qdfcmap[ind[0:1]]
if not qdfcl:
fcols = fdf.columns.values
qcols = qdf.columns.values
for qcol in qcols:
if qcol in fcols and qcol != 'trade_date':
qdfcl.append(qcol)
qdf = qdf.drop(qdfcl, axis=1)
pdate = cd.preday().strftime('%Y%m%d')
fcts = api.adj_factor(ts_code=stkc,
start_date=pdate,
end_date=pdate)
if fcts is None or len(fcts) < 1:
fcts = api.adj_factor(ts_code=stkc)
for col in PRICE_COLS:
qdf[col] = qdf[col] / float(fcts['adj_factor'][0])
qdf[col] = qdf[col].map(FORMAT)
qdf[col] = qdf[col].astype(float)
qdf['change'] = qdf['close'] - qdf['pre_close']
qdf['pct_chg'] = qdf['change'] / qdf['pre_close'] * 100
fdf = fdf.set_index('trade_date', drop=False).merge(
qdf.set_index('trade_date'),
left_index=True,
right_index=True,
how='left')
break
except BaseException as e:
print(e)
excnt += 1
if excnt == tuh.tumaxexcnt:
raise e
else:
time.sleep(60)
continue
fdf = tuh.cleandf(fdf)
return fdf
def fetchData(ind):
logger = tul.TuLog('fetch_idx_' + ind + '_x', '/log').getlog()
conn = tuh.getMysqlConn()
cursor = conn.cursor(pymysql.cursors.DictCursor)
if __fav == 0 or __fav == 1:
cursor.execute(
"select t.ts_code from idx_basic t where t.del<>1 and t.fav=" +
str(__fav) + " order by t.ts_code;")
else:
cursor.execute(
"select t.ts_code from idx_basic t where t.del<>1 order by t.ts_code;"
)
idxcs = cursor.fetchall()
cursor.close()
cursor = conn.cursor(pymysql.cursors.DictCursor)
cursor.execute(
"select t.ts_code as idc,max(t.trade_date) as itd from idx_" + ind +
" t group by t.ts_code;")
idxsdd = tuh.listToDict(cursor.fetchall(), 'idc', 'itd')
cursor.close()
tuapi = tuh.tuApi
isql = ''
cursor = conn.cursor()
for idxd in idxcs:
idxc = idxd['ts_code']
sdate = cd.ymd2date(getIdxSdate(idxsdd, idxc, __force))
edate = cd.preday()
while edate >= sdate:
try:
df = fetchTuData(tuapi, ind, sdate.strftime('%Y%m%d'),
edate.strftime('%Y%m%d'), idxc)
if len(df) > 0:
cols = df.columns
isql = tuh.genInsSql(cols, 'idx_' + ind, isql)
rowvs = []
for index, row in df.iterrows():
rowv = []
for col in cols:
rowv.append(row[col])
rowvs.append(rowv)
emsgs = tuh.insertDatas({'sql': isql, 'vals': rowvs})
for m in emsgs:
logger.error(m)
print(idxc, '===>sd:', sdate, ' ed:', edate, ' ldf:',
len(df), ' mtd:', df['trade_date'].min(), ' emc:',
len(emsgs))
else:
print(idxc, '===>sd:', sdate, ' ed:', edate, ' ldf:',
len(df))
except BaseException as e:
print(e)
time.sleep(60)
continue
if pd.isnull(df['trade_date'].min()):
edate = cd.preday(sdate)
else:
edate = cd.preday(cd.ymd2date(df['trade_date'].min()))
cursor.close()
conn.close()