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))