async def query_czce_rank(query_date: str = Depends(verify_date)):
query_sql = "SELECT * FROM `czce_rank` WHERE `date`=%s;" % query_date
with ExchangeLibDB() as cursor:
cursor.execute(query_sql)
result = cursor.fetchall()
keys = OrderedDict({
"id": "ID",
"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": "持卖仓量增减"
})
return {
"message": "郑州商品交易所{}日持仓排名数据查询成功!".format(query_date),
"result": result,
"content_keys": keys
}
async def query_dce_rank_sum(query_date: str = Depends(verify_date),
rank: int = Query(20, ge=1, le=20)):
query_sql = "SELECT `date`,variety_en,contract," \
"sum(trade) AS total_trade," \
"sum(trade_increase) AS total_trade_increase," \
"sum(long_position) AS total_long_position," \
"sum(long_position_increase) AS total_long_position_increase," \
"sum(short_position) AS total_short_position," \
"sum(short_position_increase) AS total_short_position_increase," \
"(sum(long_position) - sum(short_position)) AS net_position " \
"FROM `dce_rank` " \
"WHERE `date`=%s AND `rank`>=1 AND `rank`<= %d " \
"GROUP BY variety_en;" % (query_date, rank)
with ExchangeLibDB() as cursor:
cursor.execute(query_sql)
result = cursor.fetchall()
keys = OrderedDict({
"date": "日期",
"variety_en": "品种",
"total_trade": "成交量合计(手)",
"total_trade_increase": "成交量增加合计",
"total_long_position": "持买仓量合计(手)",
"total_long_position_increase": "持买仓量增减合计",
"total_short_position": "持卖仓量合计(手)",
"total_short_position_increase": "持卖仓量增减合计",
"net_position": "净持仓(手)"
})
return {
"message": "大连商品交易所{}日持仓统计数据查询成功!".format(query_date),
"result": result,
"content_keys": keys
}
async def query_dce_daily(query_date: str = Depends(verify_date)):
query_sql = "SELECT * FROM `dce_daily` WHERE `date`=%s;" % query_date
with ExchangeLibDB() as cursor:
cursor.execute(query_sql)
result = cursor.fetchall()
keys = OrderedDict({
"id": "ID",
"date": "日期",
"variety_en": "品种",
"contract": "合约",
"pre_settlement": "前结算",
"open_price": "开盘价",
"highest": "最高价",
"lowest": "最低价",
"close_price": "收盘价",
"settlement": "结算价",
"zd_1": "涨跌1",
"zd_2": "涨跌2",
"trade_volume": "成交量",
"trade_price": "成交额",
"empty_volume": "持仓量",
"increase_volume": "增减量"
})
return {
"message": "大连商品交易所{}日交易行情数据查询成功!".format(query_date),
"result": result,
"content_keys": keys
}
async def query_dce_receipt(query_date: str = Depends(verify_date)):
query_sql = "SELECT * FROM `dce_receipt` WHERE `date`=%s;" % query_date
with ExchangeLibDB() as cursor:
cursor.execute(query_sql)
result = cursor.fetchall()
keys = OrderedDict({
"id": "ID",
"date": "日期",
"variety_en": "品种",
"warehouse": "仓库简称",
"receipt": "仓单数量",
"receipt_increase": "仓单增减"
})
return {
"message": "大连商品交易所{}仓单日报查询成功!".format(query_date),
"result": result,
"content_keys": keys
}
async def save_czce_receipt(
sources: List[CZCEReceiptItem] = Body(...),
current_date: str = Depends(verify_date)
):
data_json = jsonable_encoder(sources)
save_sql = "INSERT INTO `czce_receipt` " \
"(`date`,`variety_en`,`warehouse`," \
"`receipt`,`receipt_increase`,`premium_discount`) " \
"VALUES (%(date)s,%(variety_en)s,%(warehouse)s," \
"%(receipt)s,%(receipt_increase)s,%(premium_discount)s);"
with ExchangeLibDB() as cursor:
# 查询数据时间
cursor.execute("SELECT `id`, `date` FROM `czce_receipt` WHERE `date`=%s;" % current_date)
fetch_one = cursor.fetchone()
message = "{}郑商所仓单日报数据已经存在,请不要重复保存!".format(current_date)
if not fetch_one:
count = cursor.executemany(save_sql, data_json)
message = "保存{}郑商所仓单日报数据成功!\n新增数量:{}".format(current_date, count)
return {"message": message}
async def query_dce_receipt_sum(query_date: str = Depends(verify_date)):
query_sql = "SELECT `date`,variety_en," \
"sum(receipt) AS total_receipt, sum(receipt_increase) AS total_receipt_increase " \
"FROM `dce_receipt` WHERE `date`=%s GROUP BY variety_en;" % query_date
with ExchangeLibDB() as cursor:
cursor.execute(query_sql)
result = cursor.fetchall()
keys = OrderedDict({
"date": "日期",
"variety_en": "品种",
"total_receipt": "仓单合计",
"total_receipt_increase": "仓单增减",
})
return {
"message": "上海期货交易所{}每日仓单统计数据查询成功!".format(query_date),
"result": result,
"content_keys": keys
}
async def query_czce_daily_sum(query_date: str = Depends(verify_date)):
query_sql = "SELECT `date`,variety_en,sum(trade_volume) as total_trade_volume," \
"sum(empty_volume) as total_empty_volume, sum(increase_volume) as total_increase_volume " \
"FROM `czce_daily` WHERE `date`=%s GROUP BY variety_en;" % query_date
with ExchangeLibDB() as cursor:
cursor.execute(query_sql)
result = cursor.fetchall()
keys = OrderedDict({
"date": "日期",
"variety_en": "品种",
"total_trade_volume": "成交量合计(手)",
"total_empty_volume": "空盘量合计",
"total_increase_volume": "增减量合计"
})
return {
"message": "郑州商品交易所{}日行情统计查询成功!".format(query_date),
"result": result,
"content_keys": keys
}
async def save_cffex_daily(
sources: List[CFFEXDailyItem] = Body(...),
current_date: str = Depends(verify_date)
):
data_json = jsonable_encoder(sources)
save_sql = "INSERT INTO `cffex_daily` " \
"(`date`,`variety_en`,`contract`,`open_price`,`highest`,`lowest`," \
"`close_price`,`settlement`,`zd_1`,`zd_2`,`trade_volume`,`empty_volume`,`trade_price`) " \
"VALUES (%(date)s,%(variety_en)s,%(contract)s,%(open_price)s,%(highest)s,%(lowest)s," \
"%(close_price)s,%(settlement)s,%(zd_1)s,%(zd_2)s,%(trade_volume)s,%(empty_volume)s,%(trade_price)s);"
with ExchangeLibDB() as cursor:
# 查询数据时间
cursor.execute("SELECT `id`, `date` FROM `cffex_daily` WHERE `date`=%s;" % current_date)
fetch_one = cursor.fetchone()
message = "{}中金所日交易数据已经存在,请不要重复保存!".format(current_date)
if not fetch_one:
count = cursor.executemany(save_sql, data_json)
message = "保存{}中金所日交易数据成功!\n新增数量:{}".format(current_date, count)
return {"message": message}
async def save_dce_rank(
sources: List[DCERankItem] = Body(...),
current_date: str = Depends(verify_date)
):
data_json = jsonable_encoder(sources)
save_sql = "INSERT INTO `dce_rank` " \
"(`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`) " \
"VALUES (%(date)s,%(variety_en)s,%(contract)s,%(rank)s," \
"%(trade_company)s,%(trade)s,%(trade_increase)s," \
"%(long_position_company)s,%(long_position)s,%(long_position_increase)s," \
"%(short_position_company)s,%(short_position)s,%(short_position_increase)s);"
with ExchangeLibDB() as cursor:
# 查询数据时间
cursor.execute("SELECT `id`, `date` FROM `dce_rank` WHERE `date`=%s;" % current_date)
fetch_one = cursor.fetchone()
message = "{}大商所日持仓排名数据已经存在,请不要重复保存!".format(current_date)
if not fetch_one:
count = cursor.executemany(save_sql, data_json)
message = "保存{}大商所日持仓排名数据成功!\n新增数量:{}".format(current_date, count)
return {"message": message}
async def all_variety_net_position(interval_days: int = Query(1)):
# 获取当前日期及45天前
current_date = datetime.today()
pre_date = current_date + timedelta(days=-45)
start_date = pre_date.strftime('%Y%m%d')
end_date = current_date.strftime('%Y%m%d')
with ExchangeLibDB() as cursor:
# 查询大商所的品种净持仓
cursor.execute(
"select `date`,variety_en,(sum(long_position) - sum(short_position)) as net_position "
"from dce_rank "
"where date<=%s and date>=%s and `rank`>=1 and `rank`<=20 "
"group by `date`,variety_en;", (end_date, start_date))
dce_net_positions = cursor.fetchall()
# 查询郑商所的品种净持仓
cursor.execute(
"select `date`,variety_en,(sum(long_position) - sum(short_position)) as net_position "
"from czce_rank "
"where date<=%s and date>=%s and `rank`>=1 and `rank`<=20 and variety_en=contract "
"group by `date`,variety_en;", (end_date, start_date))
czce_net_positions = cursor.fetchall()
# 查询上期所的品种净持仓
cursor.execute(
"select `date`,variety_en,(sum(long_position) - sum(short_position)) as net_position "
"from shfe_rank "
"where date<=%s and date>=%s and `rank`>=1 and `rank`<=20 "
"group by `date`,variety_en;", (end_date, start_date))
shfe_net_positions = cursor.fetchall()
# 查询中金所的品种净持仓
cursor.execute(
"select `date`,variety_en,(sum(long_position) - sum(short_position)) as net_position "
"from cffex_rank "
"where date<=%s and date>=%s and `rank`>=1 and `rank`<=20 "
"group by `date`,variety_en;", (end_date, start_date))
cffex_net_positions = cursor.fetchall()
# 处理各交易所的数据
dce_df = DataFrame(dce_net_positions)
czce_df = DataFrame(czce_net_positions)
shfe_df = DataFrame(shfe_net_positions)
cffex_df = DataFrame(cffex_net_positions)
all_variety_df = concat([
pivot_data_frame(dce_df, 'dce'),
pivot_data_frame(czce_df, 'czce'),
pivot_data_frame(shfe_df, 'shfe'),
pivot_data_frame(cffex_df, 'cffex')
])
# 倒置列顺序
all_variety_df = all_variety_df.iloc[:, ::-1]
# 间隔取数
split_df = all_variety_df.iloc[:, ::interval_days]
# 整理表头
split_df.columns = split_df.columns.droplevel(0)
split_df = split_df.reset_index()
split_df = split_df.fillna(0)
# 处理顺序(按字母排序)
split_df.sort_values(by='variety_en', inplace=True)
# 增加中文列
split_df["variety_zh"] = split_df["variety_en"].apply(get_variety_zh)
data_dict = split_df.to_dict(orient='records')
header_keys = split_df.columns.values.tolist()
header_keys.remove("variety_zh") # 删除中文标签
header_keys[0] = "variety_zh" # 第一个改为中文
final_data = dict()
for item in data_dict:
final_data[item['variety_en']] = item
return {
"message": "查询全品种净持仓数据成功!",
"data": final_data,
'header_keys': header_keys
}
