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 test__cast__timestamp(self):
q1 = Q.from_(self.t).select(fn.Timestamp(self.t.foo))
q2 = Q.from_(self.t).select(fn.Cast(self.t.foo, SqlTypes.TIMESTAMP))
self.assertEqual("SELECT TIMESTAMP(\"foo\") FROM \"abc\"", str(q1))
self.assertEqual("SELECT CAST(\"foo\" AS TIMESTAMP) FROM \"abc\"",
str(q2))
def test__cast__unsigned(self):
q1 = Q.from_(self.t).select(fn.Unsigned(self.t.foo))
q2 = Q.from_(self.t).select(fn.Cast(self.t.foo, SqlTypes.UNSIGNED))
self.assertEqual("SELECT CAST(\"foo\" AS UNSIGNED) FROM \"abc\"",
str(q1))
self.assertEqual("SELECT CAST(\"foo\" AS UNSIGNED) FROM \"abc\"",
str(q2))
def get_process_product_flow_costs(conn, process_ref_ids):
"""
Get the product flow costs from a list of process reference ids using a sqlite openLCA database.
Using the process id get its exchanges and from those extract the product flows and their cost.
:param sqlite3.Connection conn: database connection
:param list[str] | str process_ref_ids: list of process reference ids
:return DataFrame of costs
"""
exchanges = Table('TBL_EXCHANGES')
flows = Table('TBL_FLOWS')
processes = Table('TBL_PROCESSES')
locations = Table('TBL_LOCATIONS')
units = Table('TBL_UNITS')
currencies = Table('TBL_CURRENCIES')
# get the process ids from the ref ids
if isinstance(process_ref_ids, str):
process_ref_ids = [process_ref_ids]
process_ids = processes.select(processes.ID).where(
processes.REF_ID.isin(process_ref_ids))
# sub-query the exchanges table to limit join
sq = Query\
.from_(exchanges) \
.select(exchanges.F_OWNER, exchanges.COST_VALUE, exchanges.F_CURRENCY, exchanges.F_FLOW,
exchanges.F_UNIT, exchanges.RESULTING_AMOUNT_VALUE) \
.where(exchanges.F_OWNER.isin(process_ids))
# join exchanges to flows, processes, locations, units, currencies
q = Query\
.from_(sq) \
.left_join(flows).on(flows.ID == sq.F_FLOW) \
.left_join(processes).on(processes.ID == sq.F_OWNER) \
.left_join(locations).on(pf.Cast(processes.F_LOCATION, 'int') == locations.ID) \
.left_join(units).on(units.ID == sq.F_UNIT) \
.left_join(currencies).on(currencies.ID == sq.F_CURRENCY) \
.select(
processes.REF_ID.as_('PROCESS_REF_ID'), processes.NAME.as_('PROCESS_NAME'),
locations.NAME.as_('LOCATION'),
flows.REF_ID.as_('FLOW_REF_ID'), flows.NAME.as_('FLOW_NAME'), \
sq.COST_VALUE, currencies.NAME.as_('CURRENCY'), units.NAME.as_('UNITS')
)\
.where(flows.FlOW_TYPE == 'PRODUCT_FLOW')
return get_df(conn, q)
def get_process_locations(conn, ref_ids=None):
"""
Get process locations from sqlite openLCA database.
:param conn: database connection
:param ref_ids: list of process reference ids
:return: Dataframe of process id with longitude and latitude
"""
processes = Table('TBL_PROCESSES')
locations = Table('TBL_LOCATIONS')
q = Query \
.from_(processes) \
.left_join(locations).on(pf.Cast(processes.F_LOCATION, 'int') == locations.ID) \
.select(processes.REF_ID, processes.NAME, locations.LATITUDE, locations.LONGITUDE)\
.where(processes.REF_ID.isin(ref_ids)) # FIXME
return get_df(conn, q)
def build_product_flow_cost(process_ref_ids, time):
"""
Build a query to get the product flow costs from a list of process reference ids using a sqlite openLCA database.
:param sqlite3.Connection conn: database connection
:param list[str] process_ref_ids: list of process reference ids
:param list time: list of time labels
:return SQL string
"""
exchanges = Table('TBL_EXCHANGES')
flows = Table('TBL_FLOWS')
processes = Table('TBL_PROCESSES')
locations = Table('TBL_LOCATIONS')
# get the process ids from the ref ids
process_ids = processes.select(processes.ID).where(
processes.REF_ID.isin(process_ref_ids))
# sub-query the exchanges table to limit join
sq = Query\
.from_(exchanges) \
.select(exchanges.F_OWNER, exchanges.COST_VALUE, exchanges.F_CURRENCY, exchanges.F_FLOW,
exchanges.F_UNIT, exchanges.RESULTING_AMOUNT_VALUE) \
.where(exchanges.F_OWNER.isin(process_ids))
# join exchanges to flows, processes, locations
first_time = PseudoColumn("'" + time[0] + "'")
q = Query\
.from_(sq) \
.left_join(flows).on(flows.ID == sq.F_FLOW) \
.left_join(processes).on(processes.ID == sq.F_OWNER) \
.left_join(locations).on(pf.Cast(processes.F_LOCATION, 'int') == locations.ID) \
.select(
flows.REF_ID.as_('F'), processes.REF_ID.as_('P'), first_time.as_('T'), sq.COST_VALUE
)\
.where(flows.FlOW_TYPE == 'PRODUCT_FLOW')
return str(q)
def build_location(process_ref_ids=None):
"""
Create a table of longitudes and latitudes for each material process and its product flow.
:param list[str] process_ref_ids: list of material process reference ids
:return: SQL string
"""
exchanges = Table('TBL_EXCHANGES')
flows = Table('TBL_FLOWS')
processes = Table('TBL_PROCESSES')
locations = Table('TBL_LOCATIONS')
e = Table('e')
# convert reference ids to openLCA process ids
process_id = processes.select(processes.ID).where(
processes.REF_ID.isin(process_ref_ids))
# sub-query exchanges table to limit
sq = Query\
.from_(exchanges) \
.select(exchanges.F_OWNER, exchanges.F_FLOW, exchanges.F_UNIT, exchanges.RESULTING_AMOUNT_VALUE) \
.where(exchanges.F_OWNER.isin(process_id)) \
.as_('e')
# join exchanges to flows
q = Query\
.from_(sq).as_('e') \
.left_join(flows).on(flows.ID == sq.F_FLOW) \
.left_join(processes).on(processes.ID == sq.F_OWNER) \
.left_join(locations).on(pf.Cast(processes.F_LOCATION, 'int') == locations.ID) \
.select(
processes.REF_ID.as_('P_m'), flows.REF_ID.as_('F_m'),
locations.LONGITUDE.as_('X'), locations.LATITUDE.as_('Y')
)\
.where(flows.FlOW_TYPE == 'PRODUCT_FLOW')
return str(q)
def ends_with(field, param=None, value=None, **kwargs):
return functions.Cast(field, SqlTypes.VARCHAR).like(param)
def test__cast__date(self):
q1 = Q.from_(self.t).select(fn.Date(self.t.foo))
q2 = Q.from_(self.t).select(fn.Cast(self.t.foo, SqlTypes.DATE))
self.assertEqual("SELECT DATE(\"foo\") FROM \"abc\"", str(q1))
self.assertEqual("SELECT CAST(\"foo\" AS DATE) FROM \"abc\"", str(q2))
def mysql_contains(field, value):
return functions.Cast(field, SqlTypes.CHAR).like("%{}%".format(value))
def test__cast__as(self):
q = Q.from_(self.t).select(fn.Cast(self.t.foo, SqlTypes.UNSIGNED))
self.assertEqual("SELECT CAST(\"foo\" AS UNSIGNED) FROM \"abc\"",
str(q))
def insensitive_ends_with(field, value):
return functions.Upper(functions.Cast(field, SqlTypes.VARCHAR)).like(
functions.Upper('%{}'.format(value))
)
def insensitive_contains(field, value):
return functions.Upper(functions.Cast(field, SqlTypes.VARCHAR)).like(
functions.Upper('%{}%'.format(value))
)
def test__cast__boolean(self):
q = Q.from_(self.t).select(fn.Cast(self.t.foo, SqlTypes.BOOLEAN))
self.assertEqual("SELECT CAST(\"foo\" AS BOOLEAN) FROM \"abc\"",
str(q))
def test__cast__float(self):
q = Q.from_(self.t).select(fn.Cast(self.t.foo, SqlTypes.FLOAT))
self.assertEqual("SELECT CAST(\"foo\" AS FLOAT) FROM \"abc\"", str(q))
def test__cast__varchar_with_arg(self):
q = Q.from_(self.t).select(fn.Cast(self.t.foo, SqlTypes.VARCHAR(24)))
self.assertEqual("SELECT CAST(\"foo\" AS VARCHAR(24)) FROM \"abc\"",
str(q))
def test__cast__varbinary(self):
q = Q.from_(self.t).select(fn.Cast(self.t.foo, SqlTypes.VARBINARY))
self.assertEqual("SELECT CAST(\"foo\" AS VARBINARY) FROM \"abc\"",
str(q))
def contains(field, param=None, value=None, **kwargs):
return functions.Cast(field, SqlTypes.VARCHAR).like(param)
def mysql_insensitive_ends_with(field, value):
return functions.Upper(functions.Cast(field, SqlTypes.CHAR)).like(
functions.Upper("%{}".format(value)))
def mysql_insensitive_contains(field, value):
return functions.Upper(functions.Cast(field, SqlTypes.CHAR)).like(
functions.Upper("%{}%".format(value)))
def mysql_ends_with(field, value):
return functions.Cast(field, SqlTypes.CHAR).like("%{}".format(value))
def mysql_starts_with(field, value):
return functions.Cast(field, SqlTypes.CHAR).like("{}%".format(value))
def test__cast__long_varchar(self):
q = Q.from_(self.t).select(fn.Cast(self.t.foo, SqlTypes.LONG_VARCHAR))
self.assertEqual("SELECT CAST(\"foo\" AS LONG VARCHAR) FROM \"abc\"",
str(q))
def insensitive_ends_with(field, param=None, value=None, **kwargs):
return functions.Upper(functions.Cast(field, SqlTypes.VARCHAR)).like(
functions.Upper(param))
def test__cast__long_varbinary_with_arg(self):
q = Q.from_(self.t).select(
fn.Cast(self.t.foo, SqlTypes.LONG_VARBINARY(24)))
self.assertEqual(
"SELECT CAST(\"foo\" AS LONG VARBINARY(24)) FROM \"abc\"", str(q))
def contains(field, value):
return functions.Cast(field, SqlTypes.VARCHAR).like('%{}%'.format(value))
def test__cast__integer(self):
q = Q.from_(self.t).select(fn.Cast(self.t.foo, SqlTypes.INTEGER))
self.assertEqual("SELECT CAST(\"foo\" AS INTEGER) FROM \"abc\"",
str(q))
def starts_with(field, value):
return functions.Cast(field, SqlTypes.VARCHAR).like('{}%'.format(value))
def test__cast__numeric(self):
q = Q.from_(self.t).select(fn.Cast(self.t.foo, SqlTypes.NUMERIC))
self.assertEqual("SELECT CAST(\"foo\" AS NUMERIC) FROM \"abc\"",
str(q))
def ends_with(field, value):
return functions.Cast(field, SqlTypes.VARCHAR).like('%{}'.format(value))
