def open_table(self, security):
if security.is_open_fund():
p = jqdata.get_config().get_bcolz_otcfund_path(security)
else:
p = jqdata.get_config().get_bcolz_fund_path(security)
ct = retry_bcolz_open(p)
return _Table(ct, ct.cols['date'][:])
def __init__(self, security, db='bcolz', freq='day'):
assert db in ['bcolz', 'shm']
assert freq in ['day', 'minute']
self.security = security
self.db = db
self.freq = freq
if db == 'bcolz':
if freq == 'day':
p = jqdata.get_config().get_bcolz_day_path(security)
else:
p = jqdata.get_config().get_bcolz_minute_path(security)
ct = retry_bcolz_open(p)
self.table = _BenchTable(ct.cols['close'][:], ct.cols['factor'][:],
ct.cols['date'][:])
else:
if freq == 'day':
p = jqdata.get_config().get_day_shm_path(security.code)
arr = SharedArray.attach("file://" + p, readonly=True)
date_idx = 0
close_idx = security.day_column_names.index('close') + 1
factor_idx = security.day_column_names.index('factor') + 1
self.table = _BenchTable(arr[:, close_idx], arr[:, factor_idx],
arr[:, date_idx])
else:
raise Exception("unsupport db=shm, freq=minute")
def load_open_fund(self):
cfg = jqdata.get_config()
f = cfg.get_open_fund_dividend_pk()
with open(f, "rb") as store:
dic = pickle.load(store)
for i in dic:
self._otcdic[i] = dic[i]
def get_trading_days(self, security):
if security.is_open_fund():
p = jqdata.get_config().get_bcolz_otcfund_path(security)
cr = retry_bcolz_open(os.path.join(p, 'date'))
else:
cr = self.open_bcolz_carray(security, 'date')
return cr[:]
def get_date_by_period(security, start_date, end_date):
cfg = get_config()
if cfg.USE_SHM:
store = get_shm_day_store()
else:
store = get_bcolz_day_store()
return store.get_date_by_period(security, start_date, end_date)
def get_date_by_count(security, end_date, count):
cfg = get_config()
if cfg.USE_SHM:
store = get_shm_day_store()
else:
store = get_bcolz_day_store()
return store.get_date_by_count(security, end_date, count)
def load_open_fund(self):
f = jqdata.get_config().get_open_fund_security_pk()
with open(f, "rb") as store:
dic = pickle.load(store)
for i in dic:
self._otcdic[i] = Security(**dic[i])
pass
def get_factor_by_date(security, date):
cfg = get_config()
if cfg.USE_SHM:
store = get_shm_day_store()
else:
store = get_bcolz_day_store()
return store.get_factor_by_date(security, date)
def __init__(self):
self.__url = jqdata.get_config().GTA_SERVER
self.__sql_runner = None
self.__disable_join = True
self.__table_names = self.__load_table_names()
# 加载 table 太慢了, 动态加载
for name in self.__table_names:
setattr(self, name, None)
pass
def get_jy():
db_url = jqdata.get_config().JY_SERVER
if not db_url:
return None
eg = get_engine(db_url)
if eg is not None:
return Database(eg, disable_join=True)
else:
return None
pass
def get_minute_by_period(security, start_dt, end_dt, include_now=True):
cfg = get_config()
if cfg.USE_SHM:
store = get_mixed_minute_store()
else:
store = get_bcolz_minute_store()
return store.get_minute_by_period(security,
start_dt,
end_dt,
include_now=include_now)
def get_locked_shares(stock_list, start_date=None, end_date=None, forward_count=None):
'''
获取指定日期范围内的个股限售股解禁数据
:param stock_list:单个股票或股票代码的列表
:param start_date: 开始日期
:param end_date: 结束日期
:param forward_count: 交易日数量，与 end_date 不能同时使用。与 start_date 配合使用时， 表示获取 start_date 到 start_date+count-1个交易日期间的数据
:return: dataframe
|date|stock_code|num|rate1|rate2|
|----------|-----------|--------|----|----|
|2017-07-01|000001.XSHG|20000000|0.03|0.02|
|2017-07-01|000001.XSHG|20000000|0.03|0.02|
#### 注意单日个股多条解禁数据的问题 ####
'''
import pandas as pd
from six import StringIO
from ..utils.utils import convert_date, is_lists
from ..db_utils import query, request_mysql_server
if forward_count is not None and end_date is not None:
raise ParamsError("get_locked_shares 不能同时指定 end_date 和 forward_count 两个参数")
if forward_count is None and end_date is None:
raise ParamsError("get_locked_shares 必须指定 end_date 或 forward_count 之一")
start_date = convert_date(start_date)
if not is_lists(stock_list):
stock_list = [stock_list]
if stock_list:
stock_list = [s.split('.')[0] for s in stock_list]
if forward_count is not None:
end_date = start_date + datetime.timedelta(days=forward_count)
end_date = convert_date(end_date)
q = query(StkLockShares.day, StkLockShares.code, StkLockShares.num, StkLockShares.rate1,
StkLockShares.rate2).filter(StkLockShares.code.in_(stock_list),
StkLockShares.day <= end_date, StkLockShares.day >= start_date
).order_by(StkLockShares.day).order_by(StkLockShares.code.desc())
sql = compile_query(q)
cfg = get_config()
if os.getenv('JQENV') == 'client': # 客户端
csv = request_mysql_server(sql)
else:
if not cfg.FUNDAMENTALS_SERVERS:
raise RuntimeError(
"you must config FUNDAMENTALS_SERVERS for jqdata")
sql_runner = get_sql_runner(
server_name='fundamentals', keep_connection=cfg.KEEP_DB_CONNECTION,
retry_policy=cfg.DB_RETRY_POLICY, is_random=False)
csv = sql_runner.run(sql, return_df=False)
dtype_dict = {}
dtype_dict['code'] = str
df = pd.read_csv(StringIO(csv), dtype=dtype_dict)
return df
pass
def get_minute_bar_by_count(security, end_dt, count, fields, include_now=True):
cfg = get_config()
if cfg.USE_SHM:
store = get_mixed_minute_store()
else:
store = get_bcolz_minute_store()
return store.get_bar_by_count(security,
end_dt,
count,
fields,
include_now=include_now)
def get_engine():
global _engine
if _engine is None:
cfg = get_config()
if not cfg.FUNDAMENTALS_SERVERS:
raise RuntimeError(
"you must config FUNDAMENTALS_SERVERS for jqdata")
sql_runner = get_sql_runner(server_name='fundamentals',
keep_connection=cfg.KEEP_DB_CONNECTION,
retry_policy=cfg.DB_RETRY_POLICY)
_engine = sql_runner.engine
return _engine
def get_billboard_list(stock_list=None, start_date=None, end_date=None, count=None):
'''
返回执指定日期区间内的龙虎榜个股列表
:param stock_list：单个股票或股票代码列表， 可以为 None， 返回股票的列表。
:param start_date: 开始日期
:param end_date: 结束日期
:param count: 交易日数量，与 end_date 不能同时使用。与 start_date 配合使用时， 表示获取 start_date 到 start_date+count-1个交易日期间的数据
:return:Dataframe
| date | stock_code | abnormal_code | abnormal_name | sales_depart_name | abnormal_type | buy_value | buy_rate | sell_value | sell_rate | net_value | amount |
|----------|------------|---------------|--------------------------|-------------------|---------------|-----------|----------|------------|-----------|-----------|--------|
|2017-07-01| 000038.XSHE| 1 |日价格涨幅偏离值达7%以上的证券| None | ALL | 35298494 |0.37108699| 32098850 | 0.33744968| 3199644 |95121886|
'''
import pandas as pd
from ..utils.utils import convert_date, is_lists
from ..db_utils import query, request_mysql_server
if count is not None and start_date is not None:
raise ParamsError("get_billboard_list 不能同时指定 start_date 和 count 两个参数")
if count is None and start_date is None:
raise ParamsError("get_billboard_list 必须指定 start_date 或 count 之一")
end_date = convert_date(end_date) if end_date else datetime.date.today()
start_date = convert_date(start_date) if start_date else \
(get_trade_days(end_date=end_date, count=count)[0] if count else TRADE_MIN_DATE)
if not is_lists(stock_list):
if stock_list is not None:
stock_list = [stock_list]
if stock_list:
stock_list = [s.split('.')[0] for s in stock_list]
q = query(StkAbnormal).filter(StkAbnormal.day <= end_date, StkAbnormal.day >= start_date)
if stock_list is not None:
q = q.filter(StkAbnormal.code.in_(stock_list))
q = q.order_by(StkAbnormal.day.desc()).order_by(StkAbnormal.code.desc())
sql = compile_query(q)
cfg = get_config()
if os.getenv('JQENV') == 'client': # 客户端
csv = request_mysql_server(sql)
dtype_dict = {}
dtype_dict['code'] = str
df = pd.read_csv(six.StringIO(csv), dtype=dtype_dict)
else:
if not cfg.FUNDAMENTALS_SERVERS:
raise RuntimeError(
"you must config FUNDAMENTALS_SERVERS for jqdata")
sql_runner = get_sql_runner(
server_name='fundamentals', keep_connection=cfg.KEEP_DB_CONNECTION,
retry_policy=cfg.DB_RETRY_POLICY, is_random=False)
df = sql_runner.run(sql, return_df=True)
return df
pass
def get_macro():
# if os.getenv('JQENV') == 'client': # 客户端
if sys.platform in ['win32', 'cygwin']: # 客户端
return Macro()
else:
db_url = jqdata.get_config().MACRO_SERVER
if not db_url:
return None
eg = get_engine(db_url)
if eg is not None:
return Database(eg, disable_join=True)
else:
return None
pass
def get_sql_runner(server_name, retry_policy=None, is_random=False, **kwargs):
assert server_name == 'fundamentals'
cfg = get_config()
servers = cfg.FUNDAMENTALS_SERVERS
retry_policy = retry_policy or dict(stop_max_attempt_number=10,
wait_exponential_multiplier=1000)
retry_policy.update(retry_on_exception=lambda e: isinstance(
e, (sqlalchemy.exc.SQLAlchemyError, OSError, IOError)) and getattr(
e, 'should_retry', True))
return RetryingObject(SqlRunner,
servers,
gen_kws=kwargs,
retry_kws=retry_policy,
is_random=is_random)
def get_daily_bar_by_period(security,
start_date,
end_date,
fields,
include_now=True):
cfg = get_config()
if cfg.USE_SHM:
store = get_shm_day_store()
else:
store = get_bcolz_day_store()
return store.get_bar_by_period(security,
start_date,
end_date,
fields,
include_now=include_now)
def get_fundamentals(query_object=None, date=None, statDate=None, sql=None): # noqa
if query_object is None and sql is None:
raise ParamsError("get_fundamentals 至少输入 query_object 或者 sql 参数")
cfg = get_config()
if date:
date = convert_date(date)
if query_object:
sql = fundamentals_query_to_sql(query_object, date, statDate)
check_string(sql)
if os.getenv('JQENV') == 'client': # 客户端
from jqdata.db_utils import request_mysql_server
csv = request_mysql_server(sql)
else:
if not cfg.FUNDAMENTALS_SERVERS:
raise RuntimeError("you must config FUNDAMENTALS_SERVERS for jqdata")
sql_runner = get_sql_runner(
server_name='fundamentals', keep_connection=cfg.KEEP_DB_CONNECTION,
retry_policy=cfg.DB_RETRY_POLICY, is_random=False)
# return csv 在转成 DataFrame, 跟kaunke保持兼容, 防止直接return df 跟以前不一样
csv = sql_runner.run(sql, return_df=False)
return pd.read_csv(StringIO(csv))
def open_bcolz_table(self, security):
p = jqdata.get_config().get_bcolz_minute_path(security)
try:
ct = retry_bcolz_open(p)
except Exception as e:
# 证劵刚刚上市或者还没有上市，所有没有bcolz数据。
if security.start_date >= date.today():
return None
raise e
# 股指期货缓存date列，其他的缓存天行情的date列
if security.is_futures():
return _Table(ct, ct.cols['date'][:])
else:
try:
# index = BcolzDayStore.instance().open_bcolz_carray(security, 'date')[:]
# attrs['date'] 表示交易日。
index = np.array(ct.attrs['date'])
except KeyError as e:
# 从天bcolz数据中取date列.
index = BcolzDayStore.instance().open_bcolz_carray(
security, 'date')[:]
return _Table(ct, index)
def get_fundamentals_continuously(query_object=None, end_date=None, count=1):
'''
query_object:查询对象
end_date:查询财务数据的截止日期
count：查询财务数据前溯天数，默认为1
返回一个pd.Panel, 三维分别是 field, date, security.
field: 下面的表的中属性
https://www.joinquant.com/data/dict/fundamentals
'''
if query_object is None:
raise ParamsError("get_fundamentals_continuously 需要输入 query_object 参数")
if end_date is None:
end_date = datetime.date.today()
cfg = get_config()
trade_day = get_trade_days(end_date=end_date, count=count)
if query_object:
sql = fundamentals_continuously_query_to_sql(query_object, trade_day)
check_string(sql)
# 调用查询接口生成CSV格式字符串
if os.getenv('JQENV') == 'client': # 客户端
from jqdata.db_utils import request_mysql_server
csv = request_mysql_server(sql)
else:
if not cfg.FUNDAMENTALS_SERVERS:
raise RuntimeError("you must config FUNDAMENTALS_SERVER for jqdata")
sql_runner = get_sql_runner(
server_name='fundamentals', keep_connection=cfg.KEEP_DB_CONNECTION,
retry_policy=cfg.DB_RETRY_POLICY, is_random=False)
csv = sql_runner.run(sql, return_df=False)
# 转换成panel，设置时间和股票code为索引
df = pd.read_csv(StringIO(csv))
df = df.drop_duplicates()
newdf = df.set_index(['day', 'code'])
pan = newdf.to_panel()
return pan
pass
def instance():
if not hasattr(MarginStore_Pk, "_instance"):
cfg = jqdata.get_config()
MarginStore_Pk._instance = MarginStore_Pk(cfg.get_margin_pk())
return MarginStore_Pk._instance
def instance():
if not hasattr(MergedStore, "_instance"):
MergedStore._instance = MergedStore(
jqdata.get_config().get_merged_pk())
return MergedStore._instance
def instance():
if not hasattr(SecurityStore, '_instance'):
SecurityStore._instance = SecurityStore(
jqdata.get_config().get_security_pk())
return SecurityStore._instance
def instance():
if not hasattr(IndustryStore, "_instance"):
IndustryStore._instance = IndustryStore(jqdata.get_config().get_industry_pk())
return IndustryStore._instance
def get_margin_store():
import os
if os.path.exists(jqdata.get_config().get_sqlite_pk()):
return MarginStore_Sqlite.instance()
else:
return MarginStore_Pk.instance()
def get_industry_store():
import os
if os.path.exists(jqdata.get_config().get_sqlite_pk()):
return IndustryStore_Sqlite.instance()
else:
return IndustryStore.instance()
def instance():
if not hasattr(HisnameStore, "_instance"):
HisnameStore._instance = HisnameStore(
jqdata.get_config().get_hisname_pk())
return HisnameStore._instance
def instance():
if not hasattr(StStore, '_instance'):
StStore._instance = StStore(jqdata.get_config().get_st_pk())
return StStore._instance
def instance():
if not hasattr(FactorStore, "_instance"):
FactorStore._instance = FactorStore(
jqdata.get_config().get_factor_pk())
return FactorStore._instance
