def get_by_ticker(cls, ticker, index=False, lock_mode=None):
cache_key = '%s-%s-%s-%s-%s' % (cls.__name__, 'get_stock_by_ticker',
ticker, index, lock_mode)
cache_value = cache.get(cache_key)
if cache_value: #如果有缓存，直接返回缓存
return cache_value
query = session.query(cls)
if lock_mode:
query = query.with_lockmode(lock_mode)
query = query.filter(cls.ticker == ticker,
cls.instrument == ('index' if index else 'stock'))
obj = query.first()
cache.set(cache_key, obj)
return obj
#!/usr/bin/env python
# coding: utf8
import time
import datetime
def get_today():
today = datetime.datetime.now().strftime('%Y-%m-%d')
return today
def list_to_string(l):
l = [str(item) for item in l]
return "".join(l)
if __name__ == '__main__':
""""""
from pyquant.db_models import Symbol as m_symbol
from pyquant.libs.mysqllib import session, engine
query = session.query(m_symbol)
def get_index_list(cls):
return session.query(cls).filter(Symbol.instrument == 'index').all()
def get_list_by_symbolgroup_id(symbolgroup_id, limit=30, offset=0):
return session.query(Symbol).filter(Symbol.symbolgroup.any(id=symbolgroup_id)).\
limit(limit).offset(offset).all()
def _query_join():
pprint.pprint(
session.query(DailyPrice).join(Symbol).filter(
Symbol.id == 2433).limit(10).all())
def _query_relation():
query = session.query(DailyPrice).filter(DailyPrice.id == '7798525').all()
pprint.pprint([row.to_dict() for row in query])
def get_system_groups():
return session.query(__class__).filter(SymbolGroup.user_id == 0).all()
def get_by_symbol_id(cls,
symbol_id,
fromdate=None,
todate=None,
output='dict',
isCache=True):
"""
根据symbol_id 查daily price
:param symbol_id:
:param fromdate:
:param todate:
:param output:
:return:
"""
if not todate:
todate = str(datetime.date.today())
cache_key = '%s-%s-%s-%s-%s-%s' % (cls.__name__, 'get_by_symbol_id',
symbol_id, fromdate, todate, output)
# print('cache key', cache_key)
if isCache: #用cache
cache_value = cache.get(cache_key)
if isinstance(cache_value, pd.DataFrame):
if not cache_value.empty:
return cache_value
if cache_value: #如果有缓存，直接返回缓存
return cache_value
where = []
if isinstance(symbol_id, (list, tuple)): #
where.append(DailyPrice.symbol_id.in_(symbol_id))
else:
where.append(DailyPrice.symbol_id == symbol_id)
if fromdate:
where.append(DailyPrice.price_date >= fromdate)
if todate:
where.append(DailyPrice.price_date < todate)
# print(where)
if output == 'df':
df = pd.read_sql(
session.query(DailyPrice).filter(*where).statement,
session.bind)
del df['id']
del df['symbol_id']
#设置index
df['price_date'] = df['price_date'].astype('datetime64[ns]')
df = df.set_index('price_date')
# columns 改名
df.columns = ['open', 'high', 'low', 'close', 'volume']
# 更换columns顺序
cols = ['open', 'high', 'close', 'low', 'volume']
objs = df.ix[:, cols]
elif output == 'dict':
# print('保存缓存', cache_key)
objs = [
row.to_dict()
for row in session.query(DailyPrice).filter(*where).all()
]
else: #models
objs = session.query(DailyPrice).filter(*where).all()
cache.set(cache_key, objs)
return objs