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='VopNWyLA-W8W8EooUmsX',
#                      database='test-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_stk_sql.cursor()
engine = create_engine('mysql+pymysql://root:r6kEwqWU9!v3@chenzheshi.myds.me:3456/stocks?charset=utf8')

print('ready to write to mysql')

for stock in stocks:
    df_stock =df.loc[stock]
    df_stock.to_sql('stk%s_%s' % (stock[:6], '1d'), 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], '1d'))
endtime = dt.now()
db_stk_sql.close()
print((endtime-starttime).seconds)