from jqdatasdk import * from datetime import datetime as dt import pandas as pd import pymysql from sqlalchemy import create_engine auth('18616891214', 'Ea?*7f68nD.dafcW34d!') stocks = list(get_all_securities(['stock'], date=dt.today().strftime('%Y-%m-%d')).index) engine = create_engine('mysql+pymysql://root:r6kEwqWU9!v3@localhost:3307/stocks?charset=utf8') engine_data = create_engine('mysql+pymysql://root:r6kEwqWU9!v3@localhost:3307/stocks_data?charset=utf8') # stocks_List = ','.join(set(stocks)) # db_stocks_list = pymysql.connect(host='localhost', # user='root', # port=3307, # password='r6kEwqWU9!v3', # database='hlfx_pool') # cursor_stock_list = db_stocks_list.cursor() # sql = "INSERT INTO stocks_list (date,securities) VALUES('%s','%s')" % (dt.today().strftime('%Y-%m-%d'), stocks_List) # cursor_stock_list.execute(sql) # db_stocks_list.commit() # db_stocks_list.close() fre = '1d' print('ready to write to mysql %s' % fre) for stock in stocks[2500:]: print(stock, fre) starttime ='2010-01-04' # endtime = pd.read_sql_table('stk%s_%s' % (stock, fre), con=engine).iloc[-1, 1] df_stock = get_price(stock, start_date=starttime, 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.index.name = 'date' # print(df_stock) # print(starttime,endtime) df_money = get_money_flow(stock, start_date=starttime, end_date=dt.today().strftime('%Y-%m-%d %H:%M:%S'), fields=None, count=None) df_money = df_money.drop(columns=['sec_code']) # df_money.to_csv('/Users/daniel/Downloads/000002.csv') # print(df_money) df_stock = pd.merge(df_stock, df_money, how='outer', left_index=False , on='date') df_stock.to_csv('/Users/daniel/Downloads/Result.csv') df_stock = df_stock.dropna(axis=0) 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_data, index=True, if_exists='append') # with engine.connect() as con: # con.execute("ALTER TABLE `stk%s_%s` ADD PRIMARY KEY (`date`);" % (stock, fre)) print(df_stock)