def update_data(df):
s = pub_uti_a.save()
for i in range(len(df)):
sql = "update bankuai_day_data set ranks = {} where bk_id = '{}' ".format(df.loc[i,'ranks'],df.loc[i,'bk_id'])
# print('sql:',sql)
s.add_sql(sql)
s.commit()
def main(num, start_t, end_t):
num = str(num)
if start_t != None and end_t != None:
sql = "SELECT stock_id,stock_name,trade_date,open_price,close_price,high_price,low_price,increase FROM stock_trade_data \
where trade_date >= '{0}' and trade_date <= '{1}' and stock_id like '%{2}'".format(
start_t, end_t, num)
else:
sql = "SELECT stock_id,stock_name,trade_date,open_price,close_price,high_price,low_price,increase " \
"FROM stock_trade_data where stock_id like '%{0}' ".format(num)
df = pub_uti_a.creat_df(sql, ascending=True)
id_set = set(df['stock_id'].to_list())
s = pub_uti_a.save()
for id in id_set:
single_df = df[df.stock_id == id]
single_df.reset_index(drop=True, inplace=True)
print('single_df:', single_df)
single_df = deal_df_data(single_df)
zhuang_date, zhuang_grade, yidong, zhuang_long, max_avg_rate, lasheng_flag, lastest_target = compt_core(
single_df)
insert_sql = "insert into com_zhuang(stock_id,stock_name,zhuang_grade,zhuang_section,yidong,zhuang_long,max_avg_rate,lasheng_flag,lastest_target) " \
"values('{0}','{1}','{2}',\"{3}\",\"{4}\",'{5}','{6}','{7}','{8}') " \
"ON DUPLICATE KEY UPDATE stock_id='{0}',stock_name='{1}',zhuang_grade='{2}',zhuang_section=\"{3}\"," \
"yidong=\"{4}\",zhuang_long = '{5}' ,max_avg_rate = '{6}',lasheng_flag='{7}',lastest_target='{8}' " \
"".format(id, single_df.loc[0,'stock_name'], zhuang_grade, zhuang_date, yidong, zhuang_long, max_avg_rate, lasheng_flag ,lastest_target)
s.add_sql(insert_sql)
s.commit()
def init_buffer(self):
self.creat_time()
self.clean_tab()
self.select_info()
self.save = pub_uti_a.save()
for id in self.id_set:
self.init_stock(id)
self.save.commit()
def save_to_mysql(date=None):
if date == None:
date = datetime.datetime.now().strftime("%Y-%m-%d")
all_market_json = r.hgetall('day_market')
sv = pub_uti_a.save()
for id in all_market_json:
sql = "INSERT INTO miu_trade_date (stock_id,trade_date,data) values ('{0}','{1}','{2}')".format(
id, date, all_market_json[id])
sv.add_sql(sql)
sv.commit()
def com_lastest_point():
sql = "select stock_id,zhuang_section from com_zhuang where zhuang_grade > 0"
df = pub_uti_a.creat_df(sql)
s = pub_uti_a.save()
def map(raw):
zhuang_section = eval(raw['zhuang_section'])
if len(zhuang_section) == 0:
return raw
sql = "update com_zhuang set lastest_target= '{0}' where stock_id ='{1}'".format(
zhuang_section[0][0], raw['stock_id'])
s.add_sql(sql)
return raw
df.apply(map, axis=1)
s.commit()
def init_buffer(self):
time1 = datetime.datetime.now()
self.creat_time()
# print('创建时间耗时：',datetime.datetime.now() - time1)
time2 = datetime.datetime.now()
self.clean_tab()
# print('清除数据耗时：', datetime.datetime.now() - time2)
time3 = datetime.datetime.now()
self.select_info()
# print('查询数据耗时：', datetime.datetime.now() - time3)
time4 = datetime.datetime.now()
self.save = pub_uti_a.save()
for id in self.id_set:
# time5 = datetime.datetime.now()
self.init_stock(id)
# print('单条stock耗时：', datetime.datetime.now() - time5)
self.save.commit()
def com_volume_signal(date=None, long=120, avg_roll=10, signal_threshold=2):
if date == None:
sql = "select DATE_FORMAT(max(trade_date),'%Y-%m-%d') from stock_trade_data"
date = pub_uti_a.select_from_db(sql)[0][0]
print('date:', date)
start_date = datetime.datetime.strftime(
(datetime.datetime.strptime(date[0:10], '%Y-%m-%d') -
datetime.timedelta(days=long)), '%Y-%m-%d')
trade_sql = "select T.stock_id,T.trade_date,T.turnover_rate " \
" from (select stock_id from com_zhuang where lastest_target>= '{0}') Z " \
"LEFT JOIN stock_trade_data T " \
"ON Z.stock_id = T.stock_id " \
"WHERE T.trade_date >= '{0}' and T.trade_date<= '{1}'".format(start_date,date)
df = pub_uti_a.creat_df(trade_sql, ascending=True)
id_set = set(df['stock_id'].to_list())
volume_signal_map = {}
clean_sql = "delete from zhuang_day_grade where com_date = '{}'".format(
date)
pub_uti_a.commit_to_db(clean_sql)
s = pub_uti_a.save()
for id in id_set:
single_df = df[df.stock_id == id]
single_df.reset_index(drop=True, inplace=True)
single_df['avg'] = single_df['turnover_rate'].rolling(avg_roll).mean()
single_df['avg'] = single_df['avg'].shift(1)
single_df['avg'].fillna(100, inplace=True)
single_df[
'volume_signal'] = single_df['turnover_rate'] / single_df['avg']
# print('single_df', single_df)
index_list = single_df[
single_df['volume_signal'] >= signal_threshold].index.to_list()
print('index_list:', index_list)
if len(index_list) != 0 and index_list[0] >= (len(single_df) - 3):
trade_code = re.sub('-', '', date) + id
grade = 50
sql = "insert into zhuang_day_grade (trade_code,com_date,stock_id,grade) " \
"VALUES ('{0}','{1}','{2}',{3})".format(trade_code,date,id,grade)
print('sql:', sql)
s.add_sql(sql)
volume_signal_map[id] = grade
s.commit()
print('volume_signal_map:', volume_signal_map)
def comput_increase(self):
sql = "select trade_code,trade_date,stock_id,stock_name,close_price from stock_trade_data " \
"where trade_date >= '{}' and trade_date <= '{}'".format(self.start_date,self.end_date)
df = pub_uti_a.creat_df(sql, ascending=True)
id_set = set(df['stock_id'].to_list())
s = pub_uti_a.save()
count = 0
for id in id_set:
print('id:', id, count)
single_df = df[df.stock_id == id]
single_df['pre_close'] = single_df['close_price'].shift(1)
single_df.dropna(inplace=True)
single_df.reset_index(inplace=True, drop=True)
print('singel index:', single_df.index)
single_df['increase'] = (
single_df['close_price'] / single_df['pre_close'] - 1) * 100
for idx, raw in single_df.iterrows():
sql = "update stock_trade_data set increase={} where trade_code = '{}'".format(
raw['increase'], raw['trade_code'])
print(sql)
s.add_sql(sql)
count += 1
s.commit()
def save_data(self):
s = pub_uti_a.save()
for day_data in self.kline_list:
day_data_list = day_data.split(',')
if len(day_data_list) ==0:
logging.error('when split daily kline has error:{}'.format(day_data_list))
print('when split daily kline has error:{}'.format(day_data_list))
return 0
trade_date = day_data_list[0]
open_price = day_data_list[1]
close_price = day_data_list[2]
high_price = day_data_list[3]
low_price = day_data_list[4]
trade_amount = day_data_list[5]
#验证数据
if self.circulation_value == '-':
logging.warning('stock_id:{} circulation_value is "-"'.format(self.stock_id))
return 0
if trade_amount == '-':
logging.warning('stock_id:{} trade_amount is "-"'.format(self.stock_id))
return 0
trade_code = re.sub('-','',trade_date) + self.stock_id
turnover_rate = float(trade_amount) / float(self.circulation_value) * 10000
increase = 10000 #填充
P_E = 9999 #填充
P_B = 9999 #填充
trade_money = 9999 #填充
sql="insert into stock_trade_data(trade_code,stock_name,stock_id,trade_date,close_price,increase," \
"open_price,turnover_rate,P_E,P_B,high_price,low_price,trade_amount,trade_money) " \
"values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}')" \
"ON DUPLICATE KEY UPDATE trade_code='{0}',stock_name='{1}',stock_id='{2}',trade_date='{3}'," \
"close_price='{4}',increase='{5}',open_price='{6}',turnover_rate='{7}'," \
"P_E='{8}',P_B='{9}',high_price='{10}',low_price='{11}',trade_amount='{12}',trade_money='{13}'" \
.format(trade_code,self.stock_name,self.stock_id,trade_date,close_price,increase,open_price,
turnover_rate,P_E,P_B,high_price,low_price,trade_amount,trade_money)
s.add_sql(sql)
s.commit()
def __init__(self):
self.save = pub_uti_a.save()