from jqdatasdk import *
auth('18616891214','Ea?*7f68nD.dafcW34d!')
from sqlalchemy import create_engine


# 获得代码列表
stocks = list(get_all_securities(['stock'], date='2022-02-25').index)

# 建立stocks 数据库
engine = create_engine('mysql+pymysql://root:r6kEwqWU9!v3@localhost:3307/stocks?charset=utf8')

# 定义周期级别
fre = '30m'

print('ready to get history')

# 逐一取数据，写入sql
for stock in stocks[0:10]:
    print(stock)
    df_stock = get_price(stock, start_date='2022-01-01 00:00:00', end_date='2022-01-05 00:00:00',
                         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)
    # 重置index
    df_stock.reset_index(inplace=True)
    # df_stock.index = df_stock.index + index_len + 1
    # 保留日期数据
    df_stock.rename(columns={'index': 'date'}, inplace=True)
    # 写入数据库
    df_stock.to_sql('stk%s_%s' % (stock[:6], fre), con=engine, if_exists='append')
    with engine.connect() as con:
        con.execute('ALTER TABLE stk%s_%s ADD PRIMARY KEY (`date`);' % (stock[:6], fre))