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 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_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_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 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 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 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_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.any([f])
self.assertEqual(str(f), str(crit))
def test_zero_args_returns_empty_criterion(self):
crit = Criterion.any()
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_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.any([f])
self.assertEqual(str(f), str(crit))
def test_zero_args_returns_empty_criterion(self):
crit = Criterion.any()
self.assertIsInstance(crit, EmptyCriterion)