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)