stock/updata_qbh_hlfx.py

from jqdatasdk import *
import pandas as pd
import pymysql
from sqlalchemy import create_engine
import threading
from datetime import datetime as dt
import datetime

# auth('18616891214', 'Ea?*7f68nD.dafcW34d!')

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:1]

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)

    stk = locals()
    thd = threading.local()

    def hlfx(stocks, engine, engine2):
        for thd.stock in stocks:
            print(thd.stock)
            if ('stk%s_%s' % (thd.stock, fre)) in table_list:
                # 有历史数据
                index_len = pd.read_sql_table('stk%s_%s' % (thd.stock, fre), con=engine2).iloc[-1, 0]
                if index_len > 2:
                    # 注意修改time delta
                    startdate = pd.read_sql_table('stk%s_%s' % (thd.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)
                    thd.get_price = pd.read_sql_query(
                        'select date,open,close,high,low,volume,money from `stk%s_%s`' % (thd.stock, fre), engine)
                    thd.get_price = thd.get_price.loc[thd.get_price['date'] > startdate]
                    thd.df_day = pd.read_sql_query(
                        'select date,open,close,high,low,volume,money,HL from `stk%s_%s`' % (thd.stock, fre), engine2)
                    # 先处理去包含
                    for i in thd.get_price.index:
                        # 不包含
                        if (thd.df_day.iloc[-1, 3] > thd.get_price.loc[i, 'high']
                            and thd.df_day.iloc[-1, 4] > thd.get_price.loc[i, 'low']) \
                                or (thd.df_day.iloc[-1, 3] < thd.get_price.loc[i, 'high']
                                    and thd.df_day.iloc[-1, 4] < thd.get_price.loc[i, 'low']):
                            thd.df_day = pd.concat([thd.df_day, thd.get_price.loc[[i]]], ignore_index=True)
                            # print(thd.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 thd.df_day.iloc[-2, 3] > thd.df_day.iloc[-1, 3]:
                                thd.df_day.iloc[-1, 3] = min(thd.df_day.iloc[-1, 3], thd.get_price.loc[i, 'high'])
                                thd.df_day.iloc[-1, 4] = min(thd.df_day.iloc[-1, 4], thd.get_price.loc[i, 'low'])
                            else:
                                # 右高，上升
                                thd.df_day.iloc[-1, 3] = max(thd.df_day.iloc[-1, 3], thd.get_price.loc[i, 'high'])
                                thd.df_day.iloc[-1, 4] = max(thd.df_day.iloc[-1, 4], thd.get_price.loc[i, 'low'])
                                # 寻找顶底分型
                    if len(thd.df_day.index) > 2:
                        # 寻找顶底分型
                        for x in range(index_len, len(thd.df_day.index)):
                            m = x - 1
                            # 底
                            if ((thd.df_day.loc[x, 'high'] > thd.df_day.loc[x - 1, 'high']) and (
                                    thd.df_day.loc[x - 2, 'high'] > thd.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)
                                thd.df_day.loc[x, 'HL'] = 'L*'
                                while m:
                                    if thd.df_day.loc[m, 'HL'] == 'H':
                                        if (x - m) > 3:
                                            thd.df_day.loc[x, 'HL'] = 'L'
                                            if x == len(thd.df_day.index) - 1:
                                                print(thd.stock, '$$$$$$$', '\n', thd.df_day.loc[x, 'date'], '买买买买买！！')
                                        break
                                    elif (thd.df_day.loc[m, 'HL'] == 'L'):
                                        if thd.df_day.loc[x - 1, 'low'] < thd.df_day.loc[m - 1, 'low']:
                                            # 前一个为底，且中间存在不包含 or 更低的底
                                            thd.df_day.loc[x, 'HL'] = 'L'
                                            if x == len(thd.df_day.index) - 1:
                                                # pass
                                                print(thd.stock, '$$$$$$$', '\n', thd.df_day.loc[x, 'date'], '中继后的底————买吗？！')
                                            break
                                        else:
                                            break
                                    m = m - 1
                                    if m == 0:
                                        thd.df_day.loc[x, 'HL'] = 'L'
                            # 顶
                            elif ((thd.df_day.loc[x, 'high'] < thd.df_day.loc[x - 1, 'high']) and (
                                    thd.df_day.loc[x - 2, 'high'] < thd.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)
                                thd.df_day.loc[x, 'HL'] = 'H*'
                                while m:
                                    if thd.df_day.loc[m, 'HL'] == 'L':
                                        if x - m > 3:
                                            thd.df_day.loc[x, 'HL'] = 'H'
                                            if x == len(thd.df_day.index) - 1:
                                                # print(stock, '!!!!!!!', '\n', '卖卖卖卖卖卖卖!')
                                                pass
                                        break
                                    elif (thd.df_day.loc[m, 'HL'] == 'H'):
                                        if thd.df_day.loc[x - 1, 'high'] > thd.df_day.loc[m - 1, 'high']:
                                            # 前一个为顶，且中间存在不包含 or 更高的顶
                                            thd.df_day.loc[x, 'HL'] = 'H'
                                            if x == len(thd.df_day.index) - 1:
                                                pass
                                                # print(stock, '/\/\/\/\/\/\/', '一顶更有一顶高！')
                                            break
                                        break
                                    m = m - 1
                                    if m == 0:
                                        thd.df_day.loc[x, 'HL'] = 'H'
                            else:
                                thd.df_day.loc[x, 'HL'] = '-'

                    # 更新数据库
                    thd.df_day[index_len + 1:].to_sql('stk%s_%s' % (thd.stock, fre), con=engine2, index=True, if_exists='append')
            else:
                # 没有历史数据表
                thd.df_day = pd.DataFrame(columns=('date', 'open', 'close', 'high', 'low', 'volume', 'money', 'HL'))
                thd.get_price = pd.read_sql_query(
                    'select date,open,close,high,low,volume,money from `stk%s_%s`' % (thd.stock, fre), engine)
                # 先处理去包含
                for i in thd.get_price.index:
                    if i == 0 or i == 1:
                        thd.df_day = pd.concat([thd.df_day, thd.get_price.iloc[[i]]], ignore_index=True)
                    # 不包含
                    elif (thd.df_day.iloc[-1, 3] > thd.get_price.loc[i, 'high']
                          and thd.df_day.iloc[-1, 4] > thd.get_price.loc[i, 'low']) \
                            or (thd.df_day.iloc[-1, 3] < thd.get_price.loc[i, 'high']
                                and thd.df_day.iloc[-1, 4] < thd.get_price.loc[i, 'low']):
                        thd.df_day = pd.concat([thd.df_day, thd.get_price.loc[[i]]], ignore_index=True)
                    # 包含
                    else:
                        # 左高，下降
                        if thd.df_day.iloc[-2, 3] > thd.df_day.iloc[-1, 3]:
                            thd.df_day.iloc[-1, 3] = min(thd.df_day.iloc[-1, 3], thd.get_price.loc[i, 'high'])
                            thd.df_day.iloc[-1, 4] = min(thd.df_day.iloc[-1, 4], thd.get_price.loc[i, 'low'])
                        else:
                            # 右高，上升
                            thd.df_day.iloc[-1, 3] = max(thd.df_day.iloc[-1, 3], thd.get_price.loc[i, 'high'])
                            thd.df_day.iloc[-1, 4] = max(thd.df_day.iloc[-1, 4], thd.get_price.loc[i, 'low'])
                if len(thd.df_day.index) > 2:
                    # 寻找顶底分型
                    for x in range(2, len(thd.df_day.index)):
                        m = x - 1
                        # 底
                        if ((thd.df_day.loc[x, 'high'] > thd.df_day.loc[x - 1, 'high']) and (
                                thd.df_day.loc[x - 2, 'high'] > thd.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)
                            thd.df_day.loc[x, 'HL'] = 'L*'
                            while m:
                                if thd.df_day.loc[m, 'HL'] == 'H':
                                    if (x - m) > 3:
                                        thd.df_day.loc[x, 'HL'] = 'L'
                                        if x == len(thd.df_day.index) - 1:
                                            print(thd.stock, '$$$$$$$', '\n', thd.df_day.loc[x, 'date'], '买买买买买！！')
                                    break
                                elif (thd.df_day.loc[m, 'HL'] == 'L'):
                                    if thd.df_day.loc[x - 1, 'low'] < thd.df_day.loc[m - 1, 'low']:
                                        # 前一个为底，且中间存在不包含 or 更低的底
                                        thd.df_day.loc[x, 'HL'] = 'L'
                                        if x == len(thd.df_day.index) - 1:
                                            # pass
                                            print(thd.stock, '$$$$$$$', '\n', thd.df_day.loc[x, 'date'], '中继后的底————买吗？！')
                                        break
                                    else:
                                        break
                                m = m - 1
                                if m == 0:
                                    thd.df_day.loc[x, 'HL'] = 'L'
                        # 顶
                        elif ((thd.df_day.loc[x, 'high'] < thd.df_day.loc[x - 1, 'high']) and (
                                thd.df_day.loc[x - 2, 'high'] < thd.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)
                            thd.df_day.loc[x, 'HL'] = 'H*'
                            while m:
                                if thd.df_day.loc[m, 'HL'] == 'L':
                                    if x - m > 3:
                                        thd.df_day.loc[x, 'HL'] = 'H'
                                        if x == len(thd.df_day.index) - 1:
                                            # print(stock, '!!!!!!!', '\n', '卖卖卖卖卖卖卖!')
                                            pass
                                    break
                                elif (thd.df_day.loc[m, 'HL'] == 'H'):
                                    if thd.df_day.loc[x - 1, 'high'] > thd.df_day.loc[m - 1, 'high']:
                                        # 前一个为顶，且中间存在不包含 or 更高的顶
                                        thd.df_day.loc[x, 'HL'] = 'H'
                                        if x == len(thd.df_day.index) - 1:
                                            pass
                                            # print(stock, '/\/\/\/\/\/\/', '一顶更有一顶高！')
                                        break
                                    break
                                m = m - 1
                                if m == 0:
                                    thd.df_day.loc[x, 'HL'] = 'H'
                        else:
                            thd.df_day.loc[x, 'HL'] = '-'
                # 更新数据库
                thd.df_day.to_sql('stk%s_%s' % (thd.stock, fre), con=engine2, index=True, if_exists='append')

    step = 700
    thread_list = []
    engine = []
    engine2 = []
    times_engine = 0
    print(len(stocks))
    for i in range(0, len(stocks), step):
        engine.append(create_engine('mysql+pymysql://root:r6kEwqWU9!v3@localhost:3307/stocks?charset=utf8'))
        engine2.append(create_engine('mysql+pymysql://root:r6kEwqWU9!v3@localhost:3307/hlfx?charset=utf8'))
        thread = threading.Thread(target=hlfx, args=(stocks[i:i + step], engine[times_engine], engine2[times_engine]))
        times_engine = times_engine + 1
        thread.start()
        thread_list.append(thread)


    for thread in thread_list:
        thread.join()
    db.close()

    end= dt.now()
    print('总时长：', (end - start).seconds)