async def czce_contract_basis(contract: str = Depends(verify_contract),
query_month: int = Query(3)):
today = datetime.today()
start_date = (today +
timedelta(days=-(query_month * 30))).strftime("%Y%m%d")
end_date = today.strftime("%Y%m%d")
variety_en, _ = split_number_en(contract)
# 查询数据
with MySqlZ() as cursor:
cursor.execute(
"SELECT vsptb.date,vsptb.spot_price,vsptb.price_increase,dcedtb.variety_en, dcedtb.contract,dcedtb.close_price, dcedtb.settlement "
"FROM variety_spot_price AS vsptb "
"INNER JOIN czce_daily AS dcedtb "
"ON vsptb.variety_en=dcedtb.variety_en AND dcedtb.contract=%s AND vsptb.date=dcedtb.date "
"AND vsptb.date>=%s AND vsptb.date<=%s;",
(contract, start_date, end_date))
data = cursor.fetchall()
return {"message": "郑商所{}现货与期货价数据查询成功!".format(contract), "data": data}
def modify_contract_express(contract, current_date):
number_en = split_number_en(contract)
return current_date[2].join(number_en)
def _parser_variety_rank(cache_folder):
""" 读取文件夹内文件解析 """
all_data_df = DataFrame(columns=[
"date", "variety_en", "contract", "rank", "trade_company", "trade",
"trade_increase", "long_position_company", "long_position",
"long_position_increase", "short_position_company",
"short_position", "short_position_increase"
])
filename_list = os.listdir(cache_folder)
for contract_filename in filename_list:
contract_file_path = os.path.join(cache_folder, contract_filename)
message_list = contract_filename.split('_')
c_date = message_list[0] # 得到日期
contract = message_list[1].upper() # 得到合约
variety_en = split_number_en(message_list[1])[0].upper() # 得到合约代码
contract_df = read_table(contract_file_path)
extract_indexes = list()
start_index, end_index = None, None
for df_row in contract_df.itertuples():
if df_row[1] == "名次":
start_index = df_row[0]
if df_row[1] == "总计":
end_index = df_row[0] - 1
if start_index is not None and end_index is not None:
extract_indexes.append([start_index, end_index])
start_index, end_index = None, None
contract_result_df = DataFrame()
for split_index in extract_indexes:
target_df = contract_df.loc[split_index[0]:split_index[1]]
first_row = target_df.iloc[0]
first_row = first_row.fillna("nana") # 填充NAN的值为nana方便删除这些列
target_df.columns = first_row.values.tolist() # 将第一行作为表头
target_df = target_df.reset_index() # 重置索引
target_df = target_df.drop(labels=0) # 删除第一行
if target_df.columns.values.tolist() == [
'index', '名次', 'nana', '会员简称', '持买单量', '增减', 'nana',
'nana', 'nana', 'nana', 'nana'
]:
target_df.columns = [
'index2', '名次', 'nana', '会员简称2', '持买单量', 'nana', '增减2',
'nana', 'nana', 'nana', 'nana'
]
elif target_df.columns.values.tolist() == [
'index', '名次', 'nana', '会员简称', '持卖单量', '增减', 'nana',
'nana', 'nana', 'nana', 'nana'
]:
target_df.columns = [
'index3', '名次', 'nana', '会员简称3', '持卖单量', 'nana', '增减3',
'nana', 'nana', 'nana', 'nana'
]
# 删除为nana的列
target_df = target_df.drop("nana", axis=1) # 删除为nana的列
if contract_result_df.empty:
contract_result_df = target_df
else:
contract_result_df = merge(contract_result_df,
target_df,
on="名次")
# 提取需要的列，再重命名列头
contract_result_df["日期"] = [
c_date for _ in range(contract_result_df.shape[0])
]
contract_result_df["品种"] = [
variety_en for _ in range(contract_result_df.shape[0])
]
contract_result_df["合约"] = [
contract for _ in range(contract_result_df.shape[0])
]
# 重置列名取需要的值
contract_result_df = contract_result_df.reindex(columns=[
"日期", "品种", "合约", "名次", "会员简称", "成交量", "增减", "会员简称2", "持买单量",
"增减2", "会员简称3", "持卖单量", "增减3"
])
contract_result_df.columns = [
"date", "variety_en", "contract", "rank", "trade_company",
"trade", "trade_increase", "long_position_company",
"long_position", "long_position_increase",
"short_position_company", "short_position",
"short_position_increase"
]
# 填充缺失值
contract_result_df[[
"trade_company", "long_position_company",
"short_position_company"
]] = contract_result_df[[
"trade_company", "long_position_company",
"short_position_company"
]].fillna('')
contract_result_df = contract_result_df.fillna('0')
# 修改数据类型
contract_result_df["rank"] = contract_result_df["rank"].apply(
str_to_int)
contract_result_df["trade"] = contract_result_df["trade"].apply(
str_to_int)
contract_result_df["trade_increase"] = contract_result_df[
"trade_increase"].apply(str_to_int)
contract_result_df["long_position"] = contract_result_df[
"long_position"].apply(str_to_int)
contract_result_df["long_position_increase"] = contract_result_df[
"long_position_increase"].apply(str_to_int)
contract_result_df["short_position"] = contract_result_df[
"short_position"].apply(str_to_int)
contract_result_df["short_position_increase"] = contract_result_df[
"short_position_increase"].apply(str_to_int)
# 取1<=名次<=20的数据
contract_result_df = contract_result_df[
(1 <= contract_result_df["rank"])
& (contract_result_df["rank"] <= 20)]
all_data_df = concat([all_data_df, contract_result_df])
return all_data_df