搭建自己的金融数据源和量化分析平台（五）：更新两市退市股票信息

在前面的股票列表设计中，我们有一个list_status字段，可能的值为L上市 D退市 P暂停上市。
由于股票可能会被退市，因此需要该字段来维护上市状态。
深市爬虫：

# 读取深交所最新退市股票列表
def get_delisted_stock_list():
    cache_file_path = "./SZ_delisted_sotck_file.xlsx"
    url = "https://www.szse.cn/api/report/ShowReport?SHOWTYPE=xlsx&CATALOGID=1793_ssgs&TABKEY=tab2"
    response = requests.get(url)
    open(cache_file_path, "wb").write(response.content)
    file = pd.read_excel(cache_file_path, dtype={'证券代码': str})
    stocks = []
    for index, row in file.iterrows():
        # 处理每一行的数据
        stocks.append(row)
    os.remove(cache_file_path)
    return stocks

沪市爬虫：

# 读取上交所最新退市股票列表
def get_delisted_stock_list():
    cache_file_path = "./SH_delisted_sotck_file.xls"
    url = "https://query.sse.com.cn//sseQuery/commonExcelDd.do?sqlId=COMMON_SSE_CP_GPJCTPZ_GPLB_ZZGP_L&type=inParams&STOCK_CODE=&REG_PROVINCE=&STOCK_TYPE=1,2&COMPANY_STATUS=3"
    headers = {
        'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/58.0.3029.110 Safari/537.3',
        'Referer': 'https://www.sse.com.cn/',
        'Connection': 'close'
    }
    response = requests.get(url=url, headers=headers)
    open(cache_file_path, "wb").write(response.content)
    file = pd.read_excel(cache_file_path, dtype={'原公司代码': str})
    stocks = []
    for index, row in file.iterrows():
        # 处理每一行的数据
        stocks.append(row)
    os.remove(cache_file_path)
    return stocks

控制模块的更新代码：

# 根据退市公司信息更新股票列表
def update_A_delisted_stock(SZ=False,SH=False,BJ=False):
    database = "stock_a"
    if SZ == True:
        select_sql = "select * from stock_list where exchange = 'SZSE'"
        update_sql = "update stock_list set stock_name=%s,province=%s,industry=%s,industry_2=%s,enname=%s,market=%s,exchange=%s,list_status=%s,list_date=%s,delist_date=%s,total_share=%s,float_share=%s where stock_code=%s"
        select_result = ExecSelect(database, select_sql)  # 读取查询结果
        # 读取深交所退市公司信息
        delisted_stocks = A_SZ_basic.get_delisted_stock_list()
        update_rows = []
        for stock in select_result:
            # 遍历查询股票是否在退市股票列表中
            for delisted_stock in delisted_stocks:
                if stock[0] == delisted_stock["证券代码"]:
                    update_rows.append((delisted_stock["证券简称"], stock[2], stock[3], stock[4], stock[5], stock[6], stock[7],
                                        DELISTED, stock[9], delisted_stock["终止上市日期"], stock[11], stock[12], stock[0]))
                    break
        # 更新数据库中存在的股票信息
        if len(update_rows) > 0:
            result = ExecInsert(database, update_sql, update_rows)
            if result == 'success':
                print("更新深交所退市股票成功.")
            else:
                raise CustomException("更新深交所退市股票时发生数据库异常:" + result)
        print("深交所退市股票更新结束.")
    if SH == True:
        select_sql = "select * from stock_list where exchange = 'SSE'"
        update_sql = "update stock_list set stock_name=%s,province=%s,industry=%s,industry_2=%s,enname=%s,market=%s,exchange=%s,list_status=%s,list_date=%s,delist_date=%s,total_share=%s,float_share=%s where stock_code=%s"
        select_result = ExecSelect(database, select_sql)  # 读取查询结果
        # 读取上交所退市公司信息
        delisted_stocks = A_SH_basic.get_delisted_stock_list()
        update_rows = []
        for stock in select_result:
            # 遍历查询股票是否在退市股票列表中
            for delisted_stock in delisted_stocks:
                if stock[0] == delisted_stock["原公司代码"]:
                    delite_date_str = str(delisted_stock["终止上市日期"])
                    delist_date = delite_date_str[0:4]+"-"+delite_date_str[4:6]+"-"+delite_date_str[6:8]
                    update_rows.append(
                        (delisted_stock["原公司简称"], stock[2], stock[3], stock[4], stock[5], stock[6], stock[7],
                         DELISTED, stock[9], delist_date, stock[11], stock[12], stock[0]))
                    break
        # 更新数据库中存在的股票信息
        if len(update_rows) > 0:
            result = ExecInsert(database, update_sql, update_rows)
            if result == 'success':
                print("更新上交所退市股票成功.")
            else:
                raise CustomException("更新上交所退市股票时发生数据库异常:" + result)
        print("上交所退市股票更新结束.")
    if BJ == True:
        pass