def ins_STKS(self, opt):
sql = '''select to_char(max(trdate),'yyyymmdd'),to_char(sysdate,'yyyymmdd') from stk_mkt_ex_t'''
y = self.cur.execute(sql).fetchone()
if y[0] <> y[1]: return
sql = '''
select stkcode,to_char(trdate,'mm/dd') trdate,0 pro,'1d'||seq_ml.nextval id
from bs_t1
where trdate=to_date('%s','yyyymmdd') and status=1 and stkcode in (select stkcode from stk_good_t)
'''
rows = self.cur.execute(sql % (y[1], )).fetchall()
# rows = self.cur.execute(sql % ('20160711',)).fetchall()
e = etl()
e.setML('mxjryx', rows, 9) # 更新股票列表
self.profit_GOOD(y[1])
e.setMX()
def guzhi():
sql = '''
select '#估值#'||to_char(sysdate,'mm/dd hh24:mi')||' PEG: '||round(p.pe/cb.jl_tb,2)||', '|| case when p.pe/cb.jl_tb>2 then '超过合理估值'
when p.pe/cb.jl_tb<1 then '低于合理估值'
when p.pe/cb.jl_tb between 1 and 2 then '合理估值区'
end
,p.stkcode
from
(select c.stkcode,c.jl_tb
from cb_t c,(select stkcode,max(rpt_type) rpt_type from cb_t group by stkcode) m
where c.stkcode=m.stkcode and c.rpt_type=m.rpt_type) cb,
(select stkcode,pe from STK_CODE_T t) p
where cb.stkcode=p.stkcode and p.stkcode in (select stkcode from stk_cb_t) and p.pe/cb.jl_tb>0
'''
rows = cur.execute(sql).fetchall()
for r in rows:
cur.execute('''update stk_dig_t set content='%s',cdate=sysdate where stkcode='%s' ''' % r)
db.commit()
e = etl()
e.setDIG()
def tuning_1HOUR(self):
# 优化1小时线所有股票
e = etl()
if e.getConf('ma1h', 1) <> 1: return
# self.cur.execute('dbms_mview.refresh',['mv_stk_td_1h','c'])
self.cur.execute('truncate table ma_tu_1h_t') # 清除上次优化结果
sql = '''select trdate,exb cl,ch from mv_stk_td_1h where stkcode='%s' order by 1'''
stks = self.cur.execute('select stkcode from vw_stk_code').fetchall()
for s in stks:
d = pd.read_sql(sql % s, self.db)
if len(d) < 40: continue
rst = self.tuning(d) # 计算最优均线及周期
if rst:
rst.append(s[0])
self.cur.execute(
''' insert into ma_tu_1h_t values('%s',%s,%s,%s,%s,'%s')'''
% tuple(rst))
self.db.commit()
self.cur.callproc('dbms_mview.refresh', ['mv_ma_tu_1h', 'c'])
e.setConf('ma1h', 30)
elif value[0] <= g[2] and value[0] > g[3]:
ri.append(info[3])
else:
ri.append(info[4])
if value[0] > value[1]:
ri.append(info[5])
elif value[0] < value[1]:
ri.append(info[6])
else:
ri.append(info[7])
return ri
stks = get_stock_list()
cur = db1.cursor()
sql = '''update stk_dig_t set content='#个股状态# '||to_char(sysdate,'mm/dd ')||'%s',cdate=sysdate where stkcode='%s' '''
for s in stks:
r = get_r(s[0])
if len(r) > 0:
m = get_grade_info(r)
t = '处于' + m[0] + '行情, ' + '走势' + m[1]
sql1 = sql % (t, s[0])
cur.execute(sql1)
db1.commit()
#print chardet.detect(sql1),sql1
e = etl()
e.setDIG()
def bs_1HOUR(self, opt):
# 标记日线买卖点
y = [0, '200001010101']
y = self.cur.callproc('sync_stk_td_1h_p', y)
if y[0] < 1 and opt <> 9: return
if opt == 9:
self.cur.execute('truncate table bs_1h_t')
sql = 'select stkcode from vw_stk_code'
stks = self.cur.execute(sql).fetchall()
y[1], = self.cur.execute(
'''select to_char(max(trdate),'yyyymmddhh24mi') trdate from mv_stk_td_1h'''
).fetchone()
else:
if y[0] == 0: return
sql = '''select distinct stkcode from mv_stk_td_1h where trdate=to_date('%s','yyyymmddhh24mi')'''
stks = self.cur.execute(sql % (y[1], )).fetchall()
if not stks: return
for s in stks:
x = self.cur.execute(
'''select ma,d1,d2,t from mv_ma_tu_1h where stkcode='%s' ''' %
s).fetchone()
if not x: continue
if x[3] == 0:
x = ['kama', 10, 0, 1]
sql = '''
select trdate,cl,ch from mv_stk_td_1h where stkcode='%s' order by 1
'''
d = pd.read_sql(sql % s, self.db)
if len(d) < x[1]: continue
d = self.bs(d, x)
sql = '''insert into bs_1h_t values ('%s',%s,to_date('%s','yyyymmddhh24mi'))'''
if opt == 9:
for v in d.values:
if v[-1] in (1, -1):
self.cur.execute(
sql % (s[0], v[-1], v[0].strftime('%Y%m%d%H%M')))
else:
v = d.values[-1]
if v[-1] in (1, -1):
self.cur.execute(
sql % (s[0], v[-1], v[0].strftime('%Y%m%d%H%M')))
sql = '''
update stk_dig_t
set content='#小时线BS点# '||'%s'||
case %s when 2 then ' 持股' when 1 then ' 买入' when -1 then ' 卖出' when -2 then ' 持币' else '状态不明' end
, cdate=sysdate
where stkcode='%s'
'''
self.cur.execute(sql % (v[0].strftime('%m/%d %H:%M'), v[-1], s[0]))
self.db.commit()
# 更新信息
e = etl()
e.setGGSJ(1) # 更新日线数据
e.setDIG() #更新摘要信息
sql = '''
select stkcode,to_char(trdate,'mm/dd hh24:mi') trdate,0 pro,'1h'||seq_ml.nextval id
from bs_1h_t
where trdate=to_date('%s','yyyymmddhh24mi') and status=1
'''
rows = self.cur.execute(sql % (y[1], )).fetchall()
e.setML('mxjrjh1h', rows, 9) # 更新股票列表
self.profit_1HOUR(y[1])
e.setMX() # 更新模型统计数据
e.setConf('data1h', 0)
e.setConf('bs1h', 0)
def bs_DAY(self, opt):
# 标记日线买卖点
e1 = ex()
e1.EX_ALL()
y = [0, '20000101']
y = self.cur.callproc('sync_stk_td_p', y)
if y[0] < 1 and opt <> 9: return
if opt == 9:
self.cur.execute('truncate table bs_t')
sql = 'select stkcode from vw_stk_code'
stks = self.cur.execute(sql).fetchall()
y[1], = self.cur.execute(
'''select to_char(max(trdate),'yyyymmdd') trdate from mv_stk_td'''
).fetchone()
else:
if y[0] == 0: return
sql = '''select distinct stkcode from mv_stk_td where trdate=to_date('%s','yyyymmdd')'''
stks = self.cur.execute(sql % (y[1], )).fetchall()
if not stks: return
for s in stks:
x = self.cur.execute(
'''select ma,d1,d2,t from mv_ma_tu where stkcode='%s' ''' %
s).fetchone()
if not x: continue
if x[3] == 0:
x = ['kama', 10, 0, 1]
sql = '''
select trdate,exb cl,ch from mv_stk_td where stkcode='%s' order by 1
'''
d = pd.read_sql(sql % s, self.db)
if len(d) < x[1]: continue
d = self.bs(d, x)
sql = '''insert into bs_t values ('%s',%s,to_date('%s','yyyymmdd'))'''
if opt == 9:
for v in d.values:
if v[-1] in (1, -1):
self.cur.execute(
sql % (s[0], v[-1], v[0].strftime('%Y%m%d')))
else:
v = d.values[-1]
if v[-1] in (1, -1):
self.cur.execute(sql %
(s[0], v[-1], v[0].strftime('%Y%m%d')))
self.cur.execute(
'''update stk_dig_t set status=%s, stdate=sysdate where stkcode='%s' '''
% (v[-1], s[0]))
self.db.commit()
# 更新信息
e = etl()
e.setGGSJ(0) # 更新日线数据
e.setDIG() #更新摘要信息
sql = '''
select stkcode,to_char(trdate,'mm/dd') trdate,0 pro,'1d'||seq_ml.nextval id
from bs_t
where trdate=to_date('%s','yyyymmdd') and status=1
'''
rows = self.cur.execute(sql % (y[1], )).fetchall()
e.setML('mxjrjh', rows, 9) # 更新股票列表
self.profit_DAY(y[1])
e.setMX() # 更新模型统计数据
e.setConf('bs1d', 0)