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

# 获得代码列表
stocks = list(get_all_securities(['stock'], date='2022-03-08').index)
# stocks = stocks[0:50]

# 建立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:
    print(stock)
    df_stock = get_price(stock, start_date='2020-01-01 00:00:00', end_date='2022-03-08 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.rename(columns={'index': 'date'}, inplace=True)
    # 写入数据库
    df_stock.to_sql('stk%s_%s' % (stock, fre), con=engine, if_exists='append')
    with engine.connect() as con:
        con.execute("ALTER TABLE `stk%s_%s` ADD PRIMARY KEY (`date`);" % (stock, fre))