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


from datetime import datetime as dt
import pymysql

starttime = dt.now()
stocks = list(get_all_securities(['stock'], date='2021-12-31').index)

print('ready to get bars')
# df = get_bars(stocks, count=2000, unit='1d',
#                       fields=['date', 'open', 'close', 'high', 'low', 'volume', 'money'], include_now=False, df=True)


# db = pymysql.connect(host='localhost',
#                      user='root',
#                      password='r6kEwqWU9!v3',
#                      database='stocks',
#                      connect_timeout=600)
# # db_stk_sql = pymysql.connect(host='chenzheshi.myds.me',
#                              port=3456,
#                              user='root',
#                              password='r6kEwqWU9!v3',
#                              database='stocks',
#                              connect_timeout=600)
# cursor = db.cursor()
engine = create_engine('mysql+pymysql://root:r6kEwqWU9!v3@localhost:3307/stocks?charset=utf8')

print('ready to write to mysql')

fre = '1d'

for stock in stocks:
    # df_stock =df.loc[stock]
    print(stock)
    df_stock = get_price(stock, start_date='2010-01-01 00:00:00', 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)
    df_stock.reset_index(inplace=True)
    df_stock.rename(columns={'index': 'date'}, inplace=True)
    df_stock.to_sql('stk%s_%s' % (stock[:6], fre), con=engine, index=True, if_exists='replace')
    with engine.connect() as con:
        con.execute('ALTER TABLE stk%s_%s ADD PRIMARY KEY (`date`);' % (stock[:6], fre))
endtime = dt.now()
# db_stk_sql.close()
print((endtime-starttime).seconds)