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


auth('18616891214', 'Ea?*7f68nD.dafcW34d!')
# futures = ['IF9999.CCFX', 'IC9999.CCFX','IH9999.CCFX', 'IM9999.CCFX', 'FU9999.XSGE', 'RB9999.XSGE', 'MA9999.XZCE', 'TA9999.XZCE', 'SA9999.XZCE', 'M9999.XDCE', 'LH9999.XDCE']
# futures = ['IF9999.CCFX', 'IC9999.CCFX','IH9999.CCFX', 'IM9999.CCFX']
futures = ['FU9999.XSGE', 'RB9999.XSGE', 'MA9999.XZCE', 'TA9999.XZCE', 'SA9999.XZCE', 'M9999.XDCE', 'LH9999.XDCE']
# futures = ['FU9999.XSGE']
engine = create_engine('mysql+pymysql://root:r6kEwqWU9!v3@localhost:3307/futures?charset=utf8')
# 获取各stock的去包含dataframe
fut = locals()
print(dt.now(), '开始赋值！')
for fre in ['30m']:
    for future in futures:
        try:
            fut[future] = pd.read_sql_query('select date,open,close,high,low,volume,money from `%s_%s`' % (future, fre),
                                            engine)
        except BaseException:
            continue

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'])