def read_signals(cls, model_from=None, field=None, **kw):
"""
query signals from DB
:param model_from:
:param field:属性域
:return:
"""
try:
sql = dict()
if model_from is not None:
sql = {"model_from": model_from}
sql = dict(sql, **kw)
cursor = MODEL_TABLE(cls.location, cls.dbname, 'signals').query(sql, field)
if cursor.count():
data = pd.DataFrame(list(cursor))
# data['date'] = pd.to_datetime(data.date)
# data = cls.merge_time(data)
data = data.sort_values(['open_date'], ascending=False)
data = data.reset_index(drop=True)
# data.drop(['_id', 'classtype'], axis=1, inplace=True)
# data = data.drop_duplicates(['stock_code', 'date', 'time'])
# data.stock_code.astype('int')
cursor.close()
return data
else:
cursor.close()
return pd.DataFrame([])
except Exception:
raise MongoIOError('query signals from db raise a error')
def read_orders(cls, table_name='orders', field=None, **kw):
"""
读取持仓信息，可以读取包括orders,orders_simulated,orders_his三个表的数据
:param table_name:
:param field:
:param kw:
:return:
"""
try:
cursor = MODEL_TABLE(cls.location, cls.dbname,
table_name).query(kw, field)
if cursor.count():
ods = pd.DataFrame(list(cursor))
return ods
return pd.DataFrame()
except Exception:
raise MongoIOError('query orders data from db raise a error')
def read_finance_index(cls, model_from, field=None, **kwargs):
"""
读取模型回测的财务指标数据
:return:
"""
try:
sql = {'model_from': model_from}
sql = dict(sql, **kwargs)
cursor = MODEL_TABLE(cls.location, cls.dbname,
'financeindex').query(sql, field)
if cursor.count():
fis = pd.DataFrame(list(cursor))
fis.drop(['_id', 'classtype'], axis=1, inplace=True)
return fis
return pd.DataFrame()
except Exception:
raise MongoIOError(
'query finance indicators from db raise a error')
def read_his_orders(cls,
model_from,
start_date=None,
end_date=None,
field=None,
**kw):
"""
读取持仓的数据
:param end_date:
:param start_date:
:param model_from:
:param kw:
:return:
"""
try:
sql = {'model_from': model_from}
if start_date is None and end_date is None:
pass
else:
if start_date is not None and end_date is None:
date = {'close_date': {'$gte': start_date}}
elif end_date is not None and start_date is None:
date = {'close_date': {'$lte': end_date}}
else:
date = {
'close_date': {
'$gte': start_date,
'$lte': end_date
}
}
sql = dict(sql, **date)
sql = dict(sql, **kw)
cursor = MODEL_TABLE(cls.location, cls.dbname,
'orders_his').query(sql, field)
if cursor.count():
ods = pd.DataFrame(list(cursor))
# ods.drop(['_id', 'classtype'], axis=1, inplace=True)
return ods
return pd.DataFrame()
except Exception:
raise MongoIOError('query orders data from db raise a error')
def update_signals(cls, condition, **kw):
"""
按condition条件更新K线数据
:param condition: 形如{‘date':datetime.datetime(2018,1,1)}的一个字典
:param kw:形如close=0这样的参数组
:return:
"""
try:
MODEL_TABLE(cls.location, cls.dbname, 'signals').update_batch(condition, kw)
except Exception:
raise MongoIOError('Failed with update by MongoDB')
def remove_orders(cls, table_name='orders', **kw):
"""
移除订单
:param table_name:
:param kw:
:return:
"""
try:
r = MODEL_TABLE(cls.location, cls.dbname, table_name).remove(kw)
return r
except Exception:
raise MongoIOError('Failed with removing data ')
def insert_trade_menu(cls, menus):
"""
记录日交易所获得的收益
:param menus:
:return:
"""
try:
if len(menus):
d = menus.to_dict(orient='records')
MODEL_TABLE(cls.location, cls.dbname,
'trademenu').insert_batch(d)
except Exception:
raise MongoIOError('Failed with insert data by MongoDB')
def insert_finance_index(cls, fis):
"""
保存模型回测过程中的一些财务指标
:param fis:
:return:
"""
try:
if len(fis):
d = fis.to_dict(orient='records')
MODEL_TABLE(cls.location, cls.dbname,
'financeindex').insert_batch(d)
except Exception:
raise MongoIOError('Failed with insert data by MongoDB')
def read_orders_(cls, model_from=None, **kw):
"""
读取持仓的数据,一个特殊的实现
:param model_from:
:param kw:
:return:
"""
try:
sql = dict()
if model_from is not None:
sql = {'model_from': model_from}
sql = dict(sql, **kw)
cursor = MODEL_TABLE(cls.location, cls.dbname, 'orders').query(sql)
if cursor.count():
ods = pd.DataFrame(list(cursor))
ods = cls.merge_time(ods, 'date', 'time')
ods = cls.merge_time(ods, 'max_pst_date', 'pst_time')
ods.drop(['_id', 'classtype'], axis=1, inplace=True)
return ods
return pd.DataFrame()
except Exception:
raise MongoIOError('query orders data from db raise a error')
def update_orders(cls, condition, table_name='orders', **kw):
"""
按condition条件更新K订单数据
:param table_name:
:param condition: 形如{‘date':datetime.datetime(2018,1,1)}的一个字典
:param kw:形如close=0这样的参数组
:return:
"""
try:
MODEL_TABLE(cls.location, cls.dbname,
table_name).update_batch(condition, kw)
except Exception:
raise MongoIOError('Failed with update by MongoDB')
def remove_signals(cls, model_from, **kw):
"""
根据条件删除signals表当中的信号，其实只是将信号从signals转移到了信号历史
表当中
:param model_from:
:param kw:
:return:
"""
try:
sql = {'model_from': model_from}
sql = dict(sql, **kw)
MODEL_TABLE(cls.location, cls.dbname, 'signals').remove(sql)
except Exception:
raise MongoIOError('Failed with removing data ')
def insert_risk(cls, risks):
"""
插入风险参数
:param risks:
:return:
"""
try:
if len(risks):
dit = []
for i, row in risks.iterrows():
r = dict(row)
dit.append(r)
MODEL_TABLE(cls.location, cls.dbname, 'risk').insert_batch(dit)
except Exception:
raise MongoIOError('Failed with insert data by MongoDB')
def insert_pst(cls, pst):
"""
插入仓位
:param pst:
:return:
"""
try:
if len(pst):
dit = []
for i, row in pst.iterrows():
r = dict(row)
dit.append(r)
MODEL_TABLE(cls.location, cls.dbname,
'position').insert_batch(dit)
except Exception:
raise MongoIOError('Failed with insert data by MongoDB')
def insert_risk_pst(cls, rps):
"""
插入风险仓位数据
:param rps: a DataFrame
:return:
"""
try:
if len(rps):
dit = []
for i, row in rps.iterrows():
r = dict(row)
dit.append(r)
# print(dit[0])
MODEL_TABLE(cls.location, cls.dbname,
'risk_and_position').insert_batch(dit)
except Exception:
raise MongoIOError('Failed with insert data by MongoDB')
def insert_his_rmds(cls, rmds):
"""
插入为账户推荐的历史
:param rmds:
:return:
"""
try:
if len(rmds):
dit = []
for i, row in rmds.iterrows():
r = dict(row)
dit.append(r)
# print(dit[0])
MODEL_TABLE(cls.location, cls.dbname,
'rmds_his').insert_batch(dit)
except Exception:
raise MongoIOError('Failed with insert data by MongoDB')
def read_RRADS(cls, **kwargs):
"""
Regularly report the appointment disclosure schedule
定期报告预约披露时间表
:return:
"""
try:
sql = kwargs
cursor = MODEL_TABLE(cls.location, cls.dbname, 'RRADS').query(sql)
rds = list(cursor)
if len(rds):
rds = pd.DataFrame(rds)
rds.drop('classtype', axis=1, inplace=True)
# rds = rds.sort_values(['date'], ascending=False)
return rds
return pd.DataFrame()
except Exception:
raise MongoIOError('query orders data from db raise a error')
def insert_client_info(cls, client):
"""
插入订阅账户信息
:param client:
:return:
"""
try:
if len(client):
# 客户信息表必须包含的字段，缺省的填0
col = ['client_no', 'model_from', 'update', 'status', 'Rr']
if set(col) <= set(client.columns):
d = client.to_dict(orient='records')
MODEL_TABLE(cls.location, cls.dbname,
'clients').insert_batch(d)
else:
raise Exception('lost must field')
except Exception:
raise MongoIOError('Failed with insert data by MongoDB')
def insert_his_signals(cls, pst):
"""
把通过模型收益计算方法得到的交易清单存入数据库
:param pst:
:return:
"""
try:
if len(pst):
# 信号历史表必须包含的字段，缺省的填0
# 证券代码的字段名可以不一样，不做统一规范
col = ['open_date', 'open_price', 'close_date', 'close_price', 'type', 'reason', 'profit', 'model_from']
if set(col) <= set(pst.columns):
d = pst.to_dict(orient='records')
MODEL_TABLE(cls.location, cls.dbname, 'signals_his').insert_batch(d)
else:
raise Exception('lost must field')
except Exception:
raise MongoIOError('Failed with insert data by MongoDB')
def read_trade_menu(cls,
model_from,
start_date=None,
end_date=None,
version=None,
**kw):
"""
读取模型收益数据，读取到的数据主要包括某种策略一个或一段时间所对应的收益
:param model_from:
:param start_date:
:param end_date:
:param version:
:param kw:
:return:
"""
try:
sql = {'model_from': model_from}
if version is not None:
sql['version'] = version
if start_date is None and end_date is None:
pass
else:
if start_date is not None and end_date is None:
date = analyzer("date >= {s}".format(s=start_date))
elif end_date is not None and start_date is None:
date = analyzer("date <= {e}".format(e=end_date))
else:
date = analyzer("date >= {s} and data <= {e}".format(
s=start_date, e=end_date))
sql = dict(sql, **date)
sql = dict(sql, **kw)
cursor = MODEL_TABLE(cls.location, cls.dbname,
'trademenu').query(sql)
menus = list(cursor)
if len(menus):
menus = pd.DataFrame(menus)
menus = menus.sort_values(['date'], ascending=True)
menus = menus.reset_index(drop=True)
menus.drop(['_id', 'classtype'], axis=1, inplace=True)
return menus
return pd.DataFrame()
except Exception:
raise MongoIOError('query trade menu from db raise a error')
def insert_signals(cls, signals):
"""
The signal points of buying insert to DB
:param signals: a df of signal which come from someone models, and the df must have columns of
stock_code,date,buy_price,model_from
:return:
"""
try:
if len(signals):
# 信号表必须包含的字段，缺省的填0
# 证券代码的字段名可以不一样，不做统一规范
col = ['open_date', 'open_price', 'confidence', 'type', 'version', 'model_from']
if set(col) <= set(signals.columns):
d = signals.to_dict(orient='records')
MODEL_TABLE(cls.location, cls.dbname, 'signals').insert_batch(d)
else:
raise Exception('lost must field')
except Exception:
raise MongoIOError('Failed with insert data by DB')
def insert_account_info(cls, acts):
"""
添加账户-订阅
:return:
"""
try:
col = [
'account', 'initial_balance', 'balance', 'date',
'subscription', 'model_scale', 'role'
]
if set(col) <= set(acts.columns):
d = acts.to_dict(orient='records')
MODEL_TABLE(cls.location, cls.dbname,
'accounts').insert_batch(d)
else:
raise Exception('lost must field')
except Exception:
raise MongoIOError('Failed with insert data by DB')
pass
def order_send(cls, orders):
"""
插入需要购买的数据
:param orders: a DataFrame
:return:
"""
try:
if len(orders):
# 订单表必须包含的字段，缺省的填0
# 证券代码的字段名可以不一样，不做统一规范
# 'open_date', 'open_price', 'confidence', 'type', 'version', 'model_from'
col = []
if set(col) <= set(orders.columns):
d = orders.to_dict(orient='records')
MODEL_TABLE(cls.location, cls.dbname,
'orders').insert_batch(d)
else:
raise Exception('lost must field')
except Exception:
raise MongoIOError('Failed with insert data by MongoDB')
def insert_his_orders(cls, orders):
"""
插入交易历史数据
:param orders:
:return:
"""
try:
if len(orders):
# 订单历史表必须包含的字段，缺省的填0
# 证券代码的字段名可以不一样，不做统一规范
col = [
'open_date', 'open_price', 'close_date', 'close_price',
'type', 'reason', 'profit', 'model_from'
]
if set(col) <= set(orders.columns):
d = orders.to_dict(orient='records')
MODEL_TABLE(cls.location, cls.dbname,
'orders_his').insert_batch(d)
else:
raise Exception('lost must field')
except Exception:
raise MongoIOError('Failed with insert data by MongoDB')
def open(cls, orders):
"""
插入开仓数据
:param orders:
:return:
"""
try:
if len(orders):
# 订单表必须包含的字段，缺省的填0
# 证券代码的字段名可以不一样，不做统一规范
col = [
'open_date', 'open_price', 'type', 'model_from', 'Rr',
'exp_open_vol', 'client_no', 'status'
]
if set(col) <= set(orders.columns):
d = orders.to_dict(orient='records')
MODEL_TABLE(cls.location, cls.dbname,
'orders').insert_batch(d)
else:
raise Exception('lost must field')
except Exception:
raise MongoIOError('Failed with insert data by MongoDB')
def read_client_info(cls, model_from, field=None, **kw):
"""
:param model_from:
:return:
"""
try:
sql = {'model_from': model_from}
sql = dict(sql, **kw)
cursor = MODEL_TABLE(cls.location, cls.dbname,
'clients').query(sql, field)
if cursor.count():
clis = pd.DataFrame(list(cursor))
cursor.close()
return clis
cursor.close()
return pd.DataFrame()
except Exception as e:
ExceptionInfo(e)
return pd.DataFrame()
def read_risk_pst(cls, stock_code=None, date=None, **kw):
"""
读取仓位数据
:return:
"""
try:
sql = dict()
if stock_code is not None:
sql['stock_code'] = stock_code
if date is not None:
sql = dict(sql, **analyzer("date = {d}".format(d=date)))
sql = dict(sql, **kw)
cursor = MODEL_TABLE(cls.location, cls.dbname,
'risk_and_position').query(sql)
rps = list(cursor)
if len(rps):
rps = pd.DataFrame(rps)
rps.drop(['_id', 'classtype'], axis=1, inplace=True)
return rps
return pd.DataFrame()
except Exception:
raise MongoIOError('query risk and position from db raise a error')
def read_account_info(cls, model_from, field=None, **kw):
"""
读取现有的账户信息
:return:
"""
try:
sql = {'model_from': model_from}
sql = dict(sql, **kw)
cursor = MODEL_TABLE(cls.location, cls.dbname,
'asset').query(sql, field)
if cursor.count():
ai = pd.DataFrame(list(cursor))
ai.drop(['classtype'], axis=1, inplace=True)
ai = ai.sort_values(['date'], ascending=False)
ai = ai.drop_duplicates(['client_no'], keep='first')
cursor.close()
return ai
cursor.close()
return pd.DataFrame()
except Exception as e:
ExceptionInfo(e)
return pd.DataFrame()
def insert_orders(cls, orders, table_name='orders'):
"""
插入订单数据，默认插入到orders表中，这个表可能会参与实际交易。
也可以插入到orders_simulated表中，这只是用于模拟交易的
:param orders:
:param table_name:
:return:
"""
try:
if len(orders):
# 订单表必须包含的字段，缺省的填0
# 证券代码的字段名可以不一样，不做统一规范
col = [
'open_date', 'open_price', 'confidence', 'type', 'version',
'model_from', 'status'
]
if set(col) <= set(orders.columns):
d = orders.to_dict(orient='records')
MODEL_TABLE(cls.location, cls.dbname,
table_name).insert_batch(d)
else:
raise Exception('lost must field')
except Exception:
raise MongoIOError('Failed with insert data by MongoDB')
def read_risk(self, stock_code=None, date=None, **kw):
"""
读取风险参数
:param stock_code:
:param date:
:param kw:
:return:
"""
try:
sql = dict()
if stock_code is not None:
sql['stock_code'] = stock_code
if date is not None:
sql = dict(sql, **analyzer("date = {d}".format(d=date)))
sql = dict(sql, **kw)
cursor = MODEL_TABLE(self.location, self.dbname, 'risk').query(sql)
rks = list(cursor)
if len(rks):
rks = pd.DataFrame(rks)
rks.drop(['_id', 'classtype'], axis=1, inplace=True)
return rks
return pd.DataFrame()
except Exception:
raise MongoIOError('query risk from db raise a error')
