def put(self, _id):
"""
修改投资组合
"""
# data_dic = request.get_json() or request.form
# logger.debug("data_dic(get_json): %s", data_dic)
# data_dic = request.args
# logger.debug("data_dic(args): %s", data_dic)
data_dic = json.loads(str(request.data, encoding='utf-8'))
logger.debug("data_dic(data): %s", data_dic)
# data_dic = request.values
# logger.debug("data_dic(values): %s", data_dic)
# TODO: 增加权限检查，只能修改自己创建的投资组合
# TODO: 交易日期必须大于等于当日，如果下午3点以后不得等于当日
if data_dic is None:
raise BadRequest('缺少json请求数据')
# return jsonify({"status": "error", "message": "no json"})
if "data" not in data_dic:
# return jsonify({"status": "error", "message": "'data' key in json"})
logger.error('请求数据：%s', data_dic)
raise BadRequest('json 请求数据缺少 data 数据')
# data = json_dic["data"]
add_pl_data(_id, data_dic)
return {"status": "ok", 'id': _id}
def post(self):
"""
创建预测信息
"""
# logger.debug("request.args: %s", request.args)
# logger.debug("request.values: %s", request.values)
# logger.debug("request.json: %s", request.json)
# logger.debug("request.data: %s", request.data)
data_dic = request.get_json() or request.form
logger.debug("data_dic: %s", data_dic)
# 添加投资组合信息
data_obj = PortfolioCompareInfo()
populate_obj(
data_obj,
data_dic,
attr_list=["name", "date_from", "date_to", "access_type", "desc"],
error_if_no_key=True)
# logger.debug("data_dic['params']<%s>: %s", type(data_dic['params']), data_dic['params'])
data_obj.params = json.dumps(data_dic['params'])
# TODO: 需要进行：1）参数合法性检查 2）pl_id user_id create_dt 等参数不得传入，类似无效参数过滤
user_id = session.get('user_id')
data_obj.create_user_id = user_id
db.session.add(data_obj)
db.session.commit()
logger.info('%s：id=%d 成功插入数据库 %s', data_obj.__class__.__name__,
data_obj.cmp_id, data_obj.__tablename__)
return {"status": "ok", 'id': data_obj.cmp_id}
def post(self):
"""
创建投资组合
"""
# logger.debug("request.json %s", request.json)
# json_dic = request.json
# if json_dic is None:
# return {"status": "error", "message": "no json"}
# if "data" not in json_dic:
# return {"status": "error", "message": "'data' key in json"}
# data_dic = json_dic["data"]
data_dic = request.get_json() or request.form
logger.debug("data_dic: %s", data_dic)
# 添加投资组合信息
data_obj = PortfolioInfo()
populate_obj(data_obj,
data_dic,
attr_list=["name", "access_type", "desc"],
error_if_no_key=True)
# TODO: 需要进行：1）参数合法性检查 2）pl_id user_id create_dt 等参数不得传入，类似无效参数过滤
user_id = session.get('user_id')
data_obj.create_user_id = user_id
db.session.add(data_obj)
db.session.commit()
logger.info('%s：id=%d 成功插入数据库 %s', data_obj.__class__.__name__,
data_obj.pl_id, data_obj.__tablename__)
# 添加投资组合
if 'pl_data' in data_dic:
pl_data_dic = data_dic['pl_data']
add_pl_data(data_obj.pl_id, pl_data_dic)
return {"status": "ok", 'id': data_obj.pl_id}
def delete(self, _id):
"""
删除指定预测
"""
logger.debug('cmp_id=%d', _id)
PortfolioCompareInfo.query.filter(
PortfolioCompareInfo.cmp_id == _id).update({'is_del': 1})
db.session.commit()
return {'status': 'ok', 'id': _id}
def get(self, _id):
"""
预测结果数据（供 charts 使用，无需分页返回）
"""
sql_str = """SELECT DATE_FORMAT(trade_date, "%%Y-%%m-%%d") trade_date,
asset_1, asset_2, asset_3, result, shift_value, shift_rate FROM pl_compare_result
WHERE cmp_id = %s"""
data_df = pd.read_sql(sql_str, db.engine, params=[_id])
ret_df = data_df.where(data_df.notna(), None)
ret_dic_list = ret_df.to_dict('list')
logger.debug('%d 条数据', ret_df.shape[0])
ret_dic = {'data': ret_dic_list, 'count': len(ret_dic_list)}
return ret_dic
def get(self, _id, status, method):
"""
获取制定投资组合的成分及权重数据（分页）
"""
args = paginate_parser.parse_args()
page_no = args['page_no']
count = args['count']
user_id = session.get('user_id')
logger.debug('get_cmp_list user_id:%s', user_id)
if method == 'record':
ret_dic = PortfolioListResource.get_pl_data_list(
_id, status, page_no, count)
elif method == 'date':
ret_dic = PortfolioListResource.get_pl_data_list_by_date(
_id, status, page_no, count)
else:
raise KeyError('status 参数错误 method = %s' % method)
return ret_dic
def add_pl_data(_id, pl_data_dic: dict):
"""
更新或插入投资组合
:param _id:
:param pl_data_dic:
:return:
"""
date_str = pl_data_dic['trade_date']
price_type = pl_data_dic['price_type']
pl_data_dic_bulk = pl_data_dic['data']
PortfolioData.query.filter(PortfolioData.pl_id == _id,
PortfolioData.trade_date == date_str).delete()
logger.debug("pl_id=%d: 剔除 %s 历史数据", _id, date_str)
# 获取上一个调仓日时的持仓纪录
pl_data_obj_list_last_date = PortfolioData.query.filter(
PortfolioData.pl_id == _id,
PortfolioData.trade_date == (db.session.query(
func.max(PortfolioData.trade_date)).filter(
PortfolioData.pl_id == _id))).all()
pl_data_obj_dic_last_date = {(data.asset_type, data.asset_code): data
for data in pl_data_obj_list_last_date}
# 建立持仓数据
pl_data_obj_list = []
for pl_d_dic in pl_data_dic_bulk:
pl_d_obj = PortfolioData()
pl_d_obj.pl_id = _id
populate_obj(pl_d_obj, pl_d_dic,
["asset_code", "asset_type", "weight", "direction"])
key = (pl_d_dic["asset_type"], pl_d_dic["asset_code"])
pl_d_obj.weight_before = pl_data_obj_dic_last_date[
key].weight if key in pl_data_obj_dic_last_date else 0
pl_d_obj.trade_date = date_str
pl_d_obj.price_type = price_type
pl_data_obj_list.append(pl_d_obj)
# 批量插入
db.session.bulk_save_objects(pl_data_obj_list)
db.session.commit()
logger.debug("pl_id=%d: %d 投资组合数据插入到 pl_data 表", _id,
len(pl_data_obj_list))
def get(self, status):
"""
获取投资组合列表数据
"""
args = paginate_parser.parse_args()
page_no = args['page_no']
count = args['count']
user_id = session.get('user_id')
# 获取各个组合 最新交易日
date_latest_query = db.session.query(
PortfolioValueDaily.pl_id,
func.max(PortfolioValueDaily.trade_date).label(
'trade_date_max')).group_by(
PortfolioValueDaily.pl_id).subquery('date_latest')
# 获取各个投资组合 最新净值
nav_latest_query = db.session.query(
PortfolioValueDaily.pl_id, PortfolioValueDaily.trade_date,
PortfolioValueDaily.rr, PortfolioValueDaily.nav).filter(
PortfolioValueDaily.pl_id == date_latest_query.c.pl_id,
PortfolioValueDaily.trade_date ==
date_latest_query.c.trade_date_max).subquery('nav_latest')
# 分页查询投资组合信息及最新净值
if status == 'my':
filter_c = PortfolioInfo.create_user_id == user_id
elif status == 'all':
filter_c = or_(PortfolioInfo.access_type == 'public',
PortfolioInfo.create_user_id == user_id)
elif status == 'star':
# TODO: 星标投资组合
filter_c = not_(func.isnull(FavoriteCompare.update_time))
else:
filter_c = None
if filter_c is None:
raise KeyError('status 参数错误 status = %s' % status)
else:
pagination = PortfolioInfo.query.outerjoin(
nav_latest_query,
PortfolioInfo.pl_id == nav_latest_query.c.pl_id).add_columns(
nav_latest_query.c.trade_date,
nav_latest_query.c.rr,
nav_latest_query.c.nav,
).outerjoin(User).add_columns(User.username).outerjoin(
FavoritePortfolio,
and_(PortfolioInfo.pl_id == FavoritePortfolio.pl_id,
FavoritePortfolio.user_id == user_id)).add_columns(
func.if_(
func.isnull(FavoritePortfolio.update_time), 0,
1).label('favorite')).filter(
filter_c).paginate(page_no, count)
logger.debug('%d / %d 页 %d / %d 条数据',
pagination.page, pagination.pages,
len(pagination.items), pagination.total)
ret_dic_list = [{
'pl_id':
data.PortfolioInfo.pl_id,
'name':
data.PortfolioInfo.name,
'date_from':
date_2_str(data.PortfolioInfo.date_from),
'date_to':
date_2_str(data.PortfolioInfo.date_to),
'status':
data.PortfolioInfo.status,
'desc':
data.PortfolioInfo.desc,
'create_user_id':
data.PortfolioInfo.create_user_id,
'username':
data.username,
'favorite':
data.favorite,
'trade_date':
date_2_str(data.trade_date),
'rr':
try_2_float(data.rr),
'nav':
try_2_float(data.nav),
'access_type':
data.PortfolioInfo.access_type,
} for data in pagination.items]
ret_dic = {
'page': pagination.page,
'pages': pagination.pages,
'count': len(pagination.items),
'total': pagination.total,
'has_prev': pagination.has_prev,
'has_next': pagination.has_next,
'data': ret_dic_list,
}
return ret_dic
def get_pl_data_list_by_date(_id, status, page_no, count):
"""
获取制定投资组合的成分及权重数据（分页）
status
:param _id:
:param status: latest 最近一次调仓数据, recent 最近几日调仓数据，日期逆序排列
:return:
"""
# logger.info('pl_id=%s, status=%s', _id, status)
# PortfolioData.query.filter(PortfolioData.id == pl_id, PortfolioData.trade_date == 1)
if status == 'latest':
date_cur = db.session.query(func.max(
PortfolioData.trade_date)).filter(
PortfolioData.pl_id == _id).scalar()
# 最新调仓日期
pagination = PortfolioData.query.filter(
PortfolioData.pl_id == _id,
PortfolioData.trade_date == date_cur).paginate(page_no, count)
elif status == 'recent':
pagination = PortfolioData.query.group_by(
PortfolioData.trade_date).filter(
PortfolioData.pl_id == _id).order_by(
PortfolioData.trade_date.desc()).paginate(
page_no, count)
else:
raise KeyError('status = %s 不支持' % status)
logger.debug('%d / %d 页 %d / %d 条数据', pagination.page,
pagination.pages, len(pagination.items), pagination.total)
date_list = [data.trade_date for data in pagination.items]
date_grouped_dic = {}
ret_dic_list = []
if len(date_list) > 0:
# date_grouped_dic 中的 value 为 data_list 实际与 ret_dic_list 中对应日期的 data_list 为同一对象
# 因此可以直接修改
items = db.session.query(PortfolioData).filter(
PortfolioData.pl_id == _id,
PortfolioData.trade_date.in_(date_list)).all()
for data in items:
date_cur = date_2_str(data.trade_date)
if date_cur in date_grouped_dic:
data_list = date_grouped_dic[date_cur]
else:
data_list = []
date_grouped_dic[date_cur] = data_list
ret_dic_list.append({
'trade_date': date_cur,
'data': data_list
})
# 获取资产及资产类别中文名称
asset_name = get_asset_name(data.asset_type, data.asset_code)
# 因为list是引用性数据，直接放入 ret_dic_list
# 对 data_list 的修改直接反应到 ret_dic_list 中去
data_list.append({
'id':
data.id,
'asset_code':
data.asset_code,
'asset_name':
asset_name,
'asset_type':
data.asset_type,
'trade_date':
date_cur,
'weight':
try_2_float(data.weight),
'weight_before':
try_2_float(data.weight_before),
'price_type':
data.price_type,
'direction':
data.direction,
})
ret_dic = {
'page': pagination.page,
'pages': pagination.pages,
'count': len(pagination.items),
'total': pagination.total,
'has_prev': pagination.has_prev,
'has_next': pagination.has_next,
'data': ret_dic_list,
}
# logger.debug(ret_dic)
return ret_dic
def get_pl_data_list(_id, status, page_no, count):
"""
获取制定投资组合的成分及权重数据（分页）
:param _id:
:param status:
:param page_no:
:param count:
:return:
"""
if status == 'latest':
pagination = PortfolioData.query.filter(
PortfolioData.pl_id == _id,
PortfolioData.trade_date == (db.session.query(
func.max(PortfolioData.trade_date)).filter(
PortfolioData.pl_id == _id))).paginate(page_no, count)
elif status == 'recent':
pagination = PortfolioData.query.filter(
PortfolioData.pl_id == _id).order_by(
PortfolioData.trade_date.desc()).paginate(page_no, count)
else:
raise KeyError('status = %s 不支持' % status)
logger.debug('%d / %d 页 %d / %d 条数据', pagination.page,
pagination.pages, len(pagination.items), pagination.total)
date_grouped_dic = {}
ret_dic_list = []
for data in pagination.items:
date_cur = date_2_str(data.trade_date)
if date_cur in date_grouped_dic:
data_list = date_grouped_dic[date_cur]
else:
data_list = []
date_grouped_dic[date_cur] = data_list
ret_dic_list.append({
'trade_date': date_cur,
'data': data_list
})
# 获取资产及资产类别中文名称
asset_name = get_asset_name(data.asset_type, data.asset_code)
# 因为list是引用性数据，直接放入 ret_dic_list
# 对 data_list 的修改直接反应到 ret_dic_list 中去
data_list.append({
'id': data.id,
'asset_code': data.asset_code,
'asset_name': asset_name,
'asset_type': data.asset_type,
'trade_date': date_cur,
'weight': try_2_float(data.weight),
'weight_before': try_2_float(data.weight_before),
'price_type': data.price_type,
'direction': data.direction,
})
ret_dic = {
'page': pagination.page,
'pages': pagination.pages,
'count': len(pagination.items),
'total': pagination.total,
'has_prev': pagination.has_prev,
'has_next': pagination.has_next,
'data': ret_dic_list,
}
# logger.debug(ret_dic)
return ret_dic
def get(self, status):
"""
获取比较列表数据（分页）
"""
args = paginate_parser.parse_args()
page_no = args['page_no']
count = args['count']
user_id = session.get('user_id')
logger.debug('get_cmp_list user_id:%s', user_id)
if status == 'my':
filter_c = PortfolioCompareInfo.create_user_id == user_id
having_c = None
elif status == 'all':
filter_c = or_(PortfolioCompareInfo.create_user_id == user_id,
PortfolioCompareInfo.access_type == 'public')
having_c = None
elif status == 'star':
filter_c = and_(
or_(PortfolioCompareInfo.create_user_id == user_id,
PortfolioCompareInfo.access_type == 'public'),
not_(func.isnull(FavoriteCompare.update_time)))
having_c = None
elif status == 'verified':
filter_c = or_(PortfolioCompareInfo.create_user_id == user_id,
PortfolioCompareInfo.access_type == 'public')
having_c = column('complete_rate') >= 1
elif status == 'unverified':
filter_c = or_(PortfolioCompareInfo.create_user_id == user_id,
PortfolioCompareInfo.access_type == 'public')
having_c = or_(
column('complete_rate').is_(None),
column('complete_rate') < 1)
else:
raise KeyError('status 参数错误 status = %s' % status)
# 整理数据
# logger.debug("data_list_df len:%d", data_list_df.shape[0])
# data_list_df = data_list_df.where(data_list_df.notna(), None)
# data_list = data_list_df.to_dict('record')
# data_table_dic = {'data': data_list}
# logger.debug(data_table_dic)
query = PortfolioCompareInfo.query.outerjoin(
PortfolioCompareResult
).group_by(PortfolioCompareResult.cmp_id).add_columns(
func.count().label('tot_count'),
func.min(
PortfolioCompareResult.trade_date).label('trade_date_min'),
func.max(
PortfolioCompareResult.trade_date).label('trade_date_max'),
func.sum(PortfolioCompareResult.result).label('fit_count'),
(func.sum(PortfolioCompareResult.result) /
func.count()).label('fit_rate'),
((func.max(PortfolioCompareResult.trade_date) -
PortfolioCompareInfo.date_from) /
(PortfolioCompareInfo.date_to - PortfolioCompareInfo.date_from)
).label('complete_rate')).outerjoin(User).add_columns(
User.username).outerjoin(
FavoriteCompare,
and_(
PortfolioCompareInfo.cmp_id == FavoriteCompare.cmp_id,
FavoriteCompare.user_id == user_id)).add_columns(
func.if_(
func.isnull(FavoriteCompare.update_time), 0,
1).label('favorite')).filter(
and_(filter_c,
PortfolioCompareInfo.is_del == 0))
if having_c is None:
pagination = query.paginate(page_no, count)
else:
pagination = query.having(having_c).paginate(page_no, count)
logger.debug('%d / %d 页 %d / %d 条数据', pagination.page,
pagination.pages, len(pagination.items), pagination.total)
ret_dic_list = [{
'cmp_id':
data.PortfolioCompareInfo.cmp_id,
'name':
data.PortfolioCompareInfo.name,
'status':
data.PortfolioCompareInfo.status,
'params':
data.PortfolioCompareInfo.params,
'desc':
data.PortfolioCompareInfo.desc,
'date_from':
date_2_str(data.PortfolioCompareInfo.date_from),
'date_to':
date_2_str(data.PortfolioCompareInfo.date_to),
'trade_date_min':
date_2_str(data.trade_date_min),
'trade_date_max':
date_2_str(data.trade_date_max),
'create_user_id':
data.PortfolioCompareInfo.create_user_id,
'username':
data.username,
'favorite':
data.favorite,
'complete_rate':
try_2_float(data.complete_rate),
} for data in pagination.items]
ret_dic = {
'page': pagination.page,
'pages': pagination.pages,
'count': len(pagination.items),
'total': pagination.total,
'has_prev': pagination.has_prev,
'has_next': pagination.has_next,
'data': ret_dic_list,
}
return ret_dic