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)