def get_stock_suspension_dict_by_date(start_date, end_date, sus_type=1): if sus_type == 0: #全部 type_list = [ "444001000", "444002000", "444003000", "444004000", "444007000", "444016000" ] elif sus_type == 1: #一天及以上 type_list = ["444003000", "444016000"] elif sus_type == 2: #早盘停牌 type_list = ["444001000", "444003000", "444016000"] elif sus_type == 3: #尾盘停牌 type_list = ["444002000", "444003000", "444016000"] else: print "Not now..." daily_date_list = xyk_common_wind_db_interaction.get_calendar( start_date, end_date, 0) stock_sus_list = db_interaction.get_data_list( "stock_suspension", ["stock_id", "entry_date", "remove_date", "type"]) stock_sus_dict = {} for date in daily_date_list: stock_sus_dict[date] = [] i = 0 while i < len(stock_sus_list): if str(stock_sus_list[i][3]) in type_list: if stock_sus_dict.has_key(str(stock_sus_list[i][1])): stock_sus_dict[str(stock_sus_list[i][1])].append( str(stock_sus_list[i][0])) else: pass else: pass i += 1 return stock_sus_dict
def get_daily_data_dict_1_key(start_date, end_date, table_name, attribute_list, stock_list = [], is_str = 0, date_for_key = 0, to_df = 0): attribute_str = get_attribute_str(attribute_list) stock_str = get_stock_str(stock_list) thbase=database('sql_myhost.txt') if len(stock_list) == 0: str1="select stock_id, curr_date, " + attribute_str + " from " + table_name + " \ where curr_date >= '%s' and curr_date <= '%s'" %(start_date, end_date) else: str1="select stock_id, curr_date, " + attribute_str + " from " + table_name + " \ where curr_date >= '%s' and curr_date <= '%s' and stock_id in %s" %(start_date, end_date, stock_str) rows1=thbase.select(str1) data_dict = {} if date_for_key == 0: daily_date_list = xyk_common_wind_db_interaction.get_calendar(start_date, end_date, 0) for line in rows1: #print line this_date_str = str(line[1].strftime('%Y%m%d')) if this_date_str in daily_date_list: if not data_dict.has_key(line[0]): data_dict[line[0]] = [] daily_data = [this_date_str] j = 0 while j < len(attribute_list): if line[j + 2] != None: if is_str == 1: daily_data.append(str(line[j + 2])) else: daily_data.append(float(line[j + 2])) else: daily_data.append(None) j += 1 data_dict[line[0]].append(daily_data) if to_df != 0: for key in data_dict.keys(): data_dict[key] = pd.DataFrame(data_dict[key], columns = ['curr_date'] + attribute_list).set_index('curr_date') else: for line in rows1: #print line if not data_dict.has_key(str(line[1].strftime('%Y%m%d'))): data_dict[str(line[1].strftime('%Y%m%d'))] = [] daily_data = [str(line[0])] j = 0 while j < len(attribute_list): if line[j + 2] != None: if is_str == 1: daily_data.append(str(line[j + 2])) else: daily_data.append(float(line[j + 2])) else: daily_data.append(None) j += 1 data_dict[str(line[1].strftime('%Y%m%d'))].append(daily_data) if to_df != 0: for key in data_dict.keys(): data_dict[key] = pd.DataFrame(data_dict[key], columns = ['stock_id'] + attribute_list).set_index('stock_id') return data_dict
def get_stock_st_dict(start_date, end_date, st_type=0): if st_type == 0: #全部 type_list = ["S", "Z", "P", "L", "X", "T"] else: print "Not now..." daily_date_list = xyk_common_wind_db_interaction.get_calendar( start_date, end_date, 0) natural_date_list = xyk_common_data_processing.get_natural_datelist( start_date, end_date) date_index_dict = xyk_common_data_processing.construct_date_hirabiki_dict( daily_date_list, natural_date_list) stock_st_list = db_interaction.get_data_list( "stock_st", ["stock_id", "entry_date", "remove_date", "type"]) stock_st_dict = {} for date in daily_date_list: stock_st_dict[date] = [] i = 0 while i < len(stock_st_list): if str(stock_st_list[i][3]) in type_list: if stock_st_list[i][2] == 'NULL' or stock_st_list[i][2] == "": this_start_date = str(stock_st_list[i][1]) if this_start_date <= end_date: if this_start_date < start_date: this_start_date = start_date this_start_date_index = date_index_dict[this_start_date][1] j = this_start_date_index while j < len(daily_date_list): stock_st_dict[daily_date_list[j]].append( str(stock_st_list[i][0])) j += 1 else: pass else: this_start_date = str(stock_st_list[i][1]) this_end_date = str(stock_st_list[i][2]) if this_start_date > end_date or this_end_date < start_date or this_start_date > this_end_date: pass else: if this_start_date < start_date: this_start_date = start_date if this_end_date > end_date: this_end_date = end_date this_start_date_index = date_index_dict[this_start_date][1] this_end_date_index = date_index_dict[this_end_date][0] j = this_start_date_index while j <= this_end_date_index: stock_st_dict[daily_date_list[j]].append( str(stock_st_list[i][0])) j += 1 else: pass i += 1 return stock_st_dict
''' ***获取每日指数成分股和全部A股的代码*** ''' if Now_Range == "all": components_dict = db_data_pre_treat.get_normal_stocklist_dict(start_date, end_date, year=0, month=6) else: where = Now_Range + " = 1" components_dict = db_interaction.get_data_commonly( "daily_index_components", ["stock_id"], ["curr_date"], one_to_one=0, where=where) all_daily_date_list = xyk_common_wind_db_interaction.get_calendar( start_date, end_date, 0) daily_date_list = all_daily_date_list[:-1] index_date_list = all_daily_date_list[1:] a_stock_no_st_dict = db_data_pre_treat.get_a_stock_no_st_dict( start_date, end_date, 0, 6, 1, 0) total_stock_list = xyk_common_data_processing.get_all_element_from_dict( a_stock_no_st_dict) ''' ***获取行业数据并构建行业哑变量*** ''' citics_data = db_interaction.get_data_commonly( "stock_citics_industry", ["entry_date", "remove_date", "citics_code"], ["stock_id"], 0, 0, 0) dummy_variable_dict = db_data_pre_treat.get_indus_dummy_variable_dict( citics_code_list) '''
for tt, date in enumerate(sorted_date_list): if date >= start_date: if len(sorted_date_list) == (tt + 1): csv_next_date = "" else: csv_next_date = sorted_date_list[tt + 1] if date[4:6] == "12": next_date = str(int(date[:4]) + 1) + "0131" elif date[4:6] in ["09", "10", "11"]: next_date = date[:4] + str(int(date[4:6]) + 1) + "31" else: next_date = date[:5] + str(int(date[4:6]) + 1) + "31" if csv_next_date != "": next_date = min(next_date, csv_next_date) print next_date daily_date_list = xyk_common_wind_db_interaction.get_calendar( date, next_date, 0) for daily_date in daily_date_list: if daily_date != date: for stock in code_dict[date]: output_list.append([stock, daily_date, 1]) output_list.sort() ''' ***输出到数据库*** ''' table_name = "daily_index_components" db_interaction.insert_attributes_commonly(table_name, output_list, ['stock_id', 'curr_date', pool_name], [pool_name], batch=50000)
def get_a_stock_dict(start_date, end_date, year, month, board=1): if board == 0: #主板 board_list = ["434006000", "434004000"] elif board == 1: #全部 board_list = ["434006000", "434004000", "434003000", "434001000"] elif board == 2: #主板+创业板 board_list = ["434006000", "434004000", "434001000"] elif board == 3: #创业板 board_list = ["434001000"] elif board == 4: #主板+中小企业板 board_list = ["434006000", "434004000", "434003000"] elif board == 5: #创业板+中小企业板 board_list = ["434001000", "434003000"] elif board == 6: #主板补全 board_list = ["434006000"] else: print "What else do you want?!" daily_date_list = xyk_common_wind_db_interaction.get_calendar( start_date, end_date, 0) natural_date_list = xyk_common_data_processing.get_natural_datelist( start_date, end_date) date_index_dict = xyk_common_data_processing.construct_date_hirabiki_dict( daily_date_list, natural_date_list) a_stock_list = db_interaction.get_data_list( "a_stock", ["stock_id", "list_date", "delist_date", "board"]) a_stock_dict = {} for date in daily_date_list: a_stock_dict[date] = [] i = 0 while i < len(a_stock_list): if str(a_stock_list[i][3]) in board_list: if a_stock_list[i][2] == 'NULL' or a_stock_list[i][2] == "": this_list_date = str(a_stock_list[i][1]) this_start_date = xyk_common_data_processing.get_date_delta( this_list_date, year, month, 1) if this_start_date < start_date: this_start_date = start_date elif this_start_date > end_date: i += 1 continue if date_index_dict.has_key(this_start_date) == False: this_start_date = this_start_date[:6] + str( int(this_start_date[6:]) - 1) if date_index_dict.has_key(this_start_date) == False: this_start_date = this_start_date[:6] + str( int(this_start_date[6:]) - 1) if date_index_dict.has_key(this_start_date) == False: this_start_date = this_start_date[:6] + str( int(this_start_date[6:]) - 1) if this_start_date <= end_date: if this_start_date < start_date: this_start_date = start_date this_start_date_index = date_index_dict[this_start_date][1] j = this_start_date_index while j < len(daily_date_list): a_stock_dict[daily_date_list[j]].append( str(a_stock_list[i][0])) j += 1 else: pass else: this_list_date = str(a_stock_list[i][1]) this_start_date = xyk_common_data_processing.get_date_delta( this_list_date, year, month, 1) if this_start_date < start_date: this_start_date = start_date elif this_start_date > end_date: i += 1 continue if date_index_dict.has_key(this_start_date) == False: this_start_date = this_start_date[:6] + str( int(this_start_date[6:]) - 1) if date_index_dict.has_key(this_start_date) == False: this_start_date = this_start_date[:6] + str( int(this_start_date[6:]) - 1) if date_index_dict.has_key(this_start_date) == False: this_start_date = this_start_date[:6] + str( int(this_start_date[6:]) - 1) this_end_date = str(a_stock_list[i][2]) if this_start_date > end_date or this_end_date < start_date or this_start_date > this_end_date: pass else: if this_start_date < start_date: this_start_date = start_date if this_end_date > end_date: this_end_date = end_date this_start_date_index = date_index_dict[this_start_date][1] this_end_date_index = date_index_dict[this_end_date][0] j = this_start_date_index while j <= this_end_date_index: a_stock_dict[daily_date_list[j]].append( str(a_stock_list[i][0])) j += 1 else: pass i += 1 return a_stock_dict