def get_having_criterion(self, table, config):
if self.andKey in config:
andConfig = config.get(self.andKey)
return Criterion.all([
self.get_having_criterion(table, config)
for config in andConfig
])
if self.orKey in config:
orConfig = config.get(self.orKey)
return Criterion.any([
self.get_having_criterion(table, config) for config in orConfig
])
value = config.get('value')
if 'plain' in value:
value = value.get('plain')
else:
value = FUNCTION_MAPPING[value.get('aggregate')](
table[value.get('column')])
if 'aggregate' in config:
return FILTER_MAPPING[config.get('comp')](
FUNCTION_MAPPING[config.get('aggregate')](
table[config.get('column')]), value)
return FILTER_MAPPING[config.get('comp')](table[config.get('column')],
value)
def get_index_day_price_data(self, universe: Universe, start_date: date,
end_date: date) -> Optional[DataFrame]:
"""
해당 universe의 index 가격을 조회
:param universe: kospi, kosdaq, kospi200, kosdaq150
:param start_date:
:param end_date:
:return:
"""
if universe in (Universe.kospi, Universe.kosdaq):
ticker = universe.name.upper()
elif universe in (Universe.kospi200, Universe.kosdaq150):
if universe == Universe.kospi200:
ticker = "KOSPI200"
elif universe == Universe.kosdaq150:
ticker = "KOSDAQ150"
data_index_candle = Table("data_indexcandleday")
query = (MySQLQuery.from_(data_index_candle).select("*").where(
Criterion.all([
data_index_candle.ticker == ticker,
data_index_candle.date >= start_date,
data_index_candle.date <= end_date,
])))
df = self.executor.sql(query.get_sql())
df = df.drop(["id", "ticker"], axis=1)
return df
def select_rules_by_reaction_id(reaction_ids, filters=None) -> str:
"""
Returns a SQL statement that selects reaction rules based on their associated MetaNetX ID.
Parameters
----------
reaction_ids : list
List of MetaNetX reaction identifiers.
filters : list of pypika.terms.Criterion
Criteria that the rules should also satisfy. Feeds the WHERE statement.
Returns
-------
str
SQL SELECT statement.
"""
if filters is None:
filters = []
# Join 'rules' to 'thesaurus' on 'thesaurus.synonym' to account for internal synonymous IDs
# Select rules by 'rules.reaction_id' or 'thesaurus.id'
return (Query.from_(rules).left_join(thesaurus).on(
rules.reaction_id == thesaurus.synonym).select(rules.rule_id).where(
Criterion.all([
rules.reaction_id.isin(reaction_ids)
| thesaurus.id.isin(reaction_ids), *filters
])).get_sql())
def getCriterion(self):
conds = []
if self.name is not None: conds.append(products.name.ilike(f'%{self.name}%'))
if len(self.id) != 0: conds.append(products.id.isin(self.id))
if self.minPrice is not None: conds.append(products.price_dollar >= self.minPrice)
if self.maxPrice is not None: conds.append(products.price_dollar <= self.maxPrice)
return Criterion.all(conds)
def select_metabolites_by_inchi(inchi, filters=None):
"""
Returns a SQL statement that selects metabolites based on their InChI depiction.
Parameters
----------
inchi : str
InChI depiction of a chemical compound.
filters : list of pypika.terms.Criterion
Criteria that the metabolites should also satisfy. Feeds the WHERE statement.
Returns
-------
str
SQL SELECT statement.
"""
if filters is None:
filters = []
# Join 'metabolites' to 'thesaurus' on 'thesaurus.synonym' to account for internal synonymous IDs
# Select 'thesaurus.id' if exists, otherwise 'metabolites.metabolite_id' (meaning no synonym was found)
return (Query.from_(metabolites).left_join(thesaurus).on(
metabolites.metabolite_id == thesaurus.synonym).select(Case().when(
thesaurus.id.notnull(),
thesaurus.id).else_(metabolites.metabolite_id).as_(
"metabolite_id")).distinct().where(
Criterion.all([metabolites.inchi == inchi,
*filters])).get_sql())
def select_rules_by_similarity(input_fingerprint, cutoff, filters=None) -> str:
"""
Returns a SQL statement that selects reaction rules whose substrates have a minimum chemical similarity with the
input.
Parameters
----------
input_fingerprint : str
Serialized molecular fingerprint of a chemical compound, against which substrates are compared.
cutoff : float
Chemical similarity score (between the input fingerprint and the fingerprint of a reaction's native substrate)
below which rules are filtered out.
filters : list of pypika.terms.Criterion
Criteria that the rules should also satisfy. Feeds the WHERE statement.
Returns
-------
str
SQL SELECT statement.
"""
if filters is None:
filters = []
return (Query.from_(rules).join(stoichiometry).on(
rules.reaction_id == stoichiometry.reaction_id).join(metabolites).on(
stoichiometry.substrate_id == metabolites.metabolite_id).groupby(
rules.rule_id).having(
ChemicalSimilarity.Function(
input_fingerprint, metabolites.fingerprint) >= cutoff).
select(rules.rule_id).where(Criterion.all(filters)).get_sql())
def __str__(self):
from pypika import Field, Criterion
filter_ = []
for var_name in self.__fields__:
value = getattr(self, var_name)
if value:
field = Field(var_name)
filter_.append(field == value)
return str(Criterion.all(filter_))
async def checkSession(self, session: Session) -> bool:
sql = Query.from_(sessions).select(sessions.user_id) \
.where(Criterion.all([
sessions.session_id == session.sessionId,
sessions.user_id == session.userId,
sessions.updated_at == session.updatedAt
]))
result = await databaseClient.query(sql.get_sql())
return len(result) > 0
async def checkUser(self, login: str, password: str) -> Optional[int]:
sql = Query.from_(users).select(users.id) \
.where(Criterion.all([
users.login == login,
users.password_hash == self._getPasswordHash(password)
]))
result = await databaseClient.query(sql.get_sql())
if len(result) == 0: return None
return result[0][0]
def get_having_query(self, table, query, config):
if self.andKey in config:
rootConfig = config.get(self.andKey)
elif self.orKey in config:
rootConfig = config.get(self.orKey)
# a way to handle complex having configs
crit = Criterion.all([
self.get_having_criterion(table, config) for config in rootConfig
])
# sample query
return query.having(crit)
async def refreshSession(self, session: Session) -> Optional[Session]:
sql = Query.update(sessions).set(sessions.updated_at, datetime.now(timezone.utc)) \
.where(Criterion.all([
sessions.session_id == session.sessionId,
sessions.user_id == session.userId,
sessions.updated_at == session.updatedAt
])) \
.returning(sessions.session_id, sessions.user_id, sessions.updated_at)
result = await databaseClient.query(sql.get_sql())
if len(result) == 0: return None
row = result[0]
return Session(sessionId=row[0], userId=row[1], updatedAt=row[2])
def test_filter(self):
expr = (an.LastValue(self.table_abc.fizz).filter(
Criterion.all([self.table_abc.bar == True])).over(
self.table_abc.foo).orderby(self.table_abc.date))
q = Query.from_(self.table_abc).select(expr)
self.assertEqual(
"SELECT "
'LAST_VALUE("fizz") '
'FILTER(WHERE "bar"=true) '
'OVER(PARTITION BY "foo" ORDER BY "date") '
'FROM "abc"',
str(q),
)
def get_filter_criterion(self, table, filter):
if self.andKey in filter:
andConfig = filter.get(self.andKey)
return Criterion.all([
self.get_filter_criterion(table, config)
for config in andConfig
])
if self.orKey in filter:
orConfig = filter.get(self.orKey)
return Criterion.any([
self.get_filter_criterion(table, config) for config in orConfig
])
return FILTER_MAPPING[filter.get('comp')](table[filter.get('column')],
filter.get('value'))
def select_rules_where(filters) -> str:
"""
Returns a SQL statement that (based on a series of filters) selects reaction rules, their associated MetaNetX ID,
and SMARTS expression.
Parameters
----------
filters : list of pypika.terms.Criterion
Criteria that the rules should satisfy. Feeds the WHERE statement.
Returns
-------
str
SQL SELECT statement.
"""
return (Query.from_(rules).select(rules.rule_id, rules.reaction_id,
rules.smarts).where(
Criterion.all(filters)).get_sql())
def get_filter_query(self, table, query, config):
if self.andKey in config:
rootConfig = config.get(self.andKey)
# a way to handle complex filter configs
crit = Criterion.all([
self.get_filter_criterion(table, config)
for config in rootConfig
])
elif self.orKey in config:
rootConfig = config.get(self.orKey)
# a way to handle complex filter configs
crit = Criterion.any([
self.get_filter_criterion(table, config)
for config in rootConfig
])
# sample query
return query.where(crit)
def get_trading_day_list(self, start_date: date, end_date: date) -> list:
"""
과거 거래일 리스트를 조회하는 함수
:param start_date: back test 시작일
:param end_date: back test 종료일
:return: 과거 거래일 리스트 반환
"""
data_trading_day = Table("data_iskoreatradingday")
query = (MySQLQuery.from_(data_trading_day).select(
data_trading_day.date).where(
Criterion.all([
data_trading_day.is_tradable == "1",
data_trading_day.date >= start_date,
data_trading_day.date <= end_date,
])))
df = self.executor.sql(query.get_sql())
trading_day_list = df["date"].to_list()
return trading_day_list
def get_exchange_rate(self, exchange_index: ExchangeRate, start_date: date,
end_date: date) -> DataFrame:
"""
달러, 유로, 엔 환율 조회
:param exchange_index:
:param start_date:
:param end_date:
:return:
"""
data_exchange_rate = Table("data_exchangeratecandleday")
query = (MySQLQuery.from_(data_exchange_rate).select("*").where(
Criterion.all([
data_exchange_rate.ticker == exchange_index.value,
data_exchange_rate.date >= start_date,
data_exchange_rate.date <= end_date,
])))
df = self.executor.sql(query.get_sql())
df = df.drop(["id", "ticker"], axis=1)
return df
def get_timesheet_frame_by_sessions(self,
session_ids: tuple) -> List[tuple]:
if not session_ids:
raise DoesNotExist()
query = SQLLiteQuery().from_(TIMESHEET) \
.inner_join(CATEGORY).on(Criterion.all((
TIMESHEET.default_category_id.notnull(),
TIMESHEET.default_category_id.eq(CATEGORY.id),
))) \
.select(
TIMESHEET.star,
# (TIMESHEET.finish - TIMESHEET.start).as_('activity_duration'), # datetime in sqlite in str
CATEGORY.name) \
.where(TIMESHEET.session_id.isin(session_ids)).get_sql()
timesheet_frame = self._cursor.execute(query).fetchall()
if not timesheet_frame:
raise DoesNotExist()
return timesheet_frame
def select_rules_by_ec_number(ec_numbers, filters=None) -> str:
"""
Returns a SQL statement that selects reaction rules based on their associated E.C. numbers.
Parameters
----------
ec_numbers : list
List of E.C. numbers.
filters : list of pypika.terms.Criterion
Criteria that the rules should also satisfy. Feeds the WHERE statement.
Returns
-------
str
SQL SELECT statement.
"""
if filters is None:
filters = []
return (Query.from_(rules).left_join(classification).on(
rules.reaction_id == classification.reaction_id).select(
rules.rule_id).where(
Criterion.all(
[classification.ec_number.isin(ec_numbers),
*filters])).get_sql())
def test_with_generator(self):
crit = Criterion.all(Field(letter) for letter in "abcd")
self.assertEqual(str(crit), '"a" AND "b" AND "c" AND "d"')
def test_multiple_args_returned_in_chain_of_ors(self):
crit = Criterion.all([Field("a"), Field("b"), Field("c"), Field("d")])
self.assertEqual(str(crit), '"a" AND "b" AND "c" AND "d"')
def test_single_arg_returns_self(self):
f = Field("a")
crit = Criterion.all([f])
self.assertEqual(str(f), str(crit))
def test_zero_args_returns_empty_criterion(self):
crit = Criterion.all()
self.assertIsInstance(crit, EmptyCriterion)
def q(self):
d_rng, period, model, minesite, unit = self.d_rng, self.period, self.model, self.minesite, self.unit
a, b = pk.Tables('Downtime', 'UnitID')
hrs_in_period = cfn('tblHrsInPeriod',
['d_lower', 'd_upper', 'minesite', 'period'])
period_range = cfn('period_range', ['startdate', 'enddate', 'period'])
_month = cfn('MONTH', ['date'])
_year = cfn('YEAR', ['date'])
iso_year = cfn('dbo.iso_year', ['date'])
datepart = cfn('DATEPART', ['period_type', 'date'])
month = _month(a.ShiftDate)
week = datepart(PseudoColumn('iso_week'), a.ShiftDate)
if period == 'month':
year = _year(a.ShiftDate)
_period = fn.Concat(year, '-', month) # .as_('period')
else:
year = iso_year(
a.ShiftDate
) # only use iso_year (slow custom function) when grouping by week
_period = fn.Concat(year, '-', week) # .as_('period')
# Create all week/month periods in range crossed with units
q_prd = Query.from_(period_range(d_rng[0], d_rng[1],
period)).select('period')
q_base = Query.from_(b) \
.select(q_prd.period, b.Unit) \
.cross_join(q_prd).cross() \
.where(Criterion.all([
b.MineSite == minesite,
b.model.like(model)]))
# Unit, Total, SMS, Suncor
cols_dt = [
_period.as_('period'), a.Unit,
fn.Sum(a.Duration).as_('Total'),
fn.Sum(a.SMS).as_('SMS'),
fn.Sum(a.Suncor).as_('Suncor')
]
q_dt = Query.from_(a) \
.select(*cols_dt) \
.where(Criterion.all([
a.ShiftDate.between(d_rng[0], d_rng[1]),
a.Duration > 0.01])) \
.groupby(a.Unit, _period)
# in case need historical data for single unit
if not unit is None:
q_dt = q_dt.where(a.Unit == unit)
q_base = q_base.where(b.Unit == unit)
cols1 = [q_base.period, q_base.Unit, q_dt.Total, q_dt.SMS, q_dt.Suncor]
q1 = Query.from_(q_base) \
.select(*cols1) \
.left_join(q_dt).on_field('Unit', 'Period')
q_hrs = Query.from_(hrs_in_period(d_rng[0], d_rng[1], minesite,
period)).select('*')
cols = [
b.Model, b.DeliveryDate, q1.star, q_hrs.ExcludeHours_MA,
q_hrs.ExcludeHours_PA,
Case().when(b.AHSActive == 1,
'AHS').else_('Staffed').as_('Operation')
]
return Query.from_(q1) \
.select(*cols) \
.left_join(b).on_field('Unit') \
.left_join(q_hrs).on_field('Unit', 'Period') \
.where(b.Model.like(model))
def __get_day_price_data(
self,
universe: Universe,
table_name: str,
trading_share: bool,
trading_trend: bool,
start_date: date,
end_date: date,
) -> dict:
past_universe_stock_list = self.get_past_universe_stock_list(universe)
past_universe_stock_list = tuple(past_universe_stock_list)
data_day_price, data_ticker = Tables(table_name, "data_ticker")
query = MySQLQuery.from_(data_day_price).join(data_ticker).on(
data_day_price.ticker_id == data_ticker.id)
if trading_share is True:
data_share = Table("data_daytradinginfo")
query = query.join(data_share).on(
Criterion.all([
data_day_price.ticker_id == data_share.ticker_id,
data_day_price.date == data_share.date
]))
if trading_trend is True:
data_trend = Table("data_daytradingtrend")
query = query.left_join(data_trend).on(
Criterion.all([
data_day_price.ticker_id == data_trend.ticker_id,
data_day_price.date == data_trend.date
]))
if trading_share is False and trading_trend is False:
query = query.select(data_day_price.star, data_ticker.ticker)
if trading_share is True and trading_trend is False:
query = query.select(data_day_price.star, data_ticker.ticker,
data_share.cap, data_share.shares_out)
if trading_share is False and trading_trend is True:
query = query.select(
data_day_price.star,
data_ticker.ticker,
data_trend.p_buy_vol,
data_trend.p_buy_tr_val,
data_trend.o_buy_vol,
data_trend.o_buy_tr_val,
data_trend.f_buy_vol,
data_trend.f_buy_tr_val,
data_trend.pension_f_buy_vol,
data_trend.pension_f_tr_val,
)
if trading_share is True and trading_trend is True:
query = query.select(
data_day_price.star,
data_ticker.ticker,
data_share.cap,
data_share.shares_out,
data_trend.p_buy_vol,
data_trend.p_buy_tr_val,
data_trend.o_buy_vol,
data_trend.o_buy_tr_val,
data_trend.f_buy_vol,
data_trend.f_buy_tr_val,
data_trend.pension_f_buy_vol,
data_trend.pension_f_tr_val,
)
query = query.where(
Criterion.all([
data_ticker.ticker.isin(past_universe_stock_list),
data_day_price.date >= start_date,
data_day_price.date <= end_date,
]))
df = self.executor.sql(query.get_sql())
df = df.drop(["id"], axis=1)
return df
def expand_criterion(self):
return Criterion.all(self.get_all_criterion())
def test_zero_args_returns_empty_criterion(self):
crit = Criterion.all()
self.assertIsInstance(crit, EmptyCriterion)
def test_single_arg_returns_self(self):
f = Field('a')
crit = Criterion.all([f])
self.assertEqual(str(f), str(crit))
def test_multiple_args_returned_in_chain_of_ors(self):
crit = Criterion.all([Field('a'), Field('b'), Field('c'), Field('d')])
self.assertEqual(str(crit), '"a" AND "b" AND "c" AND "d"')
def test_with_generator(self):
crit = Criterion.all(Field(letter)
for letter in 'abcd')
self.assertEqual(str(crit), '"a" AND "b" AND "c" AND "d"')
