def save(gid, risk, df):
# fmt_columns = ['mz_nav', 'mz_inc']
# fmt_precision = 6
# if not df.empty:
# df = database.number_format(df, fmt_columns, fmt_precision)
#
# 保存择时结果到数据库
#
db = database.connection('asset')
t2 = Table('mz_highlow_limit', MetaData(bind=db), autoload=True)
columns = [literal_column(c) for c in (df.index.names + list(df.columns))]
s = select(columns,
(t2.c.mz_highlow_id == gid)).where(t2.c.mz_risk == risk)
df_old = pd.read_sql(
s,
db,
index_col=['globalid', 'mz_highlow_id', 'mz_risk', 'mz_date'],
parse_dates=['mz_date'])
# if not df_old.empty:
# df_old = database.number_format(df_old, fmt_columns, fmt_precision)
# 更新数据库
# print df_new.head()
# print df_old.head()
database.batch(db, t2, df, df_old, timestamp=False)
def save(gid, xtype, df):
fmt_columns = ['on_return_value']
fmt_precision = 8
if not df.empty:
df = database.number_format(df, fmt_columns, fmt_precision)
#
# 保存择时结果到数据库
#
db = database.connection('asset')
t2 = Table('on_online_contrib', MetaData(bind=db), autoload=True)
columns = [literal_column(c) for c in (df.index.names + list(df.columns))]
s = select(columns,
(t2.c.on_online_id == gid)).where(t2.c.on_type == xtype)
df_old = pd.read_sql(s,
db,
index_col=[
'on_online_id', 'on_type', 'on_date',
'on_fund_id', 'on_return_type'
],
parse_dates=['on_date'])
if not df_old.empty:
df_old = database.number_format(df_old, fmt_columns, fmt_precision)
# 更新数据库
# print df_new.head()
# print df_old.head()
# pdb.set_trace()
database.batch(db, t2, df, df_old, timestamp=True)
def save(gid, criteria_id, df):
fmt_columns = ['is_value']
fmt_precision = 6
if not df.empty:
df = database.number_format(df, fmt_columns, fmt_precision)
#
# 保存择时结果到数据库
#
db = database.connection('asset')
t2 = Table('is_investor_criteria', MetaData(bind=db), autoload=True)
columns = [literal_column(c) for c in (df.index.names + list(df.columns))]
s = select(
columns,
(t2.c.is_investor_id == gid)).where(t2.c.is_criteria_id == criteria_id)
df_old = pd.read_sql(
s,
db,
index_col=['is_investor_id', 'is_criteria_id', 'is_date'],
parse_dates=['is_date'])
if not df_old.empty:
df_old = database.number_format(df_old, fmt_columns, fmt_precision)
# 更新数据库
# print df_new.head()
# print df_old.head()
database.batch(db, t2, df, df_old, timestamp=False)
def update_stock_factor_specific_return(df_sret, last_date=None):
if last_date is None:
db = database.connection('asset')
Session = sessionmaker(bind=db)
session = Session()
record = session.query(
func.max(stock_factor_specific_return.trade_date)).first()
last_date = record[0].strftime(
'%Y-%m-%d') if record[0] is not None else '1900-01-01'
session.commit()
session.close()
df_sret = df_sret[df_sret.index >= last_date]
db = database.connection('asset')
Session = sessionmaker(bind=db)
session = Session()
session.query(stock_factor_specific_return).filter(
stock_factor_specific_return.trade_date >= last_date).delete()
session.commit()
session.close()
df_sret = df_sret.stack()
df_sret = df_sret.reset_index()
df_sret.columns = ['trade_date', 'stock_id', 'sret']
df_sret = df_sret.set_index(['stock_id', 'trade_date'])
db = database.connection('asset')
t = Table('stock_factor_specific_return', MetaData(bind=db), autoload=True)
database.batch(db, t, df_sret, pd.DataFrame())
def save(gid, df):
fmt_columns = ['ra_fund_ratio']
fmt_precision = 4
if not df.empty:
df['ra_fund_type'] = df['ra_fund_type'].astype(int)
df = database.number_format(df, fmt_columns, fmt_precision)
#
# 保存择时结果到数据库
#
db = database.connection('asset')
t2 = Table('ra_portfolio_pos', MetaData(bind=db), autoload=True)
columns = [literal_column(c) for c in (df.index.names + list(df.columns))]
s = select(columns, (t2.c.ra_portfolio_id == gid))
df_old = pd.read_sql(
s,
db,
index_col=['ra_portfolio_id', 'ra_date', 'ra_pool_id', 'ra_fund_id'],
parse_dates=['ra_date'])
if not df_old.empty:
df_old = database.number_format(df_old, fmt_columns, fmt_precision)
# 更新数据库
# print df_new.head()
# print df_old.head()
database.batch(db, t2, df, df_old, timestamp=True)
def batch(df_new, df_old):
t = Table('wechat_keywords', metadata, autoload=True)
fmt_columns = []
fmt_precision = 4
if not df_new.empty:
df_new = database.number_format(df_new, fmt_columns, fmt_precision)
if not df_old.empty:
df_old = database.number_format(df_old, fmt_columns, fmt_precision)
database.batch(db, t, df_new, df_old)
def batch(df_new, df_old):
t = Table('rpt_srrc', metadata, autoload=True)
fmt_columns = ['rp_amount_firstsub', 'rp_amount_resub', \
'rp_amount_redeem', 'rp_amount_aum']
fmt_precision = 2
if not df_new.empty:
df_new = database.number_format(df_new, fmt_columns, fmt_precision)
if not df_old.empty:
df_old = database.number_format(df_old, fmt_columns, fmt_precision)
database.batch(db, t, df_new, df_old)
def batch(df_new, df_old):
t = Table('rpt_srrc_rolling', metadata, autoload=True)
fmt_columns = ['rp_user_redeem_ratio', 'rp_user_resub_ratio', \
'rp_amount_redeem_ratio', 'rp_amount_resub_ratio']
fmt_precision = 4
if not df_new.empty:
df_new = database.number_format(df_new, fmt_columns, fmt_precision)
if not df_old.empty:
df_old = database.number_format(df_old, fmt_columns, fmt_precision)
database.batch(db, t, df_new, df_old)
def save(_id, df):
db = database.connection('asset')
t2 = Table('rm_riskmgr_mgarch_signal', MetaData(bind=db), autoload=True)
columns = [literal_column(c) for c in (df.index.names + list(df.columns))]
s = select(columns, (t2.c.rm_riskmgr_id == _id))
df_old = pd.read_sql(
s,
db,
index_col=['rm_riskmgr_id', 'rm_date', 'target_rm_riskmgr_id'],
parse_dates=['rm_date'])
database.batch(db, t2, df, df_old, timestamp=True)
def save(id_, df):
db = database.connection('asset')
metadata = MetaData(bind=db)
t = Table('ra_bl_view', metadata, autoload=True)
columns = [literal_column(c) for c in (df.index.names + list(df.columns))]
s = select(columns).where(t.c.globalid == id_)
df_old = pd.read_sql(s,
db,
index_col=['bl_date', 'globalid', 'bl_index_id'],
parse_dates=['bl_date'])
database.batch(db, t, df, df_old, timestamp=True)
def save(gid, df):
#
# 保存择时结果到数据库
#
db = database.connection('asset')
t2 = Table('mz_highlow', MetaData(bind=db), autoload=True)
columns = [literal_column(c) for c in (df.index.names + list(df.columns))]
s = select(columns, (t2.c.globalid == gid))
df_old = pd.read_sql(s, db, index_col=['globalid'])
# 更新数据库
# print df_new.head()
# print df_old.head()
database.batch(db, t2, df, df_old, timestamp=False)
def save(gids, df):
#
# 保存择时结果到数据库
#
db = database.connection('asset')
t2 = Table('ra_portfolio_argv', MetaData(bind=db), autoload=True)
columns = [literal_column(c) for c in (df.index.names + list(df.columns))]
s = select(columns, (t2.c.ra_portfolio_id.in_(gids)))
df_old = pd.read_sql(s, db, index_col=['ra_portfolio_id', 'ra_key'])
# 更新数据库
# print df_new.head()
# print df_old.head()
database.batch(db, t2, df, df_old, timestamp=True)
def save(gid, df):
#
# 保存择时结果到数据库
#
db = database.connection('mapi')
t2 = Table('fp_da_question', MetaData(bind=db), autoload=True)
columns = [literal_column(c) for c in (df.index.names + list(df.columns))]
s = select(columns, (t2.c.fp_nare_id == gid))
df_old = pd.read_sql(s, db, index_col=['globalid'])
# 更新数据库
print df.head()
print "\n"
print df_old.head()
database.batch(db, t2, df.head(), df_old.head(), timestamp=True)
def save(_id, df):
fmt_columns = ['var_2d', 'var_3d', 'var_5d']
fmt_precision = 6
if not df.empty:
df = database.number_format(df, fmt_columns, fmt_precision)
db = database.connection('asset')
t2 = Table('rm_riskmgr_vars', MetaData(bind=db), autoload=True)
columns = [literal_column(c) for c in (df.index.names + list(df.columns))]
s = select(columns, (t2.c.index_id == _id))
df_old = pd.read_sql(s,
db,
index_col=['index_id', 'ix_date'],
parse_dates=['ix_date'])
if not df_old.empty:
df_old = database.number_format(df_old, fmt_columns, fmt_precision)
database.batch(db, t2, df, df_old, timestamp=True)
def save(gid, df):
fmt_columns = ['mz_risk']
fmt_precision = 2
if not df.empty:
df = database.number_format(df, fmt_columns, fmt_precision)
#
# 保存择时结果到数据库
#
db = database.connection('asset')
t2 = Table('mz_highlow_alloc', MetaData(bind=db), autoload=True)
columns = [literal_column(c) for c in (df.index.names + list(df.columns))]
s = select(columns, (t2.c.mz_highlow_id == gid))
df_old = pd.read_sql(s, db, index_col=['globalid'])
if not df_old.empty:
df_old = database.number_format(df_old, fmt_columns, fmt_precision)
# 更新数据库
# print df_new.head()
# print df_old.head()
database.batch(db, t2, df, df_old, timestamp=True)
def save(gid, df):
fmt_columns = ['mz_return', 'mz_risk', 'mz_sharpe']
fmt_precision = 6
if not df.empty:
df = database.number_format(df, fmt_columns, fmt_precision)
#
# 保存择时结果到数据库
#
db = database.connection('asset')
t2 = Table('mz_markowitz_sharpe', MetaData(bind=db), autoload=True)
columns = [literal_column(c) for c in (df.index.names + list(df.columns))]
s = select(columns, (t2.c.mz_markowitz_id == gid))
df_old = pd.read_sql(s, db, index_col=['mz_markowitz_id', 'mz_date'], parse_dates=['mz_date'])
if not df_old.empty:
df_old = database.number_format(df_old, fmt_columns, fmt_precision)
# 更新数据库
# print df_new.head()
# print df_old.head()
database.batch(db, t2, df, df_old, timestamp=False)
def save(gids, df):
fmt_columns = ['mz_upper_limit', 'mz_lower_limit', 'mz_sum1_limit', 'mz_sum2_limit']
fmt_precision = 4
if not df.empty:
df = database.number_format(df, fmt_columns, fmt_precision)
#
# 保存择时结果到数据库
#
db = database.connection('asset')
t2 = Table('mz_markowitz_asset', MetaData(bind=db), autoload=True)
columns = [literal_column(c) for c in (df.index.names + list(df.columns))]
s = select(columns, (t2.c.mz_markowitz_id.in_(gids)))
df_old = pd.read_sql(s, db, index_col=['mz_markowitz_id', 'mz_markowitz_asset_id'])
if not df_old.empty:
df_old = database.number_format(df_old, fmt_columns, fmt_precision)
# 更新数据库
# print df_new.head()
# print df_old.head()
database.batch(db, t2, df, df_old, timestamp=True)
def update_exposure(sf, last_date=None):
sf_id = sf.factor_id
exposure = sf.exposure
if last_date is None:
db = database.connection('asset')
Session = sessionmaker(bind=db)
session = Session()
record = session.query(func.max(
stock_factor_exposure.trade_date)).filter(
stock_factor_exposure.sf_id == sf_id).first()
last_date = record[0].strftime(
'%Y-%m-%d') if record[0] is not None else '1900-01-01'
session.commit()
session.close()
exposure = exposure[exposure.index >= last_date]
db = database.connection('asset')
Session = sessionmaker(bind=db)
session = Session()
session.query(stock_factor_exposure).filter(
stock_factor_exposure.trade_date >= last_date).filter(
stock_factor_exposure.sf_id == sf_id).delete()
session.commit()
session.close()
df_new = exposure.stack()
df_new = df_new.reset_index()
df_new['sf_id'] = sf.factor_id
df_new.columns = ['trade_date', 'stock_id', 'exposure', 'sf_id']
df_new = df_new.set_index(['stock_id', 'sf_id', 'trade_date'])
db = database.connection('asset')
t = Table('stock_factor_exposure', MetaData(bind=db), autoload=True)
database.batch(db, t, df_new, pd.DataFrame())
def batch(df_new, df_old):
t = Table('rpt_srrc_apportion', metadata, autoload=True)
database.batch(db, t, df_new, df_old)
def batch(df_new, df_old):
t = Table('rpt_retention_type', metadata, autoload=True)
database.batch(db, t, df_new, df_old)
