def get_flow_reference_units(conn, flow_ref_ids=None):
"""
Build a query to get flows and reference units from an openLCA database.
:param sqlite3.Connection conn: database connection
:param list[str] | str flow_ref_ids: flow reference id or list of flow reference IDs
:return: DataFrame
"""
flows = Table('TBL_FLOWS')
flow_properties = Table('TBL_FLOW_PROPERTIES')
unit_groups = Table('TBL_UNIT_GROUPS')
units = Table('TBL_UNITS')
# convert reference ids to openLCA flow ids
if flow_ref_ids:
if isinstance(flow_ref_ids, str):
flow_ref_ids = [flow_ref_ids]
flow_id = flows.select(flows.ID).where(flows.REF_ID.isin(flow_ref_ids))
else:
flow_id = flows.select(flows.ID)
# join flows to properties and units
q = Query\
.from_(flows) \
.left_join(flow_properties).on(flows.F_REFERENCE_FLOW_PROPERTY == flow_properties.ID) \
.left_join(unit_groups).on(flow_properties.F_UNIT_GROUP == unit_groups.ID) \
.left_join(units).on(unit_groups.F_REFERENCE_UNIT == units.ID) \
.select(flows.ID, flows.NAME, flows.F_REFERENCE_FLOW_PROPERTY, unit_groups.F_REFERENCE_UNIT,
units.NAME.as_('UNITS_NAME')) \
.where(flows.ID.isin(flow_id))
return get_df(conn, q)
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_product_flow_units(conn, flow_ref_ids=None):
"""
Get product flows and units from the exchanges table in an openLCA database.
:param sqlite3.Connection conn: database connection
:param list[str] flow_ref_ids: list of flow reference IDs
:return: SQL string
"""
flows = Table('TBL_FLOWS')
exchanges = Table('TBL_EXCHANGES')
units = Table('TBL_UNITS')
# convert reference ids to openLCA product flow ids
flow_id = flows.select(flows.ID).where(flows.FLOW_TYPE == 'PRODUCT_FLOW')
if flow_ref_ids:
flow_id = flow_id.where(flows.REF_ID.isin(flow_ref_ids))
# sub-query to restrict to exchanges to product flows
sq = Query \
.from_(exchanges) \
.select(exchanges.ID, exchanges.F_FLOW, exchanges.F_UNIT) \
.where(exchanges.F_FLOW.isin(flow_id))
q = Query \
.from_(sq) \
.left_join(flows).on(sq.F_FLOW == flows.ID) \
.left_join(units).on(sq.F_UNIT == units.ID) \
.select(flows.NAME, units.NAME.as_('UNITS_NAME'))
return get_df(conn, q)
def get_ref_ids(conn, ids, table_name):
"""
Get reference ids from ids in a table
:param conn: database connection
:param ids: list of ids
:param table_name: name of table
:return: dict
"""
tbl = Table(table_name)
q = tbl.select(tbl.REF_ID).where(tbl.ID.isin(ids))
dfr = get_df(conn, q)
return dfr.to_dict('list')
def get_ids(conn, ref_ids, table_name):
"""
Get ids from ref ids in a table
:param conn: database connection
:param ref_ids: list of ref_ids or a ref_id
:param table_name: name of table
:return: dict
"""
if not isinstance(ref_ids, list):
ref_ids = [ref_ids]
tbl = Table(table_name)
q = tbl.select(tbl.ID, tbl.REF_ID).where(tbl.REF_ID.isin(ref_ids))
dfr = get_df(conn, q)
return dict(zip(dfr.ID, dfr.REF_ID))
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_product_flow_units(conn, process_ref_ids, set_name='P'):
"""
Get the product flow units 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 units.
:param sqlite3.Connection conn: database connection
:param list[str] process_ref_ids: list of process reference ids
:param list[str] | str set_name: column name of the index in the returned table
:return DataFrame of units indexed by set_name
"""
exchanges = Table('TBL_EXCHANGES')
flows = Table('TBL_FLOWS')
processes = Table('TBL_PROCESSES')
units = Table('TBL_UNITS')
# 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.F_FLOW,
exchanges.F_UNIT, exchanges.RESULTING_AMOUNT_VALUE) \
.where(exchanges.F_OWNER.isin(process_ids))
# join exchanges to flows, processes, units
q = Query \
.from_(sq) \
.left_join(flows).on(flows.ID == sq.F_FLOW) \
.left_join(processes).on(processes.ID == sq.F_OWNER) \
.left_join(units).on(units.ID == sq.F_UNIT) \
.select(
processes.REF_ID.as_(set_name), units.NAME.as_('Units')
)\
.where(flows.FlOW_TYPE == 'PRODUCT_FLOW')
product_flow_dfr = get_df(conn, q)
product_flow_dfr.set_index(set_name, inplace=True)
return product_flow_dfr
def build_process_elementary_flow(process_ref_ids):
"""
Build a query to create a table of processes versus elementary flows from a sqlite db
sourced from derby.
:param str[list] process_ref_ids: processes reference ids
:return: SQL string
"""
processes = Table('TBL_PROCESSES')
process_ids = processes.select(processes.ID).where(
processes.REF_ID.isin(process_ref_ids))
exchanges = Table('TBL_EXCHANGES')
flows = Table('TBL_FLOWS')
# flow_ids = processes.select(processes.ID).where(processes.REF_ID.isin(process_ref_ids))
# e = Table('e')
# find exchanges corresponding to process ref ids
exchange_query = exchanges \
.select(exchanges.F_OWNER, exchanges.F_FLOW, exchanges.F_UNIT, exchanges.RESULTING_AMOUNT_VALUE) \
.where(exchanges.F_OWNER.isin(process_ids))
# .as_('e')
# product flows
product_flows = Query \
.from_(exchange_query) \
.left_join(flows).on(flows.ID == exchange_query.F_FLOW) \
.select(flows.REF_ID, exchange_query.F_OWNER, exchange_query.RESULTING_AMOUNT_VALUE) \
.where(flows.FlOW_TYPE == 'PRODUCT_FLOW') \
.as_('product_flows')
# left join exchanges to flow and process tables
q = Query \
.from_(exchange_query) \
.left_join(flows).on(flows.ID == exchange_query.F_FLOW) \
.left_join(processes).on(processes.ID == exchange_query.F_OWNER) \
.left_join(product_flows).on(processes.ID == product_flows.F_OWNER) \
.select(
flows.REF_ID.as_('E'), product_flows.REF_ID.as_('F'),
processes.REF_ID.as_('P'), exchange_query.RESULTING_AMOUNT_VALUE.as_('EF')
) \
.where(flows.FlOW_TYPE == 'ELEMENTARY_FLOW')
return str(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 build_product_flow_units(process_ref_ids):
"""
Build a query to get processes and their product flow units.
:param list[str] process_ref_ids: list of process reference ids
:return: SQL string
"""
exchanges = Table('TBL_EXCHANGES')
flows = Table('TBL_FLOWS')
processes = Table('TBL_PROCESSES')
units = Table('TBL_UNITS')
# convert reference ids to openLCA process 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.F_FLOW,
exchanges.F_UNIT, exchanges.RESULTING_AMOUNT_VALUE) \
.where(exchanges.F_OWNER.isin(process_ids))
# join exchanges to flows, processes, units
q = Query\
.from_(sq) \
.left_join(flows).on(flows.ID == sq.F_FLOW) \
.left_join(processes).on(processes.ID == sq.F_OWNER) \
.left_join(units).on(units.ID == sq.F_UNIT) \
.select(
flows.REF_ID.as_('F'), processes.REF_ID.as_('P'), units.NAME.as_('Units')
)\
.where(flows.FlOW_TYPE == 'PRODUCT_FLOW')
return str(q)
def test_table_with_dialect_query_cls(self):
table = Table("abc", query_cls=SQLLiteQuery)
q = table.select("1")
self.assertIs(q.dialect, Dialects.SQLITE)
def test_table_with_default_query_cls(self):
table = Table("abc")
q = table.select("1")
self.assertIs(q.dialect, None)
def handle_loan_request(version, event):
"""Handle a loan request event from the events queue.
Arguments:
version (any): The version to pass to the reddit proxy
event (dict): Describes the request
post (dict):
A self post from reddit-proxy "subreddit_links" (Documented
at reddit-proxy/src/handlers/links.py)
request (dict):
A dictified utils.req_post_interpreter.LoanRequest
"""
post = event['post']
with LazyIntegrations(logger_iden='runners/borrower_request.py#handle_loan_request') as itgs:
itgs.logger.print(
Level.TRACE,
'Detected loan request from /u/{}',
post['author']
)
users = Table('users')
itgs.read_cursor.execute(
users.select(users.id)
.where(users.username == Parameter('%s'))
.get_sql(),
(post['author'].lower(),)
)
row = itgs.read_cursor.fetchone()
if row is None:
itgs.logger.print(
Level.TRACE,
'Ignoring loan request from /u/{} - they do not have any '
+ 'outstanding loans (no history)',
post['author']
)
return
(author_user_id,) = row
loans = Table('loans')
itgs.read_cursor.execute(
loan_format_helper.create_loans_query()
.select(loans.lender_id)
.where(loans.borrower_id == Parameter('%s'))
.where(loans.repaid_at.isnull())
.where(loans.unpaid_at.isnull())
.get_sql(),
(author_user_id,)
)
row = itgs.read_cursor.fetchone()
outstanding_borrowed_loans = []
while row is not None:
outstanding_borrowed_loans.append({
'pretty': loan_format_helper.fetch_loan(row[:-1]),
'lender_id': row[-1]
})
row = itgs.read_cursor.fetchone()
if not outstanding_borrowed_loans:
itgs.logger.print(
Level.TRACE,
'Ignoring loan request from /u/{} - no outstanding loans',
post['author']
)
return
unique_lenders = frozenset(loan['lender_id'] for loan in outstanding_borrowed_loans)
itgs.logger.print(
Level.INFO,
'/u/{} made a loan request while they have {} open loans from '
+ '{} unique lenders: {}. Going to inform each lender which has not '
+ 'opted out of borrower request pms.',
post['author'], len(outstanding_borrowed_loans), len(unique_lenders),
unique_lenders
)
for lender_id in unique_lenders:
lender_settings = get_settings(itgs, lender_id)
if lender_settings.borrower_req_pm_opt_out:
itgs.logger.print(
Level.TRACE,
'Not sending an alert to user {} - opted out',
lender_id
)
continue
pretty_loans = [
loan['pretty']
for loan in outstanding_borrowed_loans
if loan['lender_id'] == lender_id
]
formatted_body = get_response(
itgs,
'borrower_request',
lender_username=pretty_loans[0].lender,
borrower_username=post['author'],
thread='https://www.reddit.com/r/{}/comments/{}/redditloans'.format(
post['subreddit'], post['fullname'][3:]
),
loans=loan_format_helper.format_loan_table(pretty_loans, include_id=True)
)
utils.reddit_proxy.send_request(
itgs, 'borrower_request', version, 'compose',
{
'recipient': pretty_loans[0].lender,
'subject': '/u/{} has made a request thread'.format(post['author']),
'body': formatted_body
}
)