def reflesh_profit_prices(codes=getinfo.get_codes(), start='2015-05-01'):
print('start reflashing newly hightest and lowest price on table [profit]')
ps = Postgresql()
SQL, i, s = '', 0, len(codes)
for c in codes:
i += 1
sql = "SELECT p_change, close, volume, turnover FROM data_hist WHERE code='%s' AND date>='%s' ORDER BY date DESC LIMIT 1" % (
c, start)
r = ps.fetchone(sql)
if r is None: continue
pc, np, vol, turn = r[0], r[1], r[2], r[3]
sql = "SELECT max(high*qfq), min(low*qfq) FROM data_hist WHERE code='%s' AND date>='%s'" % (
c, start)
r = ps.fetchone(sql)
hp, lp = r[0], r[1]
SQL += "UPDATE profit SET hp=%.2f, lp=%.2f, np=%.2f, lhr=%f, nlr=%f, nhr=%f, vol=%f, turn=%f, pc=%f "\
"WHERE code='%s';"%(hp, lp, np, lp/hp, (np-lp)/lp, np/hp, vol, turn, pc, c)
print('[%d/%d=%.1f%%] [%s] stock prices info computed!' %
(i, s, float(i) / float(s) * 100, c))
ps.execute(SQL)
print('all stock newly prices info is saved into table [profit]')
fb.write('start reflashing pe & pb ... ')
SQL = "UPDATE profit SET pb=np/bvps WHERE bvps!=0;"
SQL += "UPDATE profit SET pe=np/esp WHERE esp!=0;"
ps.execute(SQL)
print(' is done!')
def fetch_fund_holdings():
'''基金持股'''
df = ts.fund_holdings(2015, 1)
sql = ''
for r in df.values:
cur.execute(
"SELECT code FROM stock_fund_holdings WHERE code='%s' AND date='%s';"
% (r[0], r[2]))
R = cur.fetchone()
if R is None:
sql += "INSERT INTO stock_fund_holdings (code, name, date, nums, nlast, count, clast, amount, ratio) VALUES ('%s', '%s', '%s', '%d', '%d', '%f', '%f', '%f', '%f');" % (
r[0], r[1], r[2], int(r[3]), int(r[4]), float(r[5]), float(
r[6]), float(r[7]), float(r[8]))
else:
sql += "UPDATE stock_fund_holdings SET nums=%d, nlast=%d, count=%f, clast=%f, amount=%f, ratio=%f WHERE code='%s' AND date='%s';" % (
int(r[3]), int(r[4]), float(r[5]), float(r[6]), float(
r[7]), float(r[8]), r[0], r[2])
sql += "UPDATE stock_list SET jjcg=true WHERE code='%s';" % r[0]
cur.execute(sql)
conn.commit()
fb.write(' is done!\n')
def reflesh_profit_stock_info():
fb.write('start refleshing stock info on table [profit] .')
ps = Postgresql()
sql = "SELECT code, name, industry, area, bvps, esp, outstanding, totals, \"timeToMarket\" FROM stock_basics ORDER BY code"
rs = ps.fetchall(sql)
i = 0
for r in rs:
ps.update_insert(table='profit',
where="code='%s'" % r[0],
code=r[0],
name=r[1],
industry=r[2],
area=r[3],
bvps=r[4],
esp=r[5],
outs=r[6],
tots=r[7],
market=str(r[8]))
i += 1
if i % 100 == 0:
fb.write('.')
ps.close()
fb.write(' is done!\n')
def fetch_stock_basics():
fb.write('start fetching stock basic info ...')
df = ts.get_stock_basics()
df.to_sql('stock_basics', ENGINE, if_exists='replace')
print('is done!')
fb.write(
'start copying code and name from table [stock_basics] to table [stock_list] ...'
)
ps = Postgresql()
'''
rs = ps.fetchall("SELECT code, name FROM stock_basics ORDER BY code ASC;")
for r in rs:
ps.update_insert(table='stock_list', where="code='%s'"%r[0], code=r[0], name=r[1])
fb.write(' is done!\n')
fb.write('start fleshing hushi shenshi chuangyeban zhongxiaoban code ...')
sql = "UPDATE stock_list SET sh=TRUE WHERE substr(code,1,1)='6';"\
"UPDATE stock_list SET sz=TRUE WHERE substr(code,1,1)='0' OR substr(code,1,1)='3';"\
"UPDATE stock_list SET cyb=TRUE WHERE substr(code,1,1)='3';"\
"UPDATE stock_list SET zxb=TRUE WHERE substr(code,1,3)='002';"
ps.execute(sql)
fb.write(' is done!\n')
fb.write('start fetching ST stock code ...')
sql = ''
for vs in ts.get_st_classified().values:
sql += "UPDATE stock_list SET st=TRUE WHERE code='%s';"%vs[0]
ps.execute(sql)
fb.write(' is done!\n')
fb.write('start fetching hushen 300 stock code...')
sql = ''
for vs in ts.get_hs300s().values:
sql += "UPDATE stock_list SET hssb=TRUE WHERE code='%s';"%vs[0]
ps.execute(sql)
fb.write('is done!\n')
fb.write('start fetching shangzheng 50 stock code...')
sql = ''
for vs in ts.get_sz50s().values:
sql += "UPDATE stock_list SET szwl=TRUE WHERE code='%s';"%vs[0]
ps.execute(sql)
fb.write('is done!\n')
fb.write('start fetching tradable stocke code ...')
sql = ''
for vs in ts.get_today_all().values:
sql += "UPDATE stock_list SET tradable=TRUE WHERE code='%s';"%vs[0]
ps.execute(sql)
fb.write(' is done!\n')
fb.write('start fetching stock industry class...')
sql = ''
for vs in ts.get_industry_classified().values:
sql += "UPDATE stock_list SET industry='%s' WHERE code='%s';" % (vs[2], vs[0])
ps.execute(sql)
fb.write(' is done!\n')
'''
fb.write('start fetching stock concept class...')
sql = ''
for vs in ts.get_concept_classified().values:
sql += "UPDATE stock_list SET concept='%s' WHERE code='%s';" % (vs[2],
vs[0])
sql += "UPDATE profit SET concept='%s' WHERE code='%s';" % (vs[2],
vs[0])
ps.execute(sql)
fb.write(' is done!\n')
fb.write('start fetching stock area class...')
sql = ''
for vs in ts.get_area_classified().values:
sql += "UPDATE stock_list SET area='%s' WHERE code='%s';" % (vs[2],
vs[0])
ps.execute(sql)
fb.write(' is done!\n')
ps.close()
