def get_previous_average_line(self, ma, security_code, the_date): sql = "select close_avg, amount_avg, vol_avg, price_avg, the_date " \ "from tquant_stock_average_line " \ "where security_code = {security_code} " \ "and ma = {ma} and the_date < {the_date} " \ "order by the_date desc limit 1".format(security_code=Utils.quotes_surround(security_code), ma=ma, the_date=Utils.quotes_surround(the_date.strftime('%Y-%m-%d'))) # print('get_previous_average_line', sql) previous_data = self.query(sql) return previous_data
def get_stock_day_kline(self, security_code, decline_ma_the_date): sql = "select the_date, close, amount, vol, price_avg " \ "from tquant_stock_day_kline " \ "where security_code = {security_code} " sql = sql.format(security_code=Utils.quotes_surround(security_code)) if decline_ma_the_date is not None: sql += "and the_date >= {max_the_date} " max_the_date = decline_ma_the_date.strftime('%Y-%m-%d') sql = sql.format(max_the_date=Utils.quotes_surround(max_the_date)) sql += "order by the_date asc " result = self.query(sql) return result
def processing_section(self, section, dict_data_pre): """ 处理切片,分3、5、10等切片数据 :param section: DataFrame切片数据 example amount close code high low open vol date 1991-11-29 8557000 0.11 1 0.13 0.11 0.13 306200 1991-11-30 3962000 0.11 1 0.11 0.11 0.11 142900 1991-12-02 11099000 0.10 1 0.11 0.10 0.11 410500 1991-12-03 6521000 0.12 1 0.12 0.10 0.10 234800 1991-12-04 6853000 0.11 1 0.12 0.11 0.12 242700 1991-12-05 3458000 0.10 1 0.11 0.10 0.11 126000 1991-12-06 4341000 0.11 1 0.11 0.10 0.10 159400 1991-12-07 2218000 0.11 1 0.11 0.11 0.11 80600 :param dict_data_pre: 前一交易日数据 格式类似本次返回的数据 :return: """ dict_data = { 'security_code': Utils.quotes_surround(self.security_code) } # 日涨跌幅计算 section_tail1 = section.tail(1) idx = section_tail1.index.values[0] if isinstance(idx, datetime.datetime) or isinstance( idx, datetime.date): the_date = idx else: the_date = idx.astype('M8[ms]').astype('O') week_day = Utils.format_week_day(the_date) dict_data['week_day'] = week_day the_date = Utils.format_date(the_date) dict_data['the_date'] = Utils.quotes_surround(the_date) if dict_data_pre is None: print('secuirty_code', self.security_code, '第一次dict_data_pre为None') # 转换时间格式,结果为<class 'datetime.datetime'> 1991-11-29 00:00:00 dict_data_pre = self.get_dict_data_pre(the_date) if dict_data_pre is None: print('secuirty_code', self.security_code, '查询后dict_data_pre还是为None,只能创建为0的数据') dict_data_pre = self.create_blank_dict_data() self.processing_1_day_chg(section_tail1, idx, dict_data, dict_data_pre) for days in [1, 3, 5, 10]: self.processing_avg(section.tail(days), days, dict_data, dict_data_pre) self.dbService.upsert(dict_data, 'tquant_stock_history_quotation', ['security_code', 'the_date']) return dict_data
def calculate_last_10_day(self, the_date): sql = StockOneTable.get_select_sql() \ + " where security_code = {security_code} and the_date <= {the_date} " \ "order by the_date desc limit 10" sql = sql.format(security_code=Utils.quotes_surround( self.security_code), the_date=Utils.quotes_surround(the_date)) tuple_datas = self.dbService.query(sql) if tuple_datas is not None and len(tuple_datas) == 10: list_index = [] list_open = [] list_high = [] list_low = [] list_close = [] list_amount = [] list_vol = [] i = len(tuple_datas) - 1 while i >= 0: dict_data = StockOneTable.get_dict_from_tuple(tuple_datas[i]) list_index.append(dict_data['the_date']) list_open.append(dict_data['open']) list_high.append(dict_data['high']) list_low.append(dict_data['low']) list_close.append(dict_data['close']) list_amount.append(dict_data['amount']) list_vol.append(dict_data['vol']) i -= 1 if len(list_index) == 10: section = pandas.DataFrame(index=list_index, data={ 'open': list_open, 'high': list_high, 'low': list_low, 'close': list_close, 'amount': list_amount, 'vol': list_vol }) dict_data_pre = self.get_dict_data_pre(the_date) if dict_data_pre is not None: self.processing_section(section, dict_data_pre) print('security_code', self.security_code, 'the_date', the_date, '计算实时行情计算完毕') else: print('security_code', self.security_code, 'the_date', the_date, '计算实时行情查询dict_data_pre为None,不做计算') else: print('security_code', self.security_code, 'the_date', the_date, '计算实时行情不够最近10条,不做计算')
def get_average_line_avg_max_the_date(self, ma, security_code): sql = "select the_date max_the_date from tquant_stock_average_line " \ "where security_code = {security_code} " \ "and ma = {ma} " \ "and close_avg_chg is not null " \ "and amount_avg_chg is not null " \ "and vol_avg_chg is not null " \ "and price_avg_chg is not null " \ "and amount_flow_chg is not null " \ "and vol_flow_chg is not null " \ "and close_avg_chg_avg is not null " \ "and amount_avg_chg_avg is not null " \ "and vol_avg_chg_avg is not null " \ "and price_avg_chg_avg is not null " \ "and amount_flow_chg_avg is not null " \ "and vol_flow_chg_avg is not null " \ "order by the_date desc limit 1" the_date = self.query(sql.format(security_code=Utils.quotes_surround(security_code), ma=ma ) ) if the_date is not None and len(the_date) > 0: return the_date[0][0] else: return None
def get_day_kline_upsertsql(security_code, dict_data, is_exist): if is_exist: sql = "update tquant_stock_history_quotation " \ "set amount = {amount}, vol = {vol}, open={open}, high={high}, low={low}, close = {close} " \ "where security_code = {security_code} and the_date = {the_date}" else: sql = "insert into tquant_stock_history_quotation (security_code, the_date, amount, vol, open, high, low, close) " \ "values ({security_code}, {the_date}, {amount}, {vol}, {open}, {high}, {low}, {close}) " sql = sql.format(security_code=Utils.quotes_surround(security_code), the_date=Utils.quotes_surround(dict_data['the_date']), amount=dict_data['amount'], vol=dict_data['vol'], open=dict_data['open'], high=dict_data['high'], low=dict_data['low'], close=dict_data['close']) return sql
def get_chg_avg_pre_data(self, security_code, the_date, ma): sql = "select close_avg_chg_avg, close_avg_chg_avg_diff, " \ "amount_avg_chg_avg, amount_avg_chg_avg_diff, " \ "vol_avg_chg_avg, vol_avg_chg_avg_diff, " \ "price_avg_chg_avg, price_avg_chg_avg_diff, " \ "amount_flow_chg_avg, amount_flow_chg_avg_diff, " \ "vol_flow_chg_avg, vol_flow_chg_avg_diff " \ "from tquant_stock_average_line " \ "where security_code = {security_code} " \ "and the_date < {the_date} " \ "and ma = {ma} " \ "order by the_date desc limit 1" sql = sql.format(security_code=Utils.quotes_surround(security_code), the_date=Utils.quotes_surround(the_date.strftime('%Y-%m-%d')), ma=ma) tuple_data = self.query(sql) return tuple_data
def get_average_line_avg_decline_max_the_date(self, ma, average_line_avg_max_the_date, security_code): if average_line_avg_max_the_date is not None and average_line_avg_max_the_date != '': sql = "select the_date from tquant_stock_day_kline " \ "where security_code = {security_code} " \ "and the_date <= {average_line_max_the_date} " \ "order by the_date desc limit {ma} " the_dates = self.query(sql.format(security_code=Utils.quotes_surround(security_code), average_line_max_the_date=Utils.quotes_surround(str(average_line_avg_max_the_date)), ma=ma + 2 ) ) if the_dates is not None and len(the_dates) > 0: decline_ma_the_date = the_dates[len(the_dates) - 1][0] return decline_ma_the_date else: return None else: return None
def get_max_the_date(self): sql = "select the_date from tquant_stock_history_quotation where security_code = {security_code} order by the_date desc limit 10" the_dates = self.dbService.query( sql.format( security_code=Utils.quotes_surround(self.security_code))) if the_dates is not None and len(the_dates) > 0: return the_dates[len(the_dates) - 1][0] else: return None
def get_day_kline_exist_max_the_date(self, security_code): sql = "select the_date from tquant_stock_day_kline " \ "where security_code = {security_code} " \ "order by the_date desc limit 1" sql = sql.format(security_code=Utils.quotes_surround(security_code)) the_date = self.query(sql) # print(sql) if the_date is not None and len(the_date) > 0: return the_date[0][0] else: return None
def get_day_kline_chg_upsertsql(security_code, the_date, dict_data): """ 根据计算得出的股票日K涨跌幅指标数据,生成需要更新的sql语句 :param security_code: 股票代码,字符串类型 :param the_date: 交易日,日期类型 :param dict_data: 股票涨跌幅数据,字典类型 :return: """ sql = "update tquant_stock_history_quotation set vol_chg = {vol_chg}, " \ "close_chg = {close_chg}, price_avg = {price_avg}, price_avg_chg = {price_avg_chg}, " \ "close_price_avg_chg = {close_price_avg_chg}, close_open_chg = {close_open_chg} " \ "where security_code = {security_code} and the_date = {the_date} " sql = sql.format(vol_chg=dict_data['vol_chg'], close_chg=dict_data['close_chg'], price_avg=dict_data['price_avg'], price_avg_chg=dict_data['price_avg_chg'], close_price_avg_chg=dict_data['close_price_avg_chg'], close_open_chg=dict_data['close_open_chg'], security_code=Utils.quotes_surround(security_code), the_date=Utils.quotes_surround( Utils.format_date(the_date))) return sql
def get_deviated_chg_max_the_date(self, security_code): sql = "select the_date from tquant_stock_day_kline " \ "where security_code = {security_code} " \ "and open_low_chg is not null " \ "and high_low_chg is not null " \ "and high_close_chg is not null " \ "and close_open_chg is not null " \ "order by the_date desc " \ "limit 1" max_the_date = self.query(sql.format(security_code=Utils.quotes_surround(security_code))) if max_the_date is not None and len(max_the_date) > 0: # return max_the_date[0][0] return None else: return None
def get_average_line(self, ma, security_code, decline_ma_the_date): sql = "select the_date, " \ "close_avg_chg, amount_avg_chg, vol_avg_chg, " \ "price_avg_chg, amount_flow_chg, vol_flow_chg " \ "from tquant_stock_average_line " \ "where security_code = {security_code} " \ "and ma = {ma} " \ "and close_avg_chg is not null " \ "and amount_avg_chg is not null " \ "and vol_avg_chg is not null " \ "and price_avg_chg is not null " \ "and amount_flow_chg is not null " \ "and vol_flow_chg is not null " max_the_date = None if decline_ma_the_date is not None: sql += "and the_date >= {max_the_date} " max_the_date = decline_ma_the_date.strftime('%Y-%m-%d') sql += "order by the_date asc " sql = sql.format(security_code=Utils.quotes_surround(security_code), max_the_date=Utils.quotes_surround(max_the_date), ma=ma ) result = self.query(sql) return result
def get_day_kline_max_the_date(self, security_code): sql = "select the_date max_the_date from tquant_stock_day_kline " \ "where security_code = {security_code} " \ "and close_pre is not null and close_chg is not null " \ "order by the_date desc limit 2" the_date = self.query(sql.format(security_code=Utils.quotes_surround(security_code) ) ) if the_date is not None and len(the_date) > 0: if len(the_date) == 2: return the_date[1][0] else: return the_date[0][0] else: return None
def get_dict_data_pre(self, the_date): sql = StockOneTable.get_select_sql() \ + " where security_code = {security_code} and the_date < {the_date} " \ "order by the_date desc limit 1" sql = sql.format(security_code=self.security_code, the_date=Utils.quotes_surround(the_date)) tuple_datas = self.dbService.query(sql) if tuple_datas is not None and len(tuple_datas) > 0: tuple_data = tuple_datas[0] if tuple_data is not None and len(tuple_data) > 0: if tuple_data[0] is None: return None else: dict_data = StockOneTable.get_dict_from_tuple(tuple_data) return dict_data else: return None else: return None
def processing_average_line(ma, security_code, r, queue): """ 股票均线数据处理方法 :param ma: :return: """ average_line_max_the_date = StockOneStopProcessor.dbService.get_average_line_max_the_date( ma, security_code) decline_ma_the_date = StockOneStopProcessor.dbService.get_average_line_decline_max_the_date( ma, average_line_max_the_date) r.lpush(queue, [ 'StockOneStopProcessor', StockOneStopProcessor.get_method_name(), 'ma', ma, security_code, 'average_line_max_the_date', average_line_max_the_date, 'decline_ma_the_date', decline_ma_the_date ]) # print('decline_ma_the_date', decline_ma_the_date, 'average_line_max_the_date', average_line_max_the_date) result = StockOneStopProcessor.dbService.get_stock_day_kline( security_code, decline_ma_the_date) len_result = len(result) # print('ma', ma, 'len_result', len_result) if len_result < ma: return try: if result is not None and len_result > 0: # 开始解析股票日K数据, the_date, close # 临时存储批量更新sql的列表 upsert_sql_list = [] # 需要处理的单只股票进度计数 add_up = 0 # 需要处理的单只股票进度打印字符 process_line = '=' # 循环处理security_code的股票日K数据 i = 0 # 由于是批量提交数据,所以在查询前一日均价时,有可能还未提交, # 所以只在第一次的时候查询,其他的情况用前一次计算的均价作为前一日均价 # is_first就是是否第一次需要查询的标识 # 前一日均值 previous_data = None while i < len_result: add_up += 1 # 如果切片的下标是元祖的最后一个元素,则退出,因为已经处理完毕 if (i + ma) > len_result: add_up -= 1 break temp_line_tuple = result[i:(i + ma)] # 如果前一交易日的数据为空,则去查询一次 if previous_data is None or len(previous_data) == 0: the_date = temp_line_tuple[ma - 1][0] # close_pre_avg, amount_pre_avg, vol_pre_avg, price_pre_avg previous_data = StockOneStopProcessor.dbService.get_previous_average_line( ma, security_code, the_date) # 返回值list [the_date, # close, close_avg, close_pre_avg, close_avg_chg, # amount, amount_avg, amount_pre_avg, amount_avg_chg, # vol, vol_avg, vol_pre_avg, vol_avg_chg, # price_avg, price_pre_avg, price_avg_chg, # amount_flow_chg, vol_flow_chg, close_ma_price_avg_chg] # print('the_date', the_date, 'previous_data', previous_data) list_data = StockOneStopProcessor.analysis_average_line( ma, temp_line_tuple, previous_data) """ 均线数据入库(3,5,10日等) """ upsert_sql = 'insert into tquant_stock_average_line (security_code, the_date, ' \ 'ma, ' \ 'close, close_avg, close_pre_avg, close_avg_chg, ' \ 'amount, amount_avg, amount_pre_avg, amount_avg_chg, ' \ 'vol, vol_avg, vol_pre_avg, vol_avg_chg, ' \ 'price_avg, price_pre_avg, price_avg_chg, ' \ 'amount_flow_chg, vol_flow_chg, close_ma_price_avg_chg) ' \ 'values ({security_code}, {the_date}, ' \ '{ma}, ' \ '{close}, {close_avg}, {close_pre_avg}, {close_avg_chg}, ' \ '{amount}, {amount_avg}, {amount_pre_avg}, {amount_avg_chg}, ' \ '{vol}, {vol_avg}, {vol_pre_avg}, {vol_avg_chg}, ' \ '{price_avg}, {price_pre_avg}, {price_avg_chg}, ' \ '{amount_flow_chg}, {vol_flow_chg}, {close_ma_price_avg_chg}) ' \ 'on duplicate key update ' \ 'close=values(close), close_avg=values(close_avg), close_pre_avg=values(close_pre_avg), close_avg_chg=values(close_avg_chg), ' \ 'amount=values(amount), amount_avg=values(amount_avg), amount_pre_avg=values(amount_pre_avg), amount_avg_chg=values(amount_avg_chg), ' \ 'vol=values(vol), vol_avg=values(vol_avg), vol_pre_avg=values(vol_pre_avg), vol_avg_chg=values(vol_avg_chg), ' \ 'price_avg=values(price_avg), price_pre_avg=values(price_pre_avg), price_avg_chg=values(price_avg_chg), ' \ 'amount_flow_chg=values(amount_flow_chg), vol_flow_chg=values(vol_flow_chg), close_ma_price_avg_chg=values(close_ma_price_avg_chg) ' upsert_sql = upsert_sql.format( security_code=Utils.quotes_surround(security_code), the_date=Utils.quotes_surround( list_data[0].strftime('%Y-%m-%d')), ma=ma, close=list_data[1], close_avg=list_data[2], close_pre_avg=list_data[3], close_avg_chg=list_data[4], amount=list_data[5], amount_avg=list_data[6], amount_pre_avg=list_data[7], amount_avg_chg=list_data[8], vol=list_data[9], vol_avg=list_data[10], vol_pre_avg=list_data[11], vol_avg_chg=list_data[12], price_avg=list_data[13], price_pre_avg=list_data[14], price_avg_chg=list_data[15], amount_flow_chg=list_data[16], vol_flow_chg=list_data[17], close_ma_price_avg_chg=list_data[18]) # print(upsert_sql) # 将本次的处理结果重新赋值到previous_data中 # close_pre_avg, amount_pre_avg, vol_pre_avg, price_pre_avg previous_data = [[ list_data[2], list_data[6], list_data[10], list_data[13] ]] # 批量(100)提交数据更新 if len(upsert_sql_list) == 1000: StockOneStopProcessor.dbService.insert_many( upsert_sql_list) process_line += '=' upsert_sql_list = [] upsert_sql_list.append(upsert_sql) if len_result == ma: progress = Utils.base_round_zero(1 * 100, 2) else: progress = Utils.base_round_zero( Utils.division_zero(add_up, (len_result - ma + 1)) * 100, 2) progress_log_list = ['StockOneStopProcessor'] progress_log_list.append( StockOneStopProcessor.get_method_name()) progress_log_list.append('ma') progress_log_list.append(ma) progress_log_list.append('security_code') progress_log_list.append(security_code) progress_log_list.append('progress') progress_log_list.append(add_up) progress_log_list.append((len_result - ma + 1)) progress_log_list.append(process_line) progress_log_list.append(progress) r.lpush(queue, progress_log_list) else: if upsert_sql is not None: upsert_sql_list.append(upsert_sql) i += 1 # 处理最后一批security_code的更新语句 if len(upsert_sql_list) > 0: StockOneStopProcessor.dbService.insert_many( upsert_sql_list) process_line += '=' if len_result == ma: progress = Utils.base_round_zero(1 * 100, 2) else: progress = Utils.base_round_zero( Utils.division_zero(add_up, (len_result - ma + 1)) * 100, 2) progress_log_list = ['StockOneStopProcessor'] progress_log_list.append( StockOneStopProcessor.get_method_name()) progress_log_list.append('ma') progress_log_list.append(ma) progress_log_list.append('security_code') progress_log_list.append(security_code) progress_log_list.append('progress') progress_log_list.append(add_up) progress_log_list.append((len_result - ma + 1)) progress_log_list.append(process_line) progress_log_list.append(progress) r.lpush(queue, progress_log_list) except Exception: exc_type, exc_value, exc_traceback = sys.exc_info() line_no = traceback.extract_stack()[-2][1] error_log_list = ['StockOneStopProcessor'] error_log_list.append(StockOneStopProcessor.get_method_name()) error_log_list.append('ma') error_log_list.append(ma) error_log_list.append('security_code') error_log_list.append(security_code) error_log_list.append('line_no') error_log_list.append(line_no) error_log_list.append(exc_type) error_log_list.append(exc_value) error_log_list.append(exc_traceback) r.lpush(queue, error_log_list, logging.ERROR)
def analysis_real_time_kline(self, day_kline, start_date): """ 解析单只股票的实时行情,并入库 :param day_kline: :param start_date: :return: """ try: if day_kline.empty == False: indexes_values = day_kline.index.values if indexes_values is None or len(indexes_values) == 0: log_list = [ Utils.get_now(), Utils.get_warn(), self.get_classs_name(), self.security_code, self.get_method_name(), '当日全部5分钟实时行情 开始时间', start_date, '【行情为空】' ] Utils.print_log(log_list) return the_date = None first_idx = None last_idx = indexes_values[len(indexes_values) - 1] for idx in indexes_values: idx_datetime = idx.astype('M8[ms]').astype('O') # idx_datetime = datetime.datetime.utcfromtimestamp(idx.astype('O') / 1e9) # 由于第三方接口返回的数据是最近1000个5分钟K,所以需要剔除不是今天的数据 if idx_datetime >= start_date: first_idx = idx day_kline = day_kline[idx:] the_date = idx_datetime the_date = Utils.format_date(the_date) break if the_date is not None: # 统计数据,包括min, max 等 day_kline_describe = day_kline.describe() open = Utils.base_round_zero( day_kline.at[first_idx, 'open'], 2) high = Utils.base_round_zero( day_kline_describe.at['max', 'high'], 2) low = Utils.base_round_zero( day_kline_describe.at['min', 'low'], 2) close = Utils.base_round_zero( day_kline.at[last_idx, 'close'], 2) # sum统计 day_kline_sum = day_kline.sum() amount_count = Utils.base_round_zero( day_kline_sum['amount'] * 100, 2) vol_count = Utils.base_round_zero( day_kline_sum['vol'] * 100, 2) dict_data = { 'security_code': Utils.quotes_surround(self.security_code), 'the_date': Utils.quotes_surround(the_date), 'amount': amount_count, 'vol': vol_count, 'open': open, 'high': high, 'low': low, 'close': close } self.dbService.upsert(dict_data, 'tquant_stock_history_quotation', ['security_code', 'the_date']) print('secuirty_code', self.security_code, '实时行情基础数据入库成功') self.calculate_last_10_day(the_date) except Exception: traceback.print_exc()
def processing_average_line_avg(self, ma): """ 股票均线数据涨跌幅平均数据处理方法 :param ma: 均线类型 :return: """ average_line_avg_max_the_date = self.dbService.get_average_line_avg_max_the_date(ma, self.security_code) decline_ma_the_date = self.dbService.get_average_line_avg_decline_max_the_date(ma, average_line_avg_max_the_date, self.security_code) log_list = [self.now(), self.info(), self.get_classs_name(), self.security_code] log_list.append(self.get_method_name()) log_list.append('ma') log_list.append(ma) log_list.append('average_line_avg_max_the_date') log_list.append(average_line_avg_max_the_date) log_list.append('decline_ma_the_date') log_list.append(decline_ma_the_date) self.print_log(log_list) result = self.dbService.get_average_line(ma, self.security_code, decline_ma_the_date) len_result = len(result) if len_result < ma: return try: if result is not None and len_result > 0: # 开始解析股票日K数据, the_date, close # 临时存储批量更新sql的列表 upsert_sql_list = [] # 需要处理的单只股票进度计数 add_up = 0 # 需要处理的单只股票进度打印字符 process_line = '=' # 循环处理security_code的股票日K数据 i = 0 while i < len_result: add_up += 1 # 如果切片的下标是元祖的最后一个元素,则退出,因为已经处理完毕 if (i + ma) > len_result: add_up -= 1 break temp_line_tuple = result[i:(i + ma)] # 返回值list_data list [the_date, # close_avg_chg_avg, amount_avg_chg_avg, vol_avg_chg_avg, # price_avg_chg_avg, amount_flow_chg_avg, vol_flow_chg_avg] list_data = self.analysis_average_line_avg(ma, temp_line_tuple) upsert_sql = self.upsert_average_line_avg.format(security_code=Utils.quotes_surround(self.security_code), the_date=Utils.quotes_surround(list_data[0].strftime('%Y-%m-%d')), ma=ma, close_avg_chg_avg=list_data[1], amount_avg_chg_avg=list_data[2], vol_avg_chg_avg=list_data[3], price_avg_chg_avg=list_data[4], amount_flow_chg_avg=list_data[5], vol_flow_chg_avg=list_data[6] ) # 批量(100)提交数据更新 if len(upsert_sql_list) == 200: self.dbService.insert_many(upsert_sql_list) process_line += '=' upsert_sql_list = [] upsert_sql_list.append(upsert_sql) if len_result == ma: progress = Utils.base_round(1 * 100, 2) else: progress = Utils.base_round(Utils.division_zero(add_up, (len_result - ma + 1)) * 100, 2) log_list = [self.now(), self.info(), self.get_classs_name(), self.security_code] log_list.append(self.get_method_name()) log_list.append('ma') log_list.append(ma) log_list.append('progress') log_list.append(add_up) log_list.append((len_result - ma + 1)) log_list.append(process_line) log_list.append(progress) self.print_log(log_list) else: if upsert_sql is not None: upsert_sql_list.append(upsert_sql) i += 1 # 处理最后一批security_code的更新语句 if len(upsert_sql_list) > 0: self.dbService.insert_many(upsert_sql_list) process_line += '=' if len_result == ma: progress = Utils.base_round(1 * 100, 2) else: progress = Utils.base_round(Utils.division_zero(add_up, (len_result - ma + 1)) * 100, 2) log_list = [self.now(), self.info(), self.get_classs_name(), self.security_code] log_list.append(self.get_method_name()) log_list.append('ma') log_list.append(ma) log_list.append('progress') log_list.append(add_up) log_list.append((len_result - ma + 1)) log_list.append(process_line) log_list.append(progress) self.print_log(log_list) except Exception: log_list = [self.now(), self.info(), self.get_classs_name(), self.security_code] log_list.append(self.get_method_name()) log_list.append('ma') log_list.append(ma) log_list.append(traceback.format_exc()) self.print_log(log_list)
def processing_day_kline(self): """ 股票日K数据处理,分全量还是增量 :return: """ try: recent_few_days = self.dbService.get_day_kline_recentdays(self.security_code) log_list = [self.now(), self.info(), self.get_classs_name(), self.security_code] log_list.append(self.get_method_name()) log_list.append('recent_few_days') log_list.append(recent_few_days) self.print_log(log_list) if recent_few_days is None: result = tt.get_all_daybar(self.security_code, 'qfq') elif recent_few_days > 0: result = tt.get_last_n_daybar(self.security_code, recent_few_days, 'qfq') else: return if result.empty == False: # 索引值为日期 indexes_values = result.index.values # 临时存储批量更新sql的列表 upsert_sql_list = [] # 需要处理的单只股票进度计数 add_up = 0 # 需要处理的单只股票进度打印字符 process_line = '=' # 循环处理security_code的股票日K数据 if indexes_values is not None: len_indexes = len(indexes_values) for idx in indexes_values: add_up += 1 # 解析股票日K数据(每行) # 解析每行的返回值格式为list [the_date, amount, vol, open, high, low, close] list_data = self.analysis_columns_day_kline(result, idx) if list_data is not None: upsert_sql = self.upsert_day_kline.format( security_code=Utils.quotes_surround(self.security_code), the_date=Utils.quotes_surround(list_data[0]), amount=list_data[1], vol=list_data[2], open=list_data[3], high=list_data[4], low=list_data[5], close=list_data[6] ) else: continue # 批量(100)提交数据更新 if len(upsert_sql_list) == 200: self.dbService.insert_many(upsert_sql_list) process_line += '=' upsert_sql_list = [] if upsert_sql is not None: upsert_sql_list.append(upsert_sql) progress = Utils.base_round(Utils.division_zero(add_up, len_indexes) * 100, 2) log_list = [self.now(), self.info(), self.get_classs_name(), self.security_code] log_list.append(self.get_method_name()) log_list.append('progress') log_list.append(add_up) log_list.append(len_indexes) log_list.append(process_line) log_list.append(str(progress) + '%') self.print_log(log_list) else: upsert_sql_list.append(upsert_sql) # 处理最后一批security_code的更新语句 if len(upsert_sql_list) > 0: self.dbService.insert_many(upsert_sql_list) process_line += '=' progress = Utils.base_round(Utils.division_zero(add_up, len(indexes_values)) * 100, 2) log_list = [self.now(), self.info(), self.get_classs_name(), self.security_code] log_list.append(self.get_method_name()) log_list.append('progress') log_list.append(add_up) log_list.append(len_indexes) log_list.append(process_line) log_list.append(progress) self.print_log(log_list) else: log_list = [self.now(), self.info(), self.get_classs_name(), self.security_code] log_list.append(self.get_method_name()) log_list.append('result`s indexes_values is None') self.print_log(log_list) else: log_list = [self.now(), self.info(), self.get_classs_name(), self.security_code] log_list.append(self.get_method_name()) log_list.append('tt.get_all_daybar or tt.get_last_n_daybar result is None') self.print_log(log_list) except Exception: log_list = [self.now(), self.info(), self.get_classs_name(), self.security_code] log_list.append(self.get_method_name()) log_list.append(traceback.format_exc()) self.print_log(log_list)
def analysis_real_time_kline(day_kline, start_date, security_code, r, queue): """ 解析单只股票的实时行情,并入库 :param day_kline: :param start_date: :return: """ if day_kline.empty == False: indexes_values = day_kline.index.values the_date = None high_max = None low_min = None open_first = None close_last = None total_amount = 0 total_vol = 0 if indexes_values is None or len(indexes_values) == 0: warn_log_list = ['StockOneStopProcessor'] warn_log_list.append(StockOneStopProcessor.get_method_name()) warn_log_list.append('security_code') warn_log_list.append(security_code) warn_log_list.append('start_date') warn_log_list.append(start_date) warn_log_list.append('tt.get_stock_bar(security_code, 1)') warn_log_list.append('indexes_values is None') r.lpush(queue, warn_log_list, logging.WARNING) return for idx in indexes_values: idx_datetime = datetime.datetime.utcfromtimestamp( idx.astype('O') / 1e9) if idx_datetime >= start_date: amount = day_kline.at[idx, 'amount'] if isinstance(amount, numpy.ndarray) and amount.size > 1: amount = amount.tolist()[0] amount = Utils.base_round(amount, 2) total_amount += amount vol = day_kline.at[idx, 'vol'] if isinstance(vol, numpy.ndarray) and vol.size > 1: vol = vol.tolist()[0] vol = Utils.base_round(vol, 2) total_vol += Utils.base_round(vol, 2) high = day_kline.at[idx, 'high'] if isinstance(high, numpy.ndarray) and high.size > 1: high = high.tolist()[0] high = Utils.base_round(high, 2) if high_max is None: high_max = high elif high > high_max: high_max = high low = day_kline.at[idx, 'low'] if isinstance(low, numpy.ndarray) and low.size > 1: low = low.tolist()[0] low = Utils.base_round(low, 2) if low_min is None: low_min = low elif low < low_min: low_min = low open = day_kline.at[idx, 'open'] if isinstance(open, numpy.ndarray) and open.size > 1: open = open.tolist()[0] open = Utils.base_round(open, 2) if open_first is None: open_first = open close = day_kline.at[idx, 'close'] if isinstance(close, numpy.ndarray) and close.size > 1: close = close.tolist()[0] close = Utils.base_round(close, 2) close_last = close the_date = (idx_datetime.strftime('%Y-%m-%d')) if the_date is not None: total_amount = total_amount * 100 total_vol = total_vol * 100 upsert_sql = StockOneStopProcessor.upsert_day_kline.format( security_code=Utils.quotes_surround(security_code), the_date=Utils.quotes_surround(the_date), amount=total_amount, vol=total_vol, open=open_first, high=high_max, low=low_min, close=close_last) StockOneStopProcessor.dbService.insert(upsert_sql)
def processing_day_kline_change_percent(security_code, r, queue): """ 股票日K数据涨跌幅处理方法 :return: """ day_kline_max_the_date = StockOneStopProcessor.dbService.get_day_kline_max_the_date( security_code) r.lpush(queue, [ 'StockOneStopProcessor', StockOneStopProcessor.get_method_name(), security_code, 'day_kline_max_the_date', day_kline_max_the_date ]) result = StockOneStopProcessor.dbService.get_stock_day_kline( security_code, day_kline_max_the_date) len_result = len(result) # print('result', result) if len_result == 0: return # 临时存储批量更新sql的列表 upsert_sql_list = [] # 需要处理的单只股票进度计数 add_up = 0 # 需要处理的单只股票进度打印字符 process_line = '' # 循环处理security_code的股票日K数据 i = 0 price_avg_pre = None while i < len_result: # 切片元组,每相连的2个一组 section_idx = i + 2 if section_idx > len_result: i += 1 break temp_kline_tuple = result[i:section_idx] # 返回值格式list [the_date, close1, close_chg, amount1, amount_chg, vol1, vol_chg, # price_avg, close_price_avg_chg, price_avg_chg] list_data = StockOneStopProcessor.analysis_day_kline_change_percent( temp_kline_tuple, price_avg_pre) # print(temp_kline_tuple) # print(price_avg_pre) if list_data is not None: """ 日K涨跌幅数据入库 """ upsert_sql = 'insert into tquant_stock_day_kline (security_code, the_date, ' \ 'close_pre, close_chg, ' \ 'amount_pre, amount_chg, ' \ 'vol_pre, vol_chg, ' \ 'price_avg, close_price_avg_chg, price_avg_chg) ' \ 'values ({security_code}, {the_date}, ' \ '{close_pre}, {close_chg},' \ '{amount_pre}, {amount_chg}, {vol_pre}, {vol_chg}, ' \ '{price_avg}, {close_price_avg_chg}, {price_avg_chg}) ' \ 'on duplicate key update ' \ 'close_pre=values(close_pre), close_chg=values(close_chg), ' \ 'amount_pre=values(amount_pre), amount_chg=values(amount_chg), ' \ 'vol_pre=values(vol_pre), vol_chg=values(vol_chg), ' \ 'price_avg=values(price_avg), close_price_avg_chg=values(close_price_avg_chg), price_avg_chg=values(price_avg_chg)' upsert_sql = upsert_sql.format( security_code=Utils.quotes_surround(security_code), the_date=Utils.quotes_surround( list_data[0].strftime('%Y-%m-%d')), close_pre=list_data[1], close_chg=list_data[2], amount_pre=list_data[3], amount_chg=list_data[4], vol_pre=list_data[5], vol_chg=list_data[6], price_avg=list_data[7], close_price_avg_chg=list_data[8], price_avg_chg=list_data[9]) price_avg_pre = list_data[7] else: upsert_sql = None # 批量(100)提交数据更新 if len(upsert_sql_list) == 1000: StockOneStopProcessor.dbService.insert_many(upsert_sql_list) process_line += '=' upsert_sql_list = [] if upsert_sql is not None: upsert_sql_list.append(upsert_sql) # 这个地方为什么要add_up + 1?因为第一条数据不会被处理,所以总数会少一条,所以在计算进度的时候要+1 progress = Utils.base_round( Utils.division_zero((add_up + 1), len_result) * 100, 2) progress_log_list = ['StockOneStopProcessor'] progress_log_list.append( StockOneStopProcessor.get_method_name()) progress_log_list.append('security_code') progress_log_list.append(security_code) progress_log_list.append('progress') progress_log_list.append(add_up + 1) progress_log_list.append(len_result) progress_log_list.append(process_line) progress_log_list.append(progress) r.lpush(queue, progress_log_list) else: if upsert_sql is not None: upsert_sql_list.append(upsert_sql) i += 1 add_up += 1 # 处理最后一批security_code的更新语句 if len(upsert_sql_list) > 0: StockOneStopProcessor.dbService.insert_many(upsert_sql_list) process_line += '=' progress = Utils.base_round( Utils.division_zero((add_up + 1), len_result) * 100, 2) progress_log_list = ['StockOneStopProcessor'] progress_log_list.append(StockOneStopProcessor.get_method_name()) progress_log_list.append('security_code') progress_log_list.append(security_code) progress_log_list.append('progress') progress_log_list.append(add_up + 1) progress_log_list.append(len_result) progress_log_list.append(process_line) progress_log_list.append(progress) r.lpush(queue, progress_log_list)
def processing_average_line_avg(ma, security_code, r, queue): """ 股票均线数据涨跌幅平均数据处理方法 :param ma: 均线类型 :return: """ average_line_avg_max_the_date = StockOneStopProcessor.dbService.get_average_line_avg_max_the_date( ma, security_code) decline_ma_the_date = StockOneStopProcessor.dbService.get_average_line_avg_decline_max_the_date( ma, average_line_avg_max_the_date) r.lpush(queue, [ 'StockOneStopProcessor', StockOneStopProcessor.get_method_name(), 'ma', ma, security_code, 'average_line_avg_max_the_date', average_line_avg_max_the_date, 'decline_ma_the_date', decline_ma_the_date ]) result = StockOneStopProcessor.dbService.get_average_line( ma, security_code, decline_ma_the_date) len_result = len(result) if len_result < ma: return try: if result is not None and len_result > 0: # 开始解析股票日K数据, the_date, close # 临时存储批量更新sql的列表 upsert_sql_list = [] # 需要处理的单只股票进度计数 add_up = 0 # 需要处理的单只股票进度打印字符 process_line = '=' # 循环处理security_code的股票日K数据 i = 0 while i < len_result: add_up += 1 # 如果切片的下标是元祖的最后一个元素,则退出,因为已经处理完毕 if (i + ma) > len_result: add_up -= 1 break temp_line_tuple = result[i:(i + ma)] # 返回值list_data list [the_date, # close_avg_chg_avg, amount_avg_chg_avg, vol_avg_chg_avg, # price_avg_chg_avg, amount_flow_chg_avg, vol_flow_chg_avg] list_data = StockOneStopProcessor.analysis_average_line_avg( ma, temp_line_tuple) upsert_sql = StockOneStopProcessor.upsert_average_line_avg.format( security_code=Utils.quotes_surround(security_code), the_date=Utils.quotes_surround( list_data[0].strftime('%Y-%m-%d')), ma=ma, close_avg_chg_avg=list_data[1], amount_avg_chg_avg=list_data[2], vol_avg_chg_avg=list_data[3], price_avg_chg_avg=list_data[4], amount_flow_chg_avg=list_data[5], vol_flow_chg_avg=list_data[6]) # print(upsert_sql) # 批量(100)提交数据更新 if len(upsert_sql_list) == 1000: StockOneStopProcessor.dbService.insert_many( upsert_sql_list) process_line += '=' upsert_sql_list = [] upsert_sql_list.append(upsert_sql) if len_result == ma: progress = Utils.base_round(1 * 100, 2) else: progress = Utils.base_round( Utils.division_zero(add_up, (len_result - ma + 1)) * 100, 2) progress_log_list = ['StockOneStopProcessor'] progress_log_list.append( StockOneStopProcessor.get_method_name()) progress_log_list.append('ma') progress_log_list.append(ma) progress_log_list.append('security_code') progress_log_list.append(security_code) progress_log_list.append('progress') progress_log_list.append(add_up) progress_log_list.append((len_result - ma + 1)) progress_log_list.append(process_line) progress_log_list.append(progress) r.lpush(queue, progress_log_list) else: if upsert_sql is not None: upsert_sql_list.append(upsert_sql) i += 1 # 处理最后一批security_code的更新语句 if len(upsert_sql_list) > 0: StockOneStopProcessor.dbService.insert_many( upsert_sql_list) process_line += '=' if len_result == ma: progress = Utils.base_round(1 * 100, 2) else: progress = Utils.base_round( Utils.division_zero(add_up, (len_result - ma + 1)) * 100, 2) progress_log_list = ['StockOneStopProcessor'] progress_log_list.append( StockOneStopProcessor.get_method_name()) progress_log_list.append('ma') progress_log_list.append(ma) progress_log_list.append('security_code') progress_log_list.append(security_code) progress_log_list.append('progress') progress_log_list.append(add_up) progress_log_list.append((len_result - ma + 1)) progress_log_list.append(process_line) progress_log_list.append(progress) r.lpush(queue, progress_log_list) except Exception: exc_type, exc_value, exc_traceback = sys.exc_info() line_no = traceback.extract_stack()[-2][1] error_log_list = ['StockOneStopProcessor'] error_log_list.append(StockOneStopProcessor.get_method_name()) error_log_list.append('ma') error_log_list.append(ma) error_log_list.append('security_code') error_log_list.append(security_code) error_log_list.append('line_no') error_log_list.append(line_no) error_log_list.append(exc_type) error_log_list.append(exc_value) error_log_list.append(exc_traceback) r.lpush(queue, error_log_list, logging.ERROR)
def processing_day_kline(security_code, r, queue): """ 股票日K数据处理,分全量还是增量 :return: """ try: recent_few_days = StockOneStopProcessor.dbService.get_day_kline_recentdays( security_code) r.lpush(queue, [ 'StockOneStopProcessor', StockOneStopProcessor.get_method_name(), security_code, 'recent_few_days', recent_few_days ]) if recent_few_days is not None and recent_few_days > 0: result = tt.get_last_n_daybar(security_code, recent_few_days, 'qfq') else: result = tt.get_all_daybar(security_code, 'qfq') # r.lpush(queue, ['result', result]) if result.empty == False: # 索引值为日期 indexes_values = result.index.values # 临时存储批量更新sql的列表 upsert_sql_list = [] # 需要处理的单只股票进度计数 add_up = 0 # 需要处理的单只股票进度打印字符 process_line = '=' # 循环处理security_code的股票日K数据 if indexes_values is not None: len_indexes = len(indexes_values) for idx in indexes_values: add_up += 1 # 解析股票日K数据(每行) # 解析每行的返回值格式为list [the_date, amount, vol, open, high, low, close] list_data = StockOneStopProcessor.analysis_columns_day_kline( result, idx) if list_data is not None: upsert_sql = StockOneStopProcessor.upsert_day_kline.format( security_code=Utils.quotes_surround( security_code), the_date=Utils.quotes_surround(list_data[0]), amount=list_data[1], vol=list_data[2], open=list_data[3], high=list_data[4], low=list_data[5], close=list_data[6]) else: continue # 批量(100)提交数据更新 if len(upsert_sql_list) == 3000: StockOneStopProcessor.dbService.insert_many( upsert_sql_list) process_line += '=' upsert_sql_list = [] if upsert_sql is not None: upsert_sql_list.append(upsert_sql) progress = Utils.base_round( Utils.division_zero(add_up, len_indexes) * 100, 2) progress_log_list = ['StockOneStopProcessor'] progress_log_list.append( StockOneStopProcessor.get_method_name()) progress_log_list.append('security_code') progress_log_list.append(security_code) progress_log_list.append('progress') progress_log_list.append(add_up) progress_log_list.append(len_indexes) progress_log_list.append(process_line) progress_log_list.append(str(progress) + '%') r.lpush(queue, progress_log_list) else: upsert_sql_list.append(upsert_sql) # 处理最后一批security_code的更新语句 if len(upsert_sql_list) > 0: StockOneStopProcessor.dbService.insert_many( upsert_sql_list) process_line += '=' progress = Utils.base_round( Utils.division_zero(add_up, len(indexes_values)) * 100, 2) progress_log_list = ['StockOneStopProcessor'] progress_log_list.append( StockOneStopProcessor.get_method_name()) progress_log_list.append('security_code') progress_log_list.append(security_code) progress_log_list.append('progress') progress_log_list.append(add_up) progress_log_list.append(len_indexes) progress_log_list.append(process_line) progress_log_list.append(progress) r.lpush(queue, progress_log_list) else: warn_log_list = ['StockOneStopProcessor'] warn_log_list.append( StockOneStopProcessor.get_method_name()) warn_log_list.append('security_code') warn_log_list.append(security_code) warn_log_list.append('result`s indexes_values is None') r.lpush(queue, warn_log_list, logging.WARNING) else: warn_log_list = ['StockOneStopProcessor'] warn_log_list.append(StockOneStopProcessor.get_method_name()) warn_log_list.append('security_code') warn_log_list.append(security_code) warn_log_list.append( 'tt.get_all_daybar or tt.get_last_n_daybar result is None') r.lpush(queue, warn_log_list, logging.WARNING) except Exception: exc_type, exc_value, exc_traceback = sys.exc_info() line_no = traceback.extract_stack()[-2][1] error_log_list = ['StockOneStopProcessor'] error_log_list.append(StockOneStopProcessor.get_method_name()) error_log_list.append('security_code') error_log_list.append(security_code) error_log_list.append('line_no') error_log_list.append(line_no) error_log_list.append(exc_type) error_log_list.append(exc_value) error_log_list.append(exc_traceback) r.lpush(queue, error_log_list, logging.ERROR)