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)
