test/hk_qfqV1.py

# -*- coding: utf-8 -*-
"""
Created on Mon Aug 30 16:57:10 2021

@author: Administrator
"""

import pandas as pd
import akshare as ak
from sqlalchemy import create_engine,text
import datetime
import sys
sys.path.append(r'C:\Users\marui\Desktop\mamama\AKshare\daily_update.py')
sys.path.append(r'C:\workspace\daily_price')

import daily_update

today = str(datetime.date.today()).replace('-','')
# today  = '20241225'
def update_data():
    #取港股实时数据
    c = ak.stock_hk_spot_em()
    a = c[c['最新价']=='-'].index.tolist()
    b = c.drop(a)
    ticker = b['代码'].tolist()
    return ticker

#未复权
def hk_daily_prices(table_name):
    table_name = 'ticker_daily_original_database.hk_daily_prices'
    engine = create_engine(engine_server)
    ticker = update_data()
    all_data = []
    for x in ticker:
        stock_hk_hist_df = ak.stock_hk_hist(symbol=x, start_date=today, end_date=today, adjust="")
        
        stock_hk_hist_df['Ticker'] = x
        stock_hk_hist_df['Adjustflag'] = '3'
        stock_hk_hist_df.columns = ['As_Of_Date','Open','Close','High','Low','Volume_Shares','Volume_Money','Amplitude','Pct_Chg','Change','Turnover','Ticker','Adjustflag']
        all_data.append(stock_hk_hist_df)
    all_dataframe = pd.concat(all_data)
    all_dataframe.to_sql(table_name.split('.')[1],engine,schema = table_name.split('.')[0], index = False, index_label=False,if_exists = 'append')
    print('港股 未复权数据已更新')


#前复权S
def hk_daily_prices_qfq(table_name):
    table_name = 'ticker_daily_original_database.hk_daily_prices_qfq'
    engine = create_engine(engine_server)
    ticker = update_data()
    a = ['06186','00700']
    b = [i for i in ticker if i not in a]
    ticker = a+b

    with engine.connect() as connection:
        trans = connection.begin()  # 开始事务
        try:
            sql = f'TRUNCATE TABLE {table_name}'
            connection.execute(text(sql))
            trans.commit()  # 提交事务
        except Exception as e:
            trans.rollback()  # 回滚事务
            print(f"Error occurred: {e}")
    
    for j in ticker:
        print(j)
        stock_hk_hist_qfq_df = ak.stock_hk_hist(symbol=j, start_date="19700101", end_date=today, adjust="qfq")
        stock_hk_hist_qfq_df_rename =  stock_hk_hist_qfq_df.rename(columns={'日期':'As_Of_Date','开盘':'Open','收盘':'Close','最高':'High',\
                                  '最低':'Low','成交量':'Volume_Shares','成交额':'Volume_Money',\
                                '振幅':'Amplitude','涨跌幅':'Pct_Chg','涨跌额':'Change','换手率':'Turnover'})
        
        stock_hk_hist_qfq_df_rename['Ticker'] = j
        stock_hk_hist_qfq_df_rename['Adjustflag'] = '2'
        if j == '01405':
            stock_hk_hist_qfq_df_rename = stock_hk_hist_qfq_df_rename[~(stock_hk_hist_qfq_df_rename['As_Of_Date'] =='2009-10-27')|(stock_hk_hist_qfq_df_rename['Volume_Money'] == 0)]
        else:
            pass
        
        stock_hk_hist_qfq_df_rename = stock_hk_hist_qfq_df_rename.drop_duplicates()
        stock_hk_hist_qfq_df_rename.to_sql(table_name.split('.')[1],engine,schema = table_name.split('.')[0], index = False, index_label=False,if_exists = 'append')
        print('港股 前复权数据已更新')


#后复权
def hk_daily_prices_hfq(table_name):
    table_name = 'ticker_daily_original_database.hk_daily_prices_hfq'
    engine = create_engine(engine_server)
    ticker = update_data()
    for c in ticker:
        stock_hk_hist_hfq_df = ak.stock_hk_hist(symbol=c, start_date=today, end_date=today, adjust="hfq")
        stock_hk_hist_hfq_df_rename = stock_hk_hist_hfq_df.rename(columns = {'日期':'As_Of_Date','开盘':'Open','收盘':'Close','最高':'High',\
                                  '最低':'Low','成交量':'Volume_Shares','成交额':'Volume_Money',\
                                '振幅':'Amplitude','涨跌幅':'Pct_Chg','涨跌额':'Change','换手率':'Turnover'})
        stock_hk_hist_hfq_df_rename['Ticker'] = c
        stock_hk_hist_hfq_df_rename['Adjustflag'] = '1'
        stock_hk_hist_hfq_df_rename.to_sql(table_name.split('.')[1],engine,schema = table_name.split('.')[0], index = False, index_label=False,if_exists = 'append')
        print('港股 后复权数据已更新')



if __name__ == '__main__':
    engine_server = "mysql+pymysql://cn_ainvest_db:cn_ainvest_sd3a1@rm-2zewagytttzk6f24xno.mysql.rds.aliyuncs.com/"
    update_servers = '152.136.226.199'
    table_name_list = ['ticker_daily_original_database.hk_daily_prices','ticker_daily_original_database.hk_daily_prices_qfq','ticker_daily_original_database.hk_daily_prices_hfq']
    
    
    
    for table_name in table_name_list:
        try:
            if table_name == 'ticker_daily_original_database.hk_daily_prices':
                hk_daily_prices(table_name)
            elif table_name == 'ticker_daily_original_database.hk_daily_prices_qfq':
                hk_daily_prices_qfq(table_name)
            else:
                hk_daily_prices_hfq(table_name)
            daily_update.update_end_date(engine_server,table_name)
        except Exception as e :
            print(e)
            #daily_update.send_email("数据更新报错","数据库："+table_name+"\n更新服务器："+update_servers)
            print('wrong')