在前面的股票列表設(shè)計(jì)中，我們有一個(gè)list_status字段，可能的值為L(zhǎng)上市 D退市 P暫停上市。
由于股票可能會(huì)被退市，因此需要該字段來(lái)維護(hù)上市狀態(tài)。
深市爬蟲：

# 讀取深交所最新退市股票列表
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():# 處理每一行的數(shù)據(jù)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():# 處理每一行的數(shù)據(jù)stocks.append(row)os.remove(cache_file_path)return stocks

控制模塊的更新代碼：

# 根據(jù)退市公司信息更新股票列表
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)  # 讀取查詢結(jié)果# 讀取深交所退市公司信息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["證券簡(jiǎn)稱"], stock[2], stock[3], stock[4], stock[5], stock[6], stock[7],DELISTED, stock[9], delisted_stock["終止上市日期"], stock[11], stock[12], stock[0]))break# 更新數(shù)據(jù)庫(kù)中存在的股票信息if len(update_rows) > 0:result = ExecInsert(database, update_sql, update_rows)if result == 'success':print("更新深交所退市股票成功.")else:raise CustomException("更新深交所退市股票時(shí)發(fā)生數(shù)據(jù)庫(kù)異常:" + result)print("深交所退市股票更新結(jié)束.")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)  # 讀取查詢結(jié)果# 讀取上交所退市公司信息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["原公司簡(jiǎn)稱"], stock[2], stock[3], stock[4], stock[5], stock[6], stock[7],DELISTED, stock[9], delist_date, stock[11], stock[12], stock[0]))break# 更新數(shù)據(jù)庫(kù)中存在的股票信息if len(update_rows) > 0:result = ExecInsert(database, update_sql, update_rows)if result == 'success':print("更新上交所退市股票成功.")else:raise CustomException("更新上交所退市股票時(shí)發(fā)生數(shù)據(jù)庫(kù)異常:" + result)print("上交所退市股票更新結(jié)束.")if BJ == True:pass