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

在前面的股票列表设计中，我们有一个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