from jqdatasdk import *
from datetime import datetime as dt
import pandas as pd

from sqlalchemy import create_engine




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

fre = ['30m', '1d']

engine_hlfx_pool = create_engine('mysql+pymysql://root:r6kEwqWU9!v3@localhost:3307/hlfx_pool?charset=utf8')
engine_stock = create_engine('mysql+pymysql://root:r6kEwqWU9!v3@localhost:3307/stocks?charset=utf8')



fut = locals()
print(dt.now(), '开始赋值！')
for fre in ['1d']:

        try:
            stock_pool = pd.read_sql_query(
                'select value from `%s`' % fre, engine_hlfx_pool)
            print(stock_pool)
            stock_pool = stock_pool.iloc[-1, 0].split(",")
            print(stock_pool)
        except BaseException:
            continue
        for stock in stock_pool[0:1]:
            print(stock)

            df_stock=df = get_bars(stock, count=10, unit=fre, fields=['date', 'open', 'close','high','low' ], include_now=True, df=True)
            price = df_stock.iloc[-1].at['close']
            price_open = df_stock.iloc[-1].at['open']
            MA5_1 = df_stock['close'][-7:-2].mean()
            MA5 = df_stock['close'][-6:-1].mean()
            MA10 = df_stock['close'].mean()
            print(price,price_open, 'ma5_1:',MA5_1, 'ma5:', MA5,MA10)
            if (price > price_open) & (price > MA5) & (MA5 > MA5_1) & (price < MA5 * 1.07):
                print('BUY')

print(dt.now(), '数据库数据已赋值！')

# print(fut['IF9999.CCFX'])

# start = dt.now()
# print(start)
# for f in futures:
#     fut[f] = pd.concat([fut[f], pd.DataFrame(columns=['MA5', 'derta'])],axis=1)
#     print(fut[f])
#
#     for i in range(len(fut[f])):
#         MA1 = MA(f, check_date=fut[f].loc[i, 'date'], timeperiod=5)
#         MA1 = MA1[f]
#         fut[f].loc[i, 'MA5'] = MA1
#         # print(fut[f].loc[i, ['close','high','low']])
#         if fut[f].loc[i, 'close'] > MA1:
#             derta = fut[f].loc[i, 'high']/MA1 - 1
#         else:
#             derta = (fut[f].loc[i, 'low']/MA1 -1)
#         fut[f].loc[i, 'derta'] = derta
#     print('___________________________________')
#     print(fut[f])
#     fut[f].to_excel('/Users/daniel/Downloads/MA_derta/30m_%s.xlsx'% f)
#
# end= dt.now()
# print('总时长：', (end - start).seconds)









# MA1 = MA('MA9999.XZCE', check_date='2022-09-25', timeperiod=5)
# print(MA1['MA9999.XZCE'])