stock/his_money_flow.py

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:
    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('D:\001_QuantTrade\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='replace')
    # with engine.connect() as con:
    #     con.execute("ALTER TABLE `stk%s_%s` ADD PRIMARY KEY (`date`);" % (stock, fre))
    print(df_stock)