from jqdatasdk import *
auth('18616891214','Ea?*7f68nD.dafcW34d!')
from sqlalchemy import create_engine
import pandas as pd
from datetime import datetime as dt
import datetime

stocks = list(get_all_securities(['stock'], date='2022-02-25').index)
engine = create_engine('mysql+pymysql://root:r6kEwqWU9!v3@localhost:3307/stocks?charset=utf8')
fre = '30m'

print('ready to write to mysql')
for stock in stocks[0:10]:
    print(stock)
    index_len = pd.read_sql_table('stk%s_%s' % (stock[:6], fre), con=engine).iloc[-1, 0]
    # 注意修改time delta
    startdate = pd.read_sql_table('stk%s_%s' % (stock[:6], fre), con=engine).iloc[-1, 1] + datetime.timedelta(minutes=5)
    print(startdate)
    df_stock = get_price(stock, start_date=startdate, end_date=dt.today().strftime('%Y-%m-%d %H:%M:%S'),
                         frequency=fre, fields=['open', 'close', 'high', 'low', 'volume', 'money'], skip_paused=False,
                         fq='pre', count=None, panel=False)
    df_stock = df_stock.dropna(axis=0)
    df_stock.reset_index(inplace=True)
    df_stock.rename(columns={'index': 'date'}, inplace=True)
    df_stock.index = df_stock.index + index_len + 1
    df_stock.to_sql('stk%s_%s' % (stock[:6], fre), con=engine, index=True, if_exists='append')