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 search_commits(conn, repos, search, author, begin, end):
"""Search commits currently in the database.
Args:
repos: List of repos to search in.
search (List of Strings): List of strings to search the commit messages for.
author (List of Strings): List of authors names to search for.
begin (datetime): Datetime to begin the search from.
end (datetime): Datetime to end the search at.
"""
commit_table = Table('COMMITS')
sql = commit_table.select(commit_table.PROJECT, commit_table.LINK,
commit_table.AUTHOR, commit_table.MESSAGE,
commit_table.DATE).orderby(commit_table.DATE,
order=Order.asc)
if repos != None:
sql = sql.where(commit_table.PROJECT.isin(repos))
if search != None:
sql = sql.where(
Criterion.any(
[commit_table.MESSAGE.like("%" + x + "%") for x in search]))
if author != None:
sql = sql.where(
Criterion.any(
[commit_table.AUTHOR.like("%" + x + "%") for x in author]))
if begin != None:
sql = sql.where(commit_table.DATE >= begin)
if end != None:
sql = sql.where(commit_table.DATE <= end)
return conn.execute(sql.get_sql())
def get_processes(conn,
name=None,
location=None,
fields=[
'ID', 'REF_ID', 'NAME', 'PROCESS_TYPE', 'LOCATION',
'F_QUANTITATIVE_REFERENCE'
]):
"""
Get processes from sqlite openLCA database.
:param conn: database connection
:param name: list of partial names of processes to retrieve
:param location: list of partial locations of processes to retrieve
:param fields: list of fields in table to return
:return: Dataframe
"""
processes = Table('TBL_PROCESSES')
locations = Table('TBL_LOCATIONS')
fields = [
f if f != 'LOCATION' else locations.NAME.as_('LOCATION')
for f in fields
]
q = Query \
.from_(processes) \
.left_join(locations).on(pf.Cast(processes.F_LOCATION, 'int') == locations.ID) \
.select(*fields) # FIXME
if name:
q = q.where(Criterion.any([processes.NAME.like(p) for p in name]))
if location:
q = q.where(Criterion.any([locations.NAME.like(p) for p in location]))
processes_dfr = get_df(conn, q)
return processes_dfr
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 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_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 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 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 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 __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]
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 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)
def get_impact_categories(
conn,
method_name=None,
category_name=None,
methods_columns=['ID', 'REF_ID', 'NAME'],
categories_columns=['ID', 'REF_ID', 'NAME', 'REFERENCE_UNIT']):
"""
Get impact categories from sqlite openLCA database. Each category is part of a method but it uniquely
defines the coefficients for each elementary flow.
:param sqlite3.Connection conn: database connection
:param list method_name: partial method names on which to filter
:param list category_name: partial category names on which to filter
:param list methods_columns: table columns to return
:param list categories_columns: table columns to return
:return: Dataframe
"""
categories = Table('TBL_IMPACT_CATEGORIES')
methods = Table('TBL_IMPACT_METHODS')
methods_fields = [
methods.field(c).as_('methods_' + c) for c in methods_columns
]
categories_fields = [
categories.field(c).as_('categories_' + c) for c in categories_columns
]
q = Query \
.from_(categories) \
.left_join(methods).on(categories.F_IMPACT_METHOD == methods.ID) \
.select(*methods_fields, *categories_fields)
if method_name:
q = q.where(Criterion.any([methods.name.like(p) for p in method_name]))
if category_name:
q = q.where(
Criterion.any([categories.name.like(p) for p in category_name]))
return get_df(conn, q)
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 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_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_product_flows(conn, name=None, fields=['ID', 'REF_ID', 'NAME']):
"""
Get product flows from sqlite openLCA database.
:param conn: database connection
:param name: list of partial names of flows to retrieve
:param fields: list of fields in table to return
:return: Dataframe
"""
flows = Table('TBL_FLOWS')
q = Query\
.from_(flows)\
.select(*fields)\
.where(flows.FLOW_TYPE == 'PRODUCT_FLOW')
if name:
q = q.where(Criterion.any([flows.name.like(p) for p in name]))
return get_df(conn, q)
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_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 test_with_generator(self):
crit = Criterion.any(Field(letter) for letter in "abcd")
self.assertEqual(str(crit), '"a" OR "b" OR "c" OR "d"')
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 test_multiple_args_returned_in_chain_of_ors(self):
crit = Criterion.any([Field('a'), Field('b'), Field('c'), Field('d')])
self.assertEqual(str(crit), '"a" OR "b" OR "c" OR "d"')
def test_single_arg_returns_self(self):
f = Field('a')
crit = Criterion.all([f])
self.assertEqual(str(f), str(crit))
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_zero_args_returns_empty_criterion(self):
crit = Criterion.all()
self.assertIsInstance(crit, EmptyCriterion)
def test_with_generator(self):
crit = Criterion.any(Field(letter)
for letter in 'abcd')
self.assertEqual(str(crit), '"a" OR "b" OR "c" OR "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"')
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"')