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