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))