stock/update_data_tosql.py

from jqdatasdk import *
from sqlalchemy import create_engine
import pandas as pd
from datetime import datetime as dt
import datetime
import pymysql

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')
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()
print('stocks_list已入库')

for fre in ['30m', '1d']:
    print('ready to write to mysql %s' % fre)
    for stock in stocks:
        print(stock, fre)
        try:
            index_len = pd.read_sql_table('stk%s_%s' % (stock, fre), con=engine).iloc[-1, 0]
            # 注意修改time delta
            if fre == '1d':
                startdate = pd.read_sql_table('stk%s_%s' % (stock, fre), con=engine).iloc[-1, 1] + datetime.timedelta(
                    days=1)
            elif fre == '30m':
                startdate = pd.read_sql_table('stk%s_%s' % (stock, fre), con=engine).iloc[-1, 1] + datetime.timedelta(
                    minutes=5)
            print(startdate)
            df_stock = get_price(stock, start_date=startdate, 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 = df_stock.dropna(axis=0)
            df_stock.reset_index(inplace=True)
            df_stock.rename(columns={'index': 'date'}, inplace=True)
            df_stock.index = df_stock.index + index_len + 1
            df_stock.to_sql('stk%s_%s' % (stock, fre), con=engine, index=True, if_exists='append')
        except BaseException:
            df_stock = get_price(stock, start_date='2022-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, panel=False)
            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, index=True, if_exists='append')
            with engine.connect() as con:
                con.execute("ALTER TABLE `stk%s_%s` ADD PRIMARY KEY (`date`);" % (stock, fre))