def __init__(self,
security_type=SecurityType.stock,
exchanges=['sh', 'sz'],
codes=None,
batch_size=10,
force_update=False,
sleeping_time=10) -> None:
super().__init__(batch_size=batch_size,
force_update=force_update,
sleeping_time=sleeping_time)
assert self.meta_provider is not None
assert self.meta_schema is not None
self.meta_category = get_store_category(data_schema=self.meta_schema)
# setup the securities you want to record
self.security_type = security_type
self.exchanges = exchanges
self.codes = codes
self.meta_session = get_db_session(provider=self.meta_provider,
store_category=self.meta_category)
# init the security list
self.securities = get_securities(session=self.meta_session,
security_type=self.security_type,
exchanges=self.exchanges,
codes=self.codes,
return_type='domain',
provider=self.meta_provider)
def __init__(self,
batch_size=10,
force_update=False,
sleeping_time=10) -> None:
"""
:param batch_size:batch size to saving to db
:type batch_size:int
:param force_update: whether force update the data even if it exists
:type force_update:bool
:param sleeping_time:sleeping seconds for recoding loop
:type sleeping_time:int
"""
assert self.provider is not None
assert self.data_schema is not None
self.store_category = get_store_category(data_schema=self.data_schema)
self.batch_size = batch_size
self.force_update = force_update
self.sleeping_time = sleeping_time
# using to do db operations
self.session = get_db_session(provider=self.provider,
store_category=self.store_category)
def get_data(data_schema,
security_id=None,
codes=None,
level=None,
provider='eastmoney',
columns=None,
return_type='df',
start_timestamp=None,
end_timestamp=None,
filters=None,
session=None,
order=None,
limit=None):
# assert (security_id is None) != (codes is None)
if isinstance(data_schema, StockKdataBase):
assert level != None
local_session = False
if not session:
store_category = get_store_category(data_schema)
session = get_db_session(store_category=store_category)
local_session = True
try:
if columns:
query = session.query(*columns)
else:
query = session.query(data_schema)
if security_id:
query = query.filter(data_schema.security_id == security_id)
if codes:
query = query.filter(data_schema.code.in_(codes))
if isinstance(data_schema, StockKdataBase):
query = query.filter(data_schema.level == level)
query = common_filter(query,
data_schema=data_schema,
start_timestamp=start_timestamp,
end_timestamp=end_timestamp,
filters=filters,
order=order,
limit=limit,
provider=provider)
if return_type == 'df':
return pd.read_sql(query.statement, query.session.bind)
elif return_type == 'domain':
return query.all()
elif return_type == 'dict':
return [item.to_json() for item in query.all()]
except Exception:
raise
finally:
if local_session:
session.close()
def get_securities(security_type='stock',
exchanges=None,
codes=None,
columns=None,
return_type='df',
session=None,
start_timestamp=None,
end_timestamp=None,
filters=None,
order=None,
limit=None,
provider='eastmoney'):
local_session = False
data_schema = get_security_schema(security_type)
store_category = get_store_category(data_schema=data_schema)
if not session:
session = get_db_session(provider=provider,
store_category=store_category)
local_session = True
if not order:
order = data_schema.code.asc()
try:
if columns:
query = session.query(*columns)
else:
query = session.query(data_schema)
# filters
if exchanges:
query = query.filter(data_schema.exchange.in_(exchanges))
if codes:
query = query.filter(data_schema.code.in_(codes))
query = common_filter(query,
data_schema=data_schema,
start_timestamp=start_timestamp,
end_timestamp=end_timestamp,
filters=filters,
order=order,
limit=limit)
if return_type == 'df':
# TODO:add indices info
return pd.read_sql(query.statement, query.session.bind)
elif return_type == 'domain':
return query.all()
elif return_type == 'dict':
return [item.to_json() for item in query.all()]
except Exception as e:
raise
finally:
if local_session:
session.close()
def get_data(data_schema,
security_id=None,
codes=None,
level=None,
provider='eastmoney',
columns=None,
return_type='df',
start_timestamp=None,
end_timestamp=None,
filters=None,
session=None,
order=None,
limit=None):
local_session = False
if not session:
store_category = get_store_category(data_schema)
session = get_db_session(provider=provider,
store_category=store_category)
local_session = True
try:
if columns:
query = session.query(*columns)
else:
query = session.query(data_schema)
if security_id:
query = query.filter(data_schema.security_id == security_id)
if codes:
query = query.filter(data_schema.code.in_(codes))
if level:
query = query.filter(data_schema.level == level)
query = common_filter(query,
data_schema=data_schema,
start_timestamp=start_timestamp,
end_timestamp=end_timestamp,
filters=filters,
order=order,
limit=limit)
if return_type == 'df':
df = pd.read_sql(query.statement, query.session.bind)
if not df.empty:
return index_df_with_time(df, drop=False)
elif return_type == 'domain':
return query.all()
elif return_type == 'dict':
return [item.to_json() for item in query.all()]
except Exception:
raise
finally:
if local_session:
session.close()
def df_to_db(df, data_schema, provider):
store_category = get_store_category(data_schema)
db_engine = get_db_engine(provider, store_category=store_category)
current = get_data(data_schema=data_schema, columns=[data_schema.id])
df = df[~df['id'].isin(current['id'])]
df.to_sql(data_schema.__tablename__,
db_engine,
index=False,
if_exists='append')
def init_securities(df, security_type='stock', provider=Provider.EASTMONEY):
df = df.drop_duplicates(subset=['id'])
data_schema = get_security_schema(security_type)
store_category = get_store_category(data_schema=data_schema)
db_engine = get_db_engine(provider, store_category=store_category)
security_schema = get_security_schema(security_type)
current = get_securities(security_type=security_type,
columns=[security_schema.id],
provider=provider)
df = df[~df['id'].isin(current['id'])]
df.to_sql(security_schema.__tablename__,
db_engine,
index=False,
if_exists='append')
def security_id_in_index(security_id,
index_id,
session=None,
data_schema=StockIndex,
provider='eastmoney'):
the_id = '{}_{}'.format(index_id, security_id)
local_session = False
if not session:
store_category = get_store_category(data_schema)
session = get_db_session(provider=provider,
store_category=store_category)
local_session = True
try:
return data_exist(session=session, schema=data_schema, id=the_id)
except Exception:
raise
finally:
if local_session:
session.close()
def __init__(self,
security_type=SecurityType.stock,
exchanges=['sh', 'sz'],
codes=None,
batch_size=10,
force_update=False,
sleeping_time=5,
fetching_style=TimeSeriesFetchingStyle.end_size,
default_size=2000,
contain_unfinished_data=False,
level=TradingLevel.LEVEL_1DAY,
one_shot=True) -> None:
self.data_schema = get_kdata_schema(security_type=security_type,
level=level)
self.store_category = get_store_category(self.data_schema)
self.jq_trading_level = to_jq_trading_level(level)
super().__init__(security_type, exchanges, codes, batch_size,
force_update, sleeping_time, fetching_style,
default_size, contain_unfinished_data, level,
one_shot)
self.current_factors = {}
for security_item in self.securities:
kdata = get_kdata(security_id=security_item.id,
provider=self.provider,
level=self.level.value,
order=self.data_schema.timestamp.desc(),
limit=1,
return_type='domain',
session=self.session)
if kdata:
self.current_factors[security_item.id] = kdata[0].factor
self.logger.info('{} latest factor:{}'.format(
security_item.id, kdata[0].factor))
auth(JQ_ACCOUNT, JQ_PASSWD)
def get_group(provider,
data_schema,
column,
group_func=func.count,
session=None):
local_session = False
if not session:
store_category = get_store_category(data_schema)
session = get_db_session(provider=provider,
store_category=store_category)
local_session = True
try:
if group_func:
query = session.query(column, group_func(column)).group_by(column)
else:
query = session.query(column).group_by(column)
df = pd.read_sql(query.statement, query.session.bind)
return df
except Exception:
raise
finally:
if local_session:
session.close()
def get_data(data_schema,
security_list=None,
security_id=None,
codes=None,
level=None,
provider='eastmoney',
columns=None,
return_type='df',
start_timestamp=None,
end_timestamp=None,
filters=None,
session=None,
order=None,
limit=None,
index='timestamp',
index_is_time=True):
local_session = False
if not session:
store_category = get_store_category(data_schema)
session = get_db_session(provider=provider,
store_category=store_category)
local_session = True
try:
if columns:
if data_schema.timestamp not in columns:
columns.append(data_schema.timestamp)
query = session.query(*columns)
else:
query = session.query(data_schema)
if security_id:
query = query.filter(data_schema.security_id == security_id)
if codes:
query = query.filter(data_schema.code.in_(codes))
if security_list:
query = query.filter(data_schema.security_id.in_(security_list))
# we always store different level in different schema,the level param is not useful now
if level:
try:
# some schema has no level,just ignore it
data_schema.level
if type(level) == TradingLevel:
level = level.value
query = query.filter(data_schema.level == level)
except Exception as e:
pass
query = common_filter(query,
data_schema=data_schema,
start_timestamp=start_timestamp,
end_timestamp=end_timestamp,
filters=filters,
order=order,
limit=limit)
if return_type == 'df':
df = pd.read_sql(query.statement, query.session.bind)
if df_is_not_null(df):
return index_df(df,
drop=False,
index=index,
index_is_time=index_is_time)
elif return_type == 'domain':
return query.all()
elif return_type == 'dict':
return [item.__dict__ for item in query.all()]
except Exception:
raise
finally:
if local_session:
session.close()
index_id,
session=None,
data_schema=StockIndex,
provider='eastmoney'):
the_id = '{}_{}'.format(index_id, security_id)
local_session = False
if not session:
store_category = get_store_category(data_schema)
session = get_db_session(provider=provider,
store_category=store_category)
local_session = True
try:
return data_exist(session=session, schema=data_schema, id=the_id)
except Exception:
raise
finally:
if local_session:
session.close()
if __name__ == '__main__':
store_category = get_store_category(Stock)
session = get_db_session(provider='eastmoney',
store_category=store_category)
print(
get_count(data_schema=Stock,
filters=[Stock.exchange == 'sh'],
session=session))
