def _retrieve_kline(self, table, sids, fields, start_date, end_date): """ retrieve specific categroy asset """ tbl = self.metadata.tables['%s_price' % table] orm = sa.select([tbl.c.trade_dt, tbl.c.sid, sa.cast(tbl.c.open, sa.Numeric(10, 2)).label('open'), sa.cast(tbl.c.high, sa.Numeric(10, 2)).label('high'), sa.cast(tbl.c.low, sa.Numeric(10, 3)).label('low'), sa.cast(tbl.c.close, sa.Numeric(12, 2)).label('close'), sa.cast(tbl.c.volume, sa.Numeric(15, 0)).label('volume'), sa.cast(tbl.c.amount, sa.Numeric(15, 2)).label('amount')]). \ where(tbl.c.trade_dt.between(start_date, end_date)) rp = self.engine.execute(orm) arrays = [[ r.trade_dt, r.sid, r.open, r.high, r.low, r.close, r.volume, r.amount ] for r in rp.fetchall()] frame = pd.DataFrame(arrays, columns=[ 'trade_dt', 'sid', 'open', 'high', 'low', 'close', 'volume', 'amount' ]) frame.drop_duplicates(ignore_index=True, inplace=True) frame = frame[frame['sid'].isin(sids)] frame.set_index('sid', inplace=True) kline = self._adjust_frame_type(frame) unpack_kline = unpack_df_to_component_dict(kline.loc[:, fields], 'trade_dt') return unpack_kline
def _retrieve_ownership(self): tbl = metadata.tables['ownership'] sql = sa.select([tbl.c.sid, tbl.c.ex_date, tbl.c.general, tbl.c.float]) # sql = sa.select([tbl.c.sid, tbl.c.ex_date, tbl.c.general, tbl.c.float]).where(tbl.c.sid == '000002') rp = engine.execute(sql) frame = pd.DataFrame([[r.sid, r.ex_date, r.general, r.float] for r in rp.fetchall()], columns=['sid', 'date', 'general', 'float']) frame.set_index('sid', inplace=True) frame.replace('--', 0.0, inplace=True) frame = self._adjust_frame_type(frame) unpack_frame = unpack_df_to_component_dict(frame) return unpack_frame
def _get_rights_with_ex_date(self, sessions): sdate, edate = sessions sql = sa.select([self.equity_rights.c.sid, self.equity_rights.c.ex_date, sa.cast(self.equity_rights.c.rights_bonus, sa.Numeric(5, 2)), sa.cast(self.equity_rights.c.rights_price, sa.Numeric(5, 2))]).\ where(self.equity_rights.c.pay_date.between(sdate, edate)) rp = self.engine.execute(sql) rights = pd.DataFrame( rp.fetchall(), columns=['sid', 'ex_date', 'rights_bonus', 'rights_price']) rights.set_index('sid', inplace=True) adjust_rights = self._adjust_frame_type(rights) unpack_rights = unpack_df_to_component_dict(adjust_rights, 'ex_date') return unpack_rights
def _get_dividends_with_ex_date(self, sessions): sdate, edate = sessions sql_dialect = sa.select([self.equity_splits.c.sid, self.equity_splits.c.ex_date, sa.cast(self.equity_splits.c.sid_bonus, sa.Numeric(5, 2)), sa.cast(self.equity_splits.c.sid_transfer, sa.Numeric(5, 2)), sa.cast(self.equity_splits.c.bonus, sa.Numeric(5, 2))]).\ where(and_(self.equity_splits.c.pay_date.between(sdate, edate), self.equity_splits.c.progress.like('实施'))) rp = self.engine.execute(sql_dialect) divdends = pd.DataFrame( rp.fetchall(), columns=['sid', 'ex_date', 'sid_bonus', 'sid_transfer', 'bonus']) divdends.set_index('sid', inplace=True) adjust_divdends = self._adjust_frame_type(divdends) unpack_divdends = unpack_df_to_component_dict(adjust_divdends, 'ex_date') return unpack_divdends
def load_raw_arrays(self, dts, assets, fields=None): sids = [a.sid for a in assets] table = self.metadata.tables['unfreeze'] sql = select([table.c.sid, table.c.declared_date, table.c.release_type, cast(table.c.zb, Numeric(10, 5)), ]).\ where(table.c.declared_date.between(dts[0], dts[1])) frame = pd.DataFrame( self.engine.execute(sql).fetchall(), columns=['sid', 'declared_date', 'release_type', 'zb']) frame.set_index('sid', inplace=True) frame.drop_duplicates(inplace=True) frame_dct = unpack_df_to_component_dict(frame, 'declared_date') frame_dct = valmap(lambda x: x.loc[:, fields] if fields else x, frame_dct) release_frame = keyfilter(lambda x: x in sids, frame_dct) return release_frame
def load_raw_arrays(self, dts, assets, fields=None): sids = [a.sid for a in assets] table = self.metadata.tables['ownership'] ins = sa.select([table.c.sid, table.c.declared_date, table.c.ex_date, sa.cast(table.c.general, sa.Numeric(20, 3)), table.c.float, table.c.manager, table.c.strict]).\ where(table.c.declared_date.between(dts[0], dts[1])) frame = pd.DataFrame(self.engine.execute(ins).fetchall(), columns=[ 'sid', 'declared_date', 'ex_date', 'general', 'float', 'manager', 'strict' ]) frame.set_index('sid', inplace=True) frame.drop_duplicates(inplace=True) frame_dct = unpack_df_to_component_dict(frame, 'declared_date') frame_dct = valmap(lambda x: x.loc[:, fields] if fields else x, frame_dct) ownership_frame = keyfilter(lambda x: x in sids, frame_dct) return ownership_frame
def load_raw_arrays(self, dts, assets, fields=None): sids = [a.sid for a in assets] # 获取数据 table = self.metadata.tables['massive'] sql = select([ table.c.declared_date, table.c.sid, cast(table.c.bid_price, Numeric(10, 2)), cast(table.c.discount, Numeric(10, 5)), cast(table.c.bid_volume, Integer), table.c.buyer, table.c.seller, table.c.cjeltszb ]).where(table.c.declared_date.between(dts[0], dts[1])) frame = pd.DataFrame(self.engine.execute(sql).fetchall(), columns=[ 'declared_date', 'sid', 'bid_price', 'discount', 'bid_volume', 'buyer', 'seller', 'cjeltszb' ]) frame.set_index('sid', inplace=True) frame.drop_duplicates(inplace=True) frame_dct = unpack_df_to_component_dict(frame, 'declared_date') frame_dct = valmap(lambda x: x.loc[:, fields] if fields else x, frame_dct) massive_frame = keyfilter(lambda x: x in sids, frame_dct) return massive_frame
def load_raw_arrays(self, dts, assets, fields=None): """股东持仓变动""" sids = [a.sid for a in assets] table = self.metadata.tables['holder'] sql = select([ table.c.sid, table.c.declared_date, table.c.股东, table.c.方式, cast(table.c.变动股本, Numeric(10, 2)), cast(table.c.总持仓, Integer), cast(table.c.占总股本比, Numeric(10, 5)), cast(table.c.总流通股, Integer), cast(table.c.占流通比, Numeric(10, 5)) ]).where(table.c.declared_date.between(dts[0], dts[1])) frame = pd.DataFrame(self.engine.execute(sql).fetchall(), columns=[ 'sid', 'declared_date', '股东', '方式', '变动股本', '总持仓', '占总股本比', '总流通股', '占流通比' ]) frame.set_index('sid', inplace=True) frame.drop_duplicates(inplace=True) frame_dct = unpack_df_to_component_dict(frame, 'declared_date') frame_dct = valmap(lambda x: x.loc[:, fields] if fields else x, frame_dct) holder_frame = keyfilter(lambda x: x in sids, frame_dct) return holder_frame