import multiprocessing as mp
import pandas as pd
import pymysql
from sqlalchemy import create_engine
from datetime import datetime as dt
import datetime
# auth('18616891214', 'Ea?*7f68nD.dafcW34d!')
def hlfx(stocks,fre,table_list):
engine = create_engine('mysql+pymysql://root:r6kEwqWU9!v3@localhost:3307/stocks?charset=utf8')
engine2 = create_engine('mysql+pymysql://root:r6kEwqWU9!v3@localhost:3307/hlfx?charset=utf8')
for stock in stocks:
# print(stock)
if ('stk%s_%s' % (stock, fre)) in table_list:
# 有历史数据
index_len = pd.read_sql_table('stk%s_%s' % (stock, fre), con=engine2).iloc[-1, 0]
# 注意修改time delta
startdate = pd.read_sql_table('stk%s_%s' % (stock, fre), con=engine2).iloc[-1, 1]
# startdate = pd.read_sql_table('stk%s_%s' % (stock, fre), con=engine2).iloc[-1, 1] + datetime.timedelta(minutes= 5)
get_price = pd.read_sql_query(
'select date,open,close,high,low,volume,money from `stk%s_%s`' % (stock, fre), engine)
get_price = get_price.loc[get_price['date'] > startdate]
df_day = pd.read_sql_query(
'select date,open,close,high,low,volume,money,HL from `stk%s_%s`' % (stock, fre), engine2)
if index_len > 2:
# 先处理去包含
for i in get_price.index:
# 不包含
if (df_day.iloc[-1, 3] > get_price.loc[i, 'high']
and df_day.iloc[-1, 4] > get_price.loc[i, 'low']) \
or (df_day.iloc[-1, 3] < get_price.loc[i, 'high']
and df_day.iloc[-1, 4] < get_price.loc[i, 'low']):
df_day = pd.concat([df_day, get_price.loc[[i]]], ignore_index=True)
# print(df_day)
# 包含
else:
# (new_df.iloc[-1,3]>=df_day.loc[i,'high'] and new_df.iloc[-1,4]<= df_day.loc[i,'low']):
# 左高，下降
if df_day.iloc[-2, 3] > df_day.iloc[-1, 3]:
df_day.iloc[-1, 3] = min(df_day.iloc[-1, 3], get_price.loc[i, 'high'])
df_day.iloc[-1, 4] = min(df_day.iloc[-1, 4], get_price.loc[i, 'low'])
else:
# 右高，上升
df_day.iloc[-1, 3] = max(df_day.iloc[-1, 3], get_price.loc[i, 'high'])
df_day.iloc[-1, 4] = max(df_day.iloc[-1, 4], get_price.loc[i, 'low'])
# 寻找顶底分型
if len(df_day.index) > 2:
# 寻找顶底分型
for x in range(index_len, len(df_day.index)):
m = x - 1
# 底
if ((df_day.loc[x, 'high'] > df_day.loc[x - 1, 'high']) and (
df_day.loc[x - 2, 'high'] > df_day.loc[x - 1, 'high'])):
# if ((stk.df_day.loc[i-2, 'date'] != stk.fxdf.iloc[-1,0]) and (stk.df_day.loc[i-3,'date'] != stk.fxdf.iloc[-1,0]) and (stk.df_day.loc[i-1,'date'] != stk.fxdf.iloc[-1,0])):
# stk.fxdf = pd.concat([stk.fxdf, stk.df_day.iloc[[i]]], ignore_index=True)
df_day.loc[x, 'HL'] = 'L*'
while m:
if df_day.loc[m, 'HL'] == 'H':
if (x - m) > 3:
df_day.loc[x, 'HL'] = 'L'
if x == len(df_day.index) - 1:
# print(stock, '$$$$$$$', '\n', df_day.loc[x, 'date'], '买买买买买！！')
pass
break
elif (df_day.loc[m, 'HL'] == 'L'):
if df_day.loc[x - 1, 'low'] < df_day.loc[m - 1, 'low']:
# 前一个为底，且中间存在不包含 or 更低的底
df_day.loc[x, 'HL'] = 'L'
if x == len(df_day.index) - 1:
pass
# print(stock, '$$$$$$$', '\n', df_day.loc[x, 'date'],
# '中继后的底————买吗？！')
break
else:
break
m = m - 1
if m == 0:
df_day.loc[x, 'HL'] = 'L'
# 顶
elif ((df_day.loc[x, 'high'] < df_day.loc[x - 1, 'high']) and (
df_day.loc[x - 2, 'high'] < df_day.loc[x - 1, 'high'])):
# if ((stk.df_day.loc[i-2, 'date'] != stk.fxdf.iloc[-1,0]) and (stk.df_day.loc[i-3,'date'] != stk.fxdf.iloc[-1,0]) and (stk.df_day.loc[i-1,'date'] != stk.fxdf.iloc[-1,0])):
# stk.fxdf = pd.concat([stk.fxdf, stk.df_day.iloc[[i]]], ignore_index=True)
df_day.loc[x, 'HL'] = 'H*'
while m:
if df_day.loc[m, 'HL'] == 'L':
if x - m > 3:
df_day.loc[x, 'HL'] = 'H'
if x == len(df_day.index) - 1:
# print(stock, '!!!!!!!', '\n', '卖卖卖卖卖卖卖!')
pass
break
elif (df_day.loc[m, 'HL'] == 'H'):
if df_day.loc[x - 1, 'high'] > df_day.loc[m - 1, 'high']:
# 前一个为顶，且中间存在不包含 or 更高的顶
df_day.loc[x, 'HL'] = 'H'
if x == len(df_day.index) - 1:
pass
# print(stock, '/\/\/\/\/\/\/', '一顶更有一顶高！')
break
break
m = m - 1
if m == 0:
df_day.loc[x, 'HL'] = 'H'
else:
df_day.loc[x, 'HL'] = '-'
# 更新数据库
df_day[index_len + 1:].to_sql('stk%s_%s' % (stock, fre), con=engine2, index=True,
if_exists='append')
else:
df_day = pd.concat([df_day, get_price], ignore_index=True)
df_day[index_len + 1:].to_sql('stk%s_%s' % (stock, fre), con=engine2, index=True,
if_exists='append')
else:
# 没有历史数据表
df_day = pd.DataFrame(columns=('date', 'open', 'close', 'high', 'low', 'volume', 'money', 'HL'))
get_price = pd.read_sql_query(
'select date,open,close,high,low,volume,money from `stk%s_%s`' % (stock, fre), engine)
# 先处理去包含
for i in get_price.index:
if i == 0 or i == 1:
df_day = pd.concat([df_day, get_price.iloc[[i]]], ignore_index=True)
# 不包含
elif (df_day.iloc[-1, 3] > get_price.loc[i, 'high']
and df_day.iloc[-1, 4] > get_price.loc[i, 'low']) \
or (df_day.iloc[-1, 3] < get_price.loc[i, 'high']
and df_day.iloc[-1, 4] < get_price.loc[i, 'low']):
df_day = pd.concat([df_day, get_price.loc[[i]]], ignore_index=True)
# 包含
else:
# 左高，下降
if df_day.iloc[-2, 3] > df_day.iloc[-1, 3]:
df_day.iloc[-1, 3] = min(df_day.iloc[-1, 3], get_price.loc[i, 'high'])
df_day.iloc[-1, 4] = min(df_day.iloc[-1, 4], get_price.loc[i, 'low'])
else:
# 右高，上升
df_day.iloc[-1, 3] = max(df_day.iloc[-1, 3], get_price.loc[i, 'high'])
df_day.iloc[-1, 4] = max(df_day.iloc[-1, 4], get_price.loc[i, 'low'])
if len(df_day.index) > 2:
# 寻找顶底分型
for x in range(2, len(df_day.index)):
m = x - 1
# 底
if ((df_day.loc[x, 'high'] > df_day.loc[x - 1, 'high']) and (
df_day.loc[x - 2, 'high'] > df_day.loc[x - 1, 'high'])):
# if ((stk.df_day.loc[i-2, 'date'] != stk.fxdf.iloc[-1,0]) and (stk.df_day.loc[i-3,'date'] != stk.fxdf.iloc[-1,0]) and (stk.df_day.loc[i-1,'date'] != stk.fxdf.iloc[-1,0])):
# stk.fxdf = pd.concat([stk.fxdf, stk.df_day.iloc[[i]]], ignore_index=True)
df_day.loc[x, 'HL'] = 'L*'
while m:
if df_day.loc[m, 'HL'] == 'H':
if (x - m) > 3:
df_day.loc[x, 'HL'] = 'L'
if x == len(df_day.index) - 1:
pass
# print(stock, '$$$$$$$', '\n', df_day.loc[x, 'date'], '买买买买买！！')
break
elif (df_day.loc[m, 'HL'] == 'L'):
if df_day.loc[x - 1, 'low'] < df_day.loc[m - 1, 'low']:
# 前一个为底，且中间存在不包含 or 更低的底
df_day.loc[x, 'HL'] = 'L'
if x == len(df_day.index) - 1:
pass
# print(stock, '$$$$$$$', '\n', df_day.loc[x, 'date'], '中继后的底————买吗？！')
break
else:
break
m = m - 1
if m == 0:
df_day.loc[x, 'HL'] = 'L'
# 顶
elif ((df_day.loc[x, 'high'] < df_day.loc[x - 1, 'high']) and (
df_day.loc[x - 2, 'high'] < df_day.loc[x - 1, 'high'])):
# if ((stk.df_day.loc[i-2, 'date'] != stk.fxdf.iloc[-1,0]) and (stk.df_day.loc[i-3,'date'] != stk.fxdf.iloc[-1,0]) and (stk.df_day.loc[i-1,'date'] != stk.fxdf.iloc[-1,0])):
# stk.fxdf = pd.concat([stk.fxdf, stk.df_day.iloc[[i]]], ignore_index=True)
df_day.loc[x, 'HL'] = 'H*'
while m:
if df_day.loc[m, 'HL'] == 'L':
if x - m > 3:
df_day.loc[x, 'HL'] = 'H'
if x == len(df_day.index) - 1:
# print(stock, '!!!!!!!', '\n', '卖卖卖卖卖卖卖!')
pass
break
elif (df_day.loc[m, 'HL'] == 'H'):
if df_day.loc[x - 1, 'high'] > df_day.loc[m - 1, 'high']:
# 前一个为顶，且中间存在不包含 or 更高的顶
df_day.loc[x, 'HL'] = 'H'
if x == len(df_day.index) - 1:
pass
# print(stock, '/\/\/\/\/\/\/', '一顶更有一顶高！')
break
break
m = m - 1
if m == 0:
df_day.loc[x, 'HL'] = 'H'
else:
df_day.loc[x, 'HL'] = '-'
# 更新数据库
df_day.to_sql('stk%s_%s' % (stock, fre), con=engine2, index=True, if_exists='append')
if __name__ == '__main__':
engine_stocks_list = create_engine('mysql+pymysql://root:r6kEwqWU9!v3@localhost:3307/hlfx_pool?charset=utf8')
# stocks = list(get_all_securities(['stock'], date=dt.today().strftime('%Y-%m-%d')).index)
stocks = pd.read_sql_query(
'select securities from stocks_list', engine_stocks_list)
stocks = stocks.iloc[-1, 0]
stocks = stocks.split(",")
print(len(stocks), type(stocks), stocks)
# stocks = stocks[0:1000]
start = dt.now()
# 确定级别
# 注意修改time delta
# fre = '30m'
for fre in ['1d', '30m']:
start = dt.now()
print(fre)
# 连接数据库
db = pymysql.connect(host='localhost',
user='root',
port=3307,
password='r6kEwqWU9!v3',
database='hlfx')
cursor = db.cursor()
cursor.execute("show tables like '%%%s%%' " % fre)
table_list = [tuple[0] for tuple in cursor.fetchall()]
print('取得 table_list %s' % fre)
step = 800
mp_list = []
print(len(stocks))
for i in range(0, len(stocks), step):
p = mp.Process(target=hlfx, args=(stocks[i:i + step], fre, table_list, ))
mp_list.append(p)
p.start()
for processing in mp_list:
processing.join()
# db.close()
end = dt.now()
print('总时长：', (end - start).seconds)