def get_and_format_all_or_summary(itgs: LazyIntegrations, username: str, threshold: int = 5):
"""Checks how many loans the given user has. If it's at or above the
threshold, this fetches the summary info on the user and formats it,
then returns the formatted summary. If it's below the threshold,
fetches all the loans for that user, formats it into a table, and returns
the formatted table.
Note that when using the summary format this will include the users in-
progress loans as a table as well so they stand out.
Arguments:
itgs (LazyIntegrations): The integrations to use for getting info
username (str): The username to check
threshold (int): The number of loans required for a summary instead of
just all the loans in a table
Returns:
(str): A markdown representation of the users loans
"""
loans = Table('loans')
users = Table('users')
lenders = users.as_('lenders')
borrowers = users.as_('borrowers')
itgs.read_cursor.execute(
Query.from_(loans).select(Count(Star()))
.join(lenders).on(lenders.id == loans.lender_id)
.join(borrowers).on(borrowers.id == loans.borrower_id)
.where(
(lenders.username == Parameter('%s'))
| (borrowers.username == Parameter('%s'))
)
.where(loans.deleted_at.isnull())
.get_sql(),
(username.lower(), username.lower())
)
(cnt,) = itgs.read_cursor.fetchone()
if cnt < threshold:
return format_loan_table(get_all_loans(itgs, username))
return format_loan_summary(*get_summary_info(itgs, username))
def get_basic_loan_info_query():
"""Get the basic query that we use for fetching a loans information"""
loans = Table('loans')
usrs = Table('users')
moneys = Table('moneys')
lenders = usrs.as_('lenders')
borrowers = usrs.as_('borrowers')
principals = moneys.as_('principals')
principal_currencies = Table('currencies').as_('principal_currencies')
principal_repayments = moneys.as_('principal_repayments')
repayment_events = Table('loan_repayment_events')
latest_repayments = Table('latest_repayments')
query = (Query.with_(
Query.from_(repayment_events).select(
repayment_events.loan_id,
ppfns.Max(
repayment_events.created_at).as_('latest_created_at')).groupby(
repayment_events.loan_id),
'latest_repayments').from_(loans).select(
lenders.username, borrowers.username, principal_currencies.code,
principal_currencies.symbol, principal_currencies.symbol_on_left,
principal_currencies.exponent, principals.amount,
principal_repayments.amount, loans.created_at,
latest_repayments.latest_created_at, loans.repaid_at,
loans.unpaid_at, loans.deleted_at).join(lenders).on(
lenders.id == loans.lender_id).join(borrowers).on(
borrowers.id == loans.borrower_id).join(principals).on(
principals.id ==
loans.principal_id).join(principal_currencies).on(
principal_currencies.id == principals.currency_id).
join(principal_repayments).on(
principal_repayments.id ==
loans.principal_repayment_id).left_join(latest_repayments).on(
latest_repayments.loan_id == loans.id))
return query
def create_loans_query():
"""Create a query which will convert every loan into a single row which can
be converted to a Loan object using fetch_loan.
Returns:
(Query): A query object which is unordered and not limited with no
restriction on which loans are included (except excluding deleted),
but will return one row per loan which can be interpreted with
fetch_loan.
"""
loans = Table('loans')
users = Table('users')
moneys = Table('moneys')
currencies = Table('currencies')
loan_creation_infos = Table('loan_creation_infos')
lenders = users.as_('lenders')
borrowers = users.as_('borrowers')
principals = moneys.as_('principals')
principal_currencies = currencies.as_('principal_currencies')
principal_repayments = moneys.as_('principal_repayments')
principal_repayment_currencies = currencies.as_('principal_repayment_currencies')
return (
Query.from_(loans)
.select(
loans.id,
lenders.username,
borrowers.username,
principals.amount,
principal_currencies.code,
principal_currencies.symbol,
principal_currencies.symbol_on_left,
principal_currencies.exponent,
principal_repayments.amount,
principal_repayment_currencies.code,
principal_repayment_currencies.symbol,
principal_repayment_currencies.symbol_on_left,
principal_repayment_currencies.exponent,
loan_creation_infos.type,
loan_creation_infos.parent_fullname,
loan_creation_infos.comment_fullname,
loans.created_at,
loans.repaid_at,
loans.unpaid_at
)
.join(lenders).on(lenders.id == loans.lender_id)
.join(borrowers).on(borrowers.id == loans.borrower_id)
.join(principals).on(principals.id == loans.principal_id)
.join(principal_currencies).on(principal_currencies.id == principals.currency_id)
.join(principal_repayments).on(principal_repayments.id == loans.principal_repayment_id)
.join(principal_repayment_currencies)
.on(principal_repayment_currencies.id == principal_repayments.currency_id)
.left_join(loan_creation_infos).on(loan_creation_infos.loan_id == loans.id)
.where(loans.deleted_at.isnull())
)
def handle_loan_unpaid(version, body):
time.sleep(5) # give the transaction some time to complete
loan_unpaid_event_id = body['loan_unpaid_event_id']
with LazyIntegrations(logger_iden=LOGGER_IDEN) as itgs:
itgs.logger.print(Level.DEBUG, 'Detected loan unpaid event: {}', loan_unpaid_event_id)
loan_unpaid_events = Table('loan_unpaid_events')
loans = Table('loans')
usrs = Table('users')
borrowers = usrs.as_('borrowers')
lenders = usrs.as_('lenders')
itgs.read_cursor.execute(
Query.from_(loan_unpaid_events)
.join(loans).on(loans.id == loan_unpaid_events.loan_id)
.join(borrowers).on(borrowers.id == loans.borrower_id)
.join(lenders).on(lenders.id == loans.lender_id)
.select(borrowers.username, lenders.username)
.where(loan_unpaid_events.id == Parameter('%s'))
.get_sql(),
(loan_unpaid_event_id,)
)
row = itgs.read_cursor.fetchone()
if row is None:
itgs.logger.print(
Level.WARN, 'Loan unpaid event {} did not exist!', loan_unpaid_event_id)
return
(username, lender_username) = row
itgs.logger.print(
Level.TRACE,
'Ensuring /u/{} is moderator or banned from unpaid event {}',
username, loan_unpaid_event_id
)
info = perms.manager.fetch_info(itgs, username, RPIDEN, version)
if info is None:
itgs.logger.print(
Level.INFO,
'/u/{} defaulted on a loan then deleted their account.',
username
)
return
if info['borrow_banned']:
itgs.logger.print(
Level.DEBUG,
'/u/{} defaulted on a loan but they are already banned.',
username
)
return
if info['borrow_moderator']:
itgs.logger.print(
Level.INFO,
'/u/{} defaulted on a loan but is a moderator - no ban',
username
)
return
if info['borrow_approved_submitter']:
itgs.logger.print(
Level.INFO,
'/u/{} defaulted on a loan but is an approved submitter - no ban',
username
)
# easy to forget about approved submitters
utils.reddit_proxy.send_request(
itgs, RPIDEN, version, 'compose',
{
'recipient': '/r/borrow',
'subject': 'Approved Submitter Unpaid Loan',
'body': (
'/u/{} defaulted on a loan but did not get banned since they are '
+ 'an approved submitter.'
).format(username)
}
)
return
itgs.logger.print(
Level.TRACE,
'Banning /u/{} because they defaulted on a loan',
username
)
substitutions = {
'borrower_username': username,
'lender_username': lender_username
}
utils.reddit_proxy.send_request(
itgs, RPIDEN, version, 'ban_user',
{
'subreddit': 'borrow',
'username': username,
'message': get_response(itgs, 'unpaid_ban_message', **substitutions),
'note': get_response(itgs, 'unpaid_ban_note', **substitutions)
}
)
itgs.logger.print(
Level.INFO,
'Banned /u/{} on /r/borrow - failed to repay loan with /u/{}',
username,
lender_username
)
perms.manager.flush_cache(itgs, username)
def get_csv_dump(alt_authorization: str = None, authorization=Header(None)):
"""Get a csv of all loans where the columns are
id, lender_id, borrower_id, currency, principal_minor, principal_cents,
principal_repayment_minor, principal_repayment_cents, created_at,
last_repayment_at, repaid_at, unpaid_at
This endpoint is _very_ expensive for us. Without a users ratelimit being
increased they will almost certainly not even be able to use this endpoint
once. We charge 5 * rows * log(rows) toward the quota and do not allow users
which contribute to the global ratelimit. It is NOT cheaper to use this
endpoint compared to just walking the index endpoint.
This mainly exists for users which are willing to pay for a csv dump. You
may use a query parameter for authorization instead of a header.
"""
if authorization is None and alt_authorization is None:
return Response(status_code=401)
if authorization is None:
authorization = f'bearer {alt_authorization}'
attempt_request_cost = 1
check_request_cost_cost = 10
headers = {'x-request-cost': str(attempt_request_cost)}
with LazyItgs() as itgs:
user_id, _, perms = users.helper.get_permissions_from_header(
itgs, authorization, ratelimit_helper.RATELIMIT_PERMISSIONS
)
settings = (
ratelimit_helper.USER_RATELIMITS
if user_id is None
else get_settings(itgs, user_id)
)
if not ratelimit_helper.check_ratelimit(
itgs, user_id, perms, attempt_request_cost,
settings=settings):
return Response(status_code=429, headers=headers)
if user_id is None:
return Response(status_code=403, headers=headers)
if settings.global_ratelimit_applies:
return Response(status_code=403, headers=headers)
headers['x-request-cost'] = (
str(attempt_request_cost + check_request_cost_cost)
)
if not ratelimit_helper.check_ratelimit(
itgs, user_id, perms, check_request_cost_cost,
settings=settings):
return Response(status_code=429, headers=headers)
loans = Table('loans')
itgs.read_cursor.execute(
Query.from_(loans).select(Count(Star())).get_sql()
)
(cnt_loans,) = itgs.read_cursor.fetchone()
real_request_cost = (
5 * cnt_loans * max(1, math.ceil(math.log(cnt_loans)))
)
headers['x-request-cost'] = (
str(
attempt_request_cost
+ check_request_cost_cost
+ real_request_cost
)
)
if not ratelimit_helper.check_ratelimit(
itgs, user_id, perms, real_request_cost,
settings=settings):
return Response(status_code=429, headers=headers)
moneys = Table('moneys')
currencies = Table('currencies')
principals = moneys.as_('principals')
principal_repayments = moneys.as_('principal_repayments')
repayment_events = Table('loan_repayment_events')
query = (
Query.from_(loans)
.select(
loans.id,
loans.lender_id,
loans.borrower_id,
currencies.code,
principals.amount,
principals.amount_usd_cents,
principal_repayments.amount,
principal_repayments.amount_usd_cents,
loans.created_at,
Max(repayment_events.created_at),
loans.repaid_at,
loans.unpaid_at
)
.join(principals)
.on(principals.id == loans.principal_id)
.join(currencies)
.on(currencies.id == principals.currency_id)
.join(principal_repayments)
.on(principal_repayments.id == loans.principal_repayment_id)
.left_join(repayment_events)
.on(repayment_events.loan_id == loans.id)
.groupby(
loans.id,
currencies.id,
principals.id,
principal_repayments.id
)
)
headers['Content-Type'] = 'text/csv'
headers['Content-Disposition'] = 'attachment; filename="loans.csv"'
headers['Cache-Control'] = 'public, max-age=86400'
return StreamingResponse(
query_generator(
query.get_sql(),
','.join((
'id', 'lender_id', 'borrower_id', 'currency', 'principal_minor',
'principal_cents', 'principal_repayment_minor', 'principal_repayment_cents',
'created_at', 'last_repayment_at', 'repaid_at', 'unpaid_at'
))
),
status_code=200,
headers=headers
)
def index_loans(request: Request,
id: int = 0,
after_time: int = 0,
before_time: int = 0,
borrower_id: int = 0,
lender_id: int = 0,
includes_user_id: int = 0,
borrower_name: str = '',
lender_name: str = '',
includes_user_name: str = '',
principal_cents: int = 0,
principal_repayment_cents: int = -1,
unpaid: int = -1,
repaid: int = -1,
format: int = 2,
limit: int = 10):
id = _zero_to_none(id)
after_time = _zero_to_none(after_time)
before_time = _zero_to_none(before_time)
borrower_id = _zero_to_none(borrower_id)
lender_id = _zero_to_none(lender_id)
includes_user_id = _zero_to_none(includes_user_id)
borrower_name = _blank_to_none(borrower_name)
lender_name = _blank_to_none(lender_name)
includes_user_name = _blank_to_none(includes_user_name)
principal_cents = _zero_to_none(principal_cents)
principal_repayment_cents = _neg1_to_none(principal_repayment_cents)
unpaid = _neg1_to_none(unpaid)
repaid = _neg1_to_none(repaid)
limit = _zero_to_none(limit)
attempt_request_cost = 5
headers = {'x-request-cost': str(attempt_request_cost)}
with LazyItgs() as itgs:
auth = find_bearer_token(request)
user_id, _, perms = users.helper.get_permissions_from_header(
itgs, auth, ratelimit_helper.RATELIMIT_PERMISSIONS)
resp = try_handle_deprecated_call(itgs, request, SLUG, user_id=user_id)
if resp is not None:
return resp
if not ratelimit_helper.check_ratelimit(itgs, user_id, perms,
attempt_request_cost):
return JSONResponse(content=RATELIMIT_RESPONSE.dict(),
status_code=429,
headers=headers)
if limit is not None and (limit < 0 or limit >= 1000):
return JSONResponse(content=PHPErrorResponse(errors=[
PHPError(
error_type='INVALID_PARAMETER',
error_message=(
'Limit must be 0 or a positive integer less than 1000'
))
]).dict(),
status_code=400)
if limit is None and user_id is None:
headers[
'x-limit-warning'] = 'unauthed requests limit=0 replaced with limit=100'
limit = 100
if format not in (0, 1, 2, 3):
return JSONResponse(content=PHPErrorResponse(errors=[
PHPError(error_type='INVALID_PARAMETER',
error_message=('Format must be 0, 1, 2, or 3'))
]).dict(),
status_code=400)
loans = Table('loans')
if limit is None:
real_request_cost = 100
else:
real_request_cost = min(100, limit)
if format == 0:
real_request_cost = math.ceil(math.log(real_request_cost + 1))
elif format < 3:
# Cost needs to be greater than loans show
real_request_cost = 25 + real_request_cost * 2
else:
# We need to ensure the cost is greater than using the /users show
# endpoint for getting usernames
real_request_cost = 25 + math.ceil(real_request_cost * 4.1)
headers['x-request-cost'] = str(attempt_request_cost +
real_request_cost)
if not ratelimit_helper.check_ratelimit(itgs, user_id, perms,
real_request_cost):
return JSONResponse(content=RATELIMIT_RESPONSE.dict(),
status_code=429,
headers=headers)
moneys = Table('moneys')
principals = moneys.as_('principals')
principal_repayments = moneys.as_('principal_repayments')
usrs = Table('users')
lenders = usrs.as_('lenders')
borrowers = usrs.as_('borrowers')
query = (Query.from_(loans).where(loans.deleted_at.isnull()).orderby(
loans.id, order=Order.desc))
params = []
joins = set()
def _add_param(val):
params.append(val)
return Parameter(f'${len(params)}')
def _ensure_principals():
nonlocal query
if 'principals' in joins:
return
joins.add('principals')
query = (query.join(principals).on(
principals.id == loans.principal_id))
def _ensure_principal_repayments():
nonlocal query
if 'principal_repayments' in joins:
return
joins.add('principal_repayments')
query = (query.join(principal_repayments).on(
principal_repayments.id == loans.principal_repayment_id))
def _ensure_lenders():
nonlocal query
if 'lenders' in joins:
return
joins.add('lenders')
query = (query.join(lenders).on(lenders.id == loans.lender_id))
def _ensure_borrowers():
nonlocal query
if 'borrowers' in joins:
return
joins.add('borrowers')
query = (query.join(borrowers).on(
borrowers.id == loans.borrower_id))
if id is not None:
query = query.where(loans.id == _add_param(id))
if after_time is not None:
query = (query.where(loans.created_at > _add_param(
datetime.fromtimestamp(after_time / 1000.0))))
if before_time is not None:
query = (query.where(loans.created_at < _add_param(
datetime.fromtimestamp(before_time / 1000.0))))
if principal_cents is not None:
_ensure_principals()
query = (query.where(
principals.amount_usd_cents == _add_param(principal_cents)))
if principal_repayment_cents is not None:
_ensure_principal_repayments()
query = (query.where(principal_repayments.amount_usd_cents ==
_add_param(principal_repayment_cents)))
if borrower_id is not None:
query = (query.where(loans.borrower_id == _add_param(borrower_id)))
if lender_id is not None:
query = (query.where(loans.lender_id == _add_param(lender_id)))
if includes_user_id is not None:
prm = _add_param(includes_user_id)
query = (query.where((loans.borrower_id == prm)
| (loans.lender_id == prm)))
if borrower_name is not None:
_ensure_borrowers()
query = (query.where(
borrowers.username == _add_param(borrower_name.lower())))
if lender_name is not None:
_ensure_lenders()
query = (query.where(
lenders.username == _add_param(lender_name.lower())))
if includes_user_name is not None:
_ensure_lenders()
_ensure_borrowers()
prm = _add_param(includes_user_name)
query = (query.where((lenders.username == prm)
| (borrowers.username == prm)))
if unpaid is not None:
if unpaid:
query = query.where(loans.unpaid_at.notnull())
else:
query = query.where(loans.unpaid_at.isnull())
if repaid is not None:
if repaid:
query = query.where(loans.repaid_at.notnull())
else:
query = query.where(loans.repaid_at.isnull())
if limit is not None:
query = query.limit(limit)
query = query.select(loans.id)
if format > 0:
_ensure_principals()
_ensure_principal_repayments()
event_tables = (Table('loan_repayment_events'),
Table('loan_unpaid_events'),
Table('loan_admin_events'))
latest_events = Table('latest_events')
query = (query.with_(
Query.from_(loans).select(
loans.id.as_('loan_id'),
Greatest(loans.created_at,
*(tbl.created_at for tbl in event_tables
)).as_('latest_event_at')).groupby(loans.id),
'latest_events').left_join(latest_events).on(
latest_events.loan_id == loans.id).select(
loans.lender_id, loans.borrower_id,
principals.amount_usd_cents,
principal_repayments.amount_usd_cents, (Case().when(
loans.unpaid_at.isnull(), 'false').else_('true')),
Cast(
Extract('epoch', loans.created_at) * 1000,
'bigint'),
Cast(
Extract('epoch', latest_events.latest_event_at) *
1000, 'bigint')))
if format == 3:
creation_infos = Table('loan_creation_infos')
_ensure_borrowers()
_ensure_lenders()
query = (query.join(creation_infos).on(
creation_infos.loan_id == loans.id).select(
Function('SUBSTRING', creation_infos.parent_fullname,
4),
Function('SUBSTRING', creation_infos.comment_fullname,
4), lenders.username, borrowers.username))
sql, args = convert_numbered_args(query.get_sql(), params)
headers['Cache-Control'] = 'public, max-age=600'
if format == 0:
return _UltraCompactResponse((sql, args), headers,
'LOANS_ULTRACOMPACT')
elif format == 1:
return _CompactResponse((sql, args), headers, 'LOANS_COMPACT')
elif format == 2:
return _StandardResponse((sql, args), headers, 'LOANS_STANDARD')
else:
return _ExtendedResponse((sql, args), headers, 'LOANS_EXTENDED')
def update_stats():
the_time = time.time()
with LazyIntegrations(logger_iden=LOGGER_IDEN) as itgs:
plots = {}
for unit in ('count', 'usd'):
plots[unit] = {}
frequency = 'monthly'
frequency_unit = 'month'
plots[unit][frequency] = {
'title': f'{frequency} {unit}'.title(),
'x_axis': frequency_unit.title(),
'y_axis': unit.title(),
'generated_at': the_time,
'data': {
# Categories will be added later
'series': {} # Will be listified later
}
}
for style in ('lent', 'repaid', 'unpaid'):
plots[unit][frequency]['data']['series'][style] = {}
loans = Table('loans')
moneys = Table('moneys')
principals = moneys.as_('principals')
time_parts = {
'month': DatePart('month', loans.created_at),
'year': DatePart('year', loans.created_at)
}
query = (
Query.from_(loans)
.join(principals).on(principals.id == loans.principal_id)
.select(
time_parts['year'], # Which month are we counting?
time_parts['month'], # Which year are we counting?
Count(Star()), # Total # of Loans Lent In Interval
Sum(principals.amount_usd_cents) # Total USD of Loans Lent In Interval
)
.groupby(time_parts['year'], time_parts['month'])
.where(loans.deleted_at.isnull())
)
sql = query.get_sql()
itgs.logger.print(Level.TRACE, sql)
count_series = plots['count']['monthly']['data']['series']['lent']
usd_series = plots['usd']['monthly']['data']['series']['lent']
itgs.read_cursor.execute(sql)
row = itgs.read_cursor.fetchone()
while row is not None:
count_series[(row[0], row[1])] = row[2]
usd_series[(row[0], row[1])] = row[3] / 100
row = itgs.read_cursor.fetchone()
time_parts = {
'month': DatePart('month', loans.repaid_at),
'year': DatePart('year', loans.repaid_at)
}
query = (
Query.from_(loans)
.join(principals).on(principals.id == loans.principal_id)
.select(
time_parts['year'],
time_parts['month'],
Count(Star()),
Sum(principals.amount_usd_cents)
)
.groupby(time_parts['year'], time_parts['month'])
.where(loans.deleted_at.isnull())
.where(loans.repaid_at.notnull())
)
sql = query.get_sql()
itgs.logger.print(Level.TRACE, sql)
count_series = plots['count']['monthly']['data']['series']['repaid']
usd_series = plots['usd']['monthly']['data']['series']['repaid']
itgs.read_cursor.execute(sql)
row = itgs.read_cursor.fetchone()
while row is not None:
count_series[(row[0], row[1])] = row[2]
usd_series[(row[0], row[1])] = row[3] / 100
row = itgs.read_cursor.fetchone()
time_parts = {
'month': DatePart('month', loans.unpaid_at),
'year': DatePart('year', loans.unpaid_at)
}
query = (
Query.from_(loans)
.join(principals).on(principals.id == loans.principal_id)
.select(
time_parts['year'],
time_parts['month'],
Count(Star()),
Sum(principals.amount_usd_cents)
)
.groupby(time_parts['year'], time_parts['month'])
.where(loans.deleted_at.isnull())
.where(loans.unpaid_at.notnull())
)
sql = query.get_sql()
itgs.logger.print(Level.TRACE, sql)
count_series = plots['count']['monthly']['data']['series']['unpaid']
usd_series = plots['usd']['monthly']['data']['series']['unpaid']
itgs.read_cursor.execute(sql)
row = itgs.read_cursor.fetchone()
while row is not None:
count_series[(row[0], row[1])] = row[2]
usd_series[(row[0], row[1])] = row[3] / 100
row = itgs.read_cursor.fetchone()
# We've now fleshed out all the monthly plots. We first standardize the
# series to a categories list and series list, rather than a series dict.
# So series[k]: {"foo": 3, "bar": 2} -> "categories": ["foo", "bar"],
# series[k]: [3, 2]. This introduces time-based ordering
all_keys = set()
for unit_dict in plots.values():
for plot in unit_dict.values():
for series in plot['data']['series'].values():
for key in series.keys():
all_keys.add(key)
categories = sorted(all_keys)
categories_pretty = [f'{int(year)}-{int(month)}' for (year, month) in categories]
for unit_dict in plots.values():
for plot in unit_dict.values():
plot['data']['categories'] = categories_pretty
for key in tuple(plot['data']['series'].keys()):
dict_fmted = plot['data']['series'][key]
plot['data']['series'][key] = [
dict_fmted.get(cat, 0) for cat in categories
]
# We now map series from a dict to a list, moving the key into name
for unit_dict in plots.values():
for plot in unit_dict.values():
plot['data']['series'] = [
{
'name': key.title(),
'data': val
}
for (key, val) in plot['data']['series'].items()
]
# We can now augment monthly to quarterly. 1-3 -> q1, 4-6 -> q2, etc.
def map_month_to_quarter(month):
return int((month - 1) / 3) + 1
quarterly_categories = []
for (year, month) in categories:
quarter = map_month_to_quarter(month)
pretty_quarter = f'{int(year)}Q{quarter}'
if not quarterly_categories or quarterly_categories[-1] != pretty_quarter:
quarterly_categories.append(pretty_quarter)
for unit, unit_dict in plots.items():
monthly_plot = unit_dict['monthly']
quarterly_plot = {
'title': f'Quarterly {unit}'.title(),
'x_axis': 'Quarter',
'y_axis': unit.title(),
'generated_at': the_time,
'data': {
'categories': quarterly_categories,
'series': []
}
}
unit_dict['quarterly'] = quarterly_plot
for series in monthly_plot['data']['series']:
quarterly_series = []
quarterly_plot['data']['series'].append({
'name': series['name'],
'data': quarterly_series
})
last_year_and_quarter = None
for idx, (year, month) in enumerate(categories):
quarter = map_month_to_quarter(month)
year_and_quarter = (year, quarter)
if year_and_quarter == last_year_and_quarter:
quarterly_series[-1] += series['data'][idx]
else:
last_year_and_quarter = year_and_quarter
quarterly_series.append(series['data'][idx])
# And finally we fill caches
for unit, unit_dict in plots.items():
for frequency, plot in unit_dict.items():
cache_key = f'stats/loans/{unit}/{frequency}'
jsonified = json.dumps(plot)
itgs.logger.print(Level.TRACE, '{} -> {}', cache_key, jsonified)
encoded = jsonified.encode('utf-8')
itgs.cache.set(cache_key, encoded)
itgs.logger.print(Level.INFO, 'Successfully updated loans statistics')
def get_loan_events(itgs, loan_id, perms):
"""Get the loan events for the given loan if the user has access to view
the loan. The details of each event may also depend on what the user has
access to. Returns the events in ascending (oldest to newest) order.
"""
loans = Table('loans')
usrs = Table('users')
moneys = Table('moneys')
q = (Query.from_(loans).select(
loans.created_at).where(loans.id == Parameter('%s')))
if DELETED_LOANS_PERM not in perms:
q = q.where(loans.deleted_at.isnull())
itgs.read_cursor.execute(q.get_sql(), (loan_id, ))
row = itgs.read_cursor.fetchone()
if row is None:
return []
(created_at, ) = row
result = []
creation_infos = Table('loan_creation_infos')
itgs.read_cursor.execute(
Query.from_(creation_infos).select(
creation_infos.type, creation_infos.parent_fullname,
creation_infos.comment_fullname).where(
creation_infos.loan_id == Parameter('%s')).get_sql(),
(loan_id, ))
row = itgs.read_cursor.fetchone()
if row is not None:
(creation_type, parent_fullname, comment_fullname) = row
result.append(
models.CreationLoanEvent(
event_type='creation',
occurred_at=created_at.timestamp(),
creation_type=creation_type,
creation_permalink=(
None if creation_type != 0 else
'https://www.reddit.com/comments/{}/redditloans/{}'.format(
parent_fullname[3:], comment_fullname[3:]))))
admin_events = Table('loan_admin_events')
admins = usrs.as_('admins')
old_principals = moneys.as_('old_principals')
new_principals = moneys.as_('new_principals')
old_principal_repayments = moneys.as_('old_principal_repayments')
new_principal_repayments = moneys.as_('new_principal_repayments')
itgs.read_cursor.execute(
Query.from_(admin_events).select(
admins.username, admin_events.reason, old_principals.amount,
new_principals.amount, old_principal_repayments.amount,
new_principal_repayments.amount, admin_events.old_created_at,
admin_events.new_created_at, admin_events.old_repaid_at,
admin_events.new_repaid_at, admin_events.old_unpaid_at,
admin_events.new_unpaid_at, admin_events.old_deleted_at,
admin_events.new_deleted_at,
admin_events.created_at).join(admins).on(
admins.id == admin_events.admin_id).join(old_principals).on(
old_principals.id ==
admin_events.old_principal_id).join(new_principals).on(
new_principals.id == admin_events.new_principal_id).
join(old_principal_repayments).on(
old_principal_repayments.id == admin_events.
old_principal_repayment_id).join(new_principal_repayments).on(
new_principal_repayments.id ==
admin_events.new_principal_repayment_id).where(
admin_events.loan_id == Parameter('%s')).get_sql(),
(loan_id, ))
can_view_admins = VIEW_ADMIN_EVENT_AUTHORS_PERM in perms
row = itgs.read_cursor.fetchone()
while row is not None:
result.append(
models.AdminLoanEvent(event_type='admin',
occurred_at=row[-1].timestamp(),
admin=(row[0] if can_view_admins else None),
reason=(row[1] if can_view_admins else None),
old_principal_minor=row[2],
new_principal_minor=row[3],
old_principal_repayment_minor=row[4],
new_principal_repayment_minor=row[5],
old_created_at=row[6].timestamp(),
new_created_at=row[7].timestamp(),
old_repaid_at=row[8].timestamp()
if row[8] is not None else None,
new_repaid_at=row[9].timestamp()
if row[9] is not None else None,
old_unpaid_at=row[10].timestamp()
if row[10] is not None else None,
new_unpaid_at=row[11].timestamp()
if row[11] is not None else None,
old_deleted_at=row[12].timestamp()
if row[12] is not None else None,
new_deleted_at=row[13].timestamp()
if row[13] is not None else None))
row = itgs.read_cursor.fetchone()
repayment_events = Table('loan_repayment_events')
repayments = moneys.as_('repayments')
itgs.read_cursor.execute(
Query.from_(repayment_events).select(
repayments.amount,
repayment_events.created_at).join(repayments).on(
repayments.id == repayment_events.repayment_id).where(
repayment_events.loan_id == Parameter('%s')).get_sql(),
(loan_id, ))
row = itgs.read_cursor.fetchone()
while row is not None:
result.append(
models.RepaymentLoanEvent(event_type='repayment',
occurred_at=row[1].timestamp(),
repayment_minor=row[0]))
row = itgs.read_cursor.fetchone()
unpaid_events = Table('loan_unpaid_events')
itgs.read_cursor.execute(
Query.from_(unpaid_events).select(
unpaid_events.unpaid, unpaid_events.created_at).where(
unpaid_events.loan_id == Parameter('%s')).get_sql(),
(loan_id, ))
row = itgs.read_cursor.fetchone()
while row is not None:
result.append(
models.UnpaidLoanEvent(event_type='unpaid',
occurred_at=row[1].timestamp(),
unpaid=row[0]))
row = itgs.read_cursor.fetchone()
result.sort(key=lambda x: x.occurred_at)
return result
async def prefetch(self, instance_list: list, related_query: "QuerySet[MODEL]") -> list:
instance_id_set = [
instance._meta.pk.db_value(instance.pk, instance)
for instance in instance_list
]
field_object: ManyToManyField = self.model._meta.fields_map[self.model_field_name]
through_table = Table(field_object.through)
subquery = (
self.model._meta.db.query_class.from_(through_table)
.select(
through_table[field_object.backward_key],
through_table[field_object.forward_key],
)
.where(through_table[field_object.backward_key].isin(instance_id_set))
)
related_query_table = related_query.model._meta.table()
related_pk_field = related_query.model._meta.pk_db_column
context = related_query.create_query_context(parent_context=None)
context.query = (
context.query.join(subquery)
.on(getattr(subquery, field_object.forward_key) == related_query_table[related_pk_field])
.select(getattr(subquery, field_object.backward_key))
)
context.push(
related_query.model,
related_query_table,
{field_object.through: through_table.as_(subquery.alias)}
)
related_query._add_query_details(context)
#
# Following few lines are transformed version of these lines, when I was trying
# to convert row dictionary decoding to ordered (list) decoding.
#
# relations = [
# (
# self.model._meta.pk.to_python_value(e[field_object.backward_key]),
# related_query.model._init_from_db_row(iter(zip(db_columns, e))),
# )
# for e in raw_results
# ]
#
_, db_columns, raw_results = await self.model._meta.db.execute_query(context.query.get_sql())
relations: List[Tuple[Any, MODEL]] = []
for row in raw_results:
row_iter = iter(zip(db_columns, row))
related_instance = related_query.model._init_from_db_row(row_iter, related_query._select_related)
db_column, value = next(row_iter) # row[field_object.backward_key]
backward_key = self.model._meta.pk.to_python_value(value)
relations.append((backward_key, related_instance))
related_executor = self.model._meta.db.executor_class(
model=related_query.model,
db=self.model._meta.db,
prefetch_map=related_query._prefetch_map,
prefetch_queries=related_query._prefetch_queries,
)
await related_executor._execute_prefetch_queries([item for _, item in relations])
relation_map: Dict[Any, List[MODEL]] = {}
for k, item in relations:
relation_map.setdefault(k, []).append(item)
for instance in instance_list:
relation_container = getattr(instance, self.model_field_name)
relation_container._set_objects(relation_map.get(instance.pk, []))
return instance_list
def revoke_permission(id: int, perm: str, authorization=Header(None)):
"""This immediately takes effect on all corresponding auth tokens."""
if authorization is None:
return Response(status_code=401)
request_cost = 5
with LazyItgs(no_read_only=True) as itgs:
user_id, _, perms = users.helper.get_permissions_from_header(
itgs, authorization,
(helper.VIEW_OTHERS_AUTHENTICATION_METHODS_PERM,
helper.CAN_MODIFY_OTHERS_AUTHENTICATION_METHODS_PERM,
helper.CAN_VIEW_DELETED_AUTHENTICATION_METHODS_PERM, perm,
*ratelimit_helper.RATELIMIT_PERMISSIONS))
if not ratelimit_helper.check_ratelimit(itgs, user_id, perms,
request_cost):
return Response(status_code=429,
headers={'x-request-cost': str(request_cost)})
if user_id is None or perm not in perms:
return Response(status_code=403,
headers={'x-request-cost': str(request_cost)})
can_view_others_auth_methods = helper.VIEW_OTHERS_AUTHENTICATION_METHODS_PERM in perms
can_modify_others_auth_methods = (
helper.CAN_MODIFY_OTHERS_AUTHENTICATION_METHODS_PERM in perms)
can_view_deleted_auth_methods = helper.CAN_VIEW_DELETED_AUTHENTICATION_METHODS_PERM in perms
auth_methods = Table('password_authentications')
authtokens = Table('authtokens')
authtoken_perms = Table('authtoken_permissions')
permissions = Table('permissions')
itgs.read_cursor.execute(
Query.from_(auth_methods).select(
auth_methods.user_id, auth_methods.deleted).where(
auth_methods.id == Parameter('%s')).get_sql(), (id, ))
row = itgs.read_cursor.fetchone()
if row is None:
return Response(status_code=404,
headers={'x-request-cost': str(request_cost)})
(auth_method_user_id, deleted) = row
if deleted:
if not can_view_deleted_auth_methods:
return Response(status_code=404,
headers={'x-request-cost': str(request_cost)})
return Response(status_code=403,
headers={'x-request-cost': str(request_cost)})
if auth_method_user_id != user_id:
if not can_view_others_auth_methods:
return Response(status_code=404,
headers={'x-request-cost': str(request_cost)})
if not can_modify_others_auth_methods:
return Response(status_code=403,
headers={'x-request-cost': str(request_cost)})
auth_perms = Table('password_auth_permissions')
outer_auth_perms = auth_perms.as_('outer_perms')
inner_auth_perms = auth_perms.as_('inner_perms')
itgs.write_cursor.execute(
Query.from_(outer_auth_perms).delete().where(
exists(
Query.from_(inner_auth_perms).where(
inner_auth_perms.id == outer_auth_perms.id).join(
auth_methods).on(
auth_methods.id == inner_auth_perms.
password_authentication_id).join(permissions).
on(permissions.id == inner_auth_perms.permission_id).where(
auth_methods.id == Parameter('%s')).where(
permissions.name == Parameter('%s')))).returning(
outer_auth_perms.id).get_sql(),
(id, perm.lower()))
found_any = not not itgs.write_cursor.fetchall()
outer_perms = authtoken_perms.as_('outer_perms')
inner_perms = authtoken_perms.as_('inner_perms')
itgs.write_cursor.execute(
Query.from_(outer_perms).delete().where(
exists(
Query.from_(inner_perms).where(
inner_perms.id == outer_perms.id).join(authtokens).on(
authtokens.id ==
inner_perms.authtoken_id).join(permissions).on(
permissions.id == inner_perms.permission_id).
where(authtokens.source_type == Parameter('%s')).where(
authtokens.source_id == Parameter('%s')).where(
permissions.name == Parameter('%s')))).get_sql(),
('password_authentication', id, perm.lower()))
if found_any:
events = Table('password_authentication_events')
itgs.write_cursor.execute(
Query.into(events).columns(
events.password_authentication_id, events.type,
events.reason, events.user_id,
events.permission_id).from_(permissions).select(
Parameter('%s'), Parameter('%s'), Parameter('%s'),
Parameter('%s'),
permissions.id).where(permissions.name == Parameter(
'%s')).limit(1).get_sql(),
(id, 'permission-revoked',
'No reason provided; performed manually', user_id,
perm.lower()))
itgs.write_conn.commit()
if not found_any:
return Response(status_code=409,
headers={'x-request-cost': str(request_cost)})
return Response(status_code=200,
headers={'x-request-cost': str(request_cost)})
def send_messages(version):
with LazyIntegrations(logger_iden=LOGGER_IDEN) as itgs:
itgs.logger.print(Level.TRACE,
'Sending moderator onboarding messages...')
mod_onboarding_messages = Table('mod_onboarding_messages')
itgs.read_cursor.execute(
Query.from_(mod_onboarding_messages).select(
Max(mod_onboarding_messages.msg_order)).get_sql())
(max_msg_order, ) = itgs.read_cursor.fetchone()
if max_msg_order is None:
itgs.logger.print(Level.DEBUG,
'There are no moderator onboarding messages.')
return
mod_onboarding_progress = Table('mod_onboarding_progress')
moderators = Table('moderators')
users = Table('users')
itgs.read_cursor.execute(
Query.from_(moderators).join(users).on(
users.id == moderators.user_id).
left_join(mod_onboarding_progress).on(
mod_onboarding_progress.moderator_id == moderators.id).select(
users.id, moderators.id, users.username,
mod_onboarding_progress.msg_order).where(
mod_onboarding_progress.msg_order.isnull()
| (mod_onboarding_progress.msg_order < Parameter('%s'))
).get_sql(), (max_msg_order, ))
rows = itgs.read_cursor.fetchall()
responses = Table('responses')
titles = responses.as_('titles')
bodies = responses.as_('bodies')
for (user_id, mod_id, username, cur_msg_order) in rows:
itgs.read_cursor.execute(
Query.from_(mod_onboarding_messages).join(titles).on(
titles.id == mod_onboarding_messages.title_id).join(bodies)
.on(bodies.id == mod_onboarding_messages.body_id).select(
mod_onboarding_messages.msg_order, titles.id, titles.name,
bodies.id, bodies.name).where(
Parameter('%s').isnull()
| (mod_onboarding_messages.msg_order > Parameter('%s'))
).orderby(mod_onboarding_messages.msg_order,
order=Order.asc).limit(1).get_sql(), (
cur_msg_order,
cur_msg_order,
))
(new_msg_order, title_id, title_name, body_id,
body_name) = itgs.read_cursor.fetchone()
title_formatted = get_response(itgs, title_name, username=username)
body_formatted = get_response(itgs, body_name, username=username)
utils.reddit_proxy.send_request(
itgs, 'mod_onboarding_messages', version, 'compose', {
'recipient': username,
'subject': title_formatted,
'body': body_formatted
})
utils.mod_onboarding_utils.store_letter_message_with_id_and_names(
itgs, user_id, title_id, title_name, body_id, body_name)
if cur_msg_order is None:
itgs.write_cursor.execute(
Query.into(mod_onboarding_progress).columns(
mod_onboarding_progress.moderator_id,
mod_onboarding_progress.msg_order).insert(
*(Parameter('%s') for _ in range(2))).get_sql(),
(mod_id, new_msg_order))
else:
itgs.write_cursor.execute(
Query.update(mod_onboarding_progress).set(
mod_onboarding_progress.msg_order,
Parameter('%s')).set(
mod_onboarding_progress.updated_at,
CurTimestamp()).where(
mod_onboarding_progress.moderator_id ==
Parameter('%s')).get_sql(),
(new_msg_order, mod_id))
itgs.write_conn.commit()
itgs.logger.print(
Level.INFO,
'Successfully sent moderator onboarding message (msg_order={}) to /u/{}',
new_msg_order, username)
def handle_comment(self, itgs, comment, rpiden, rpversion):
token_vals = PARSER.parse(comment['body'])
borrower_username = comment['author']
lender_username = token_vals[0]
amt = token_vals[1]
usd_amount = None
if amt.currency == 'USD':
usd_amount = amt
else:
# We prefer the source is stable so we get the inverted rate and invert
usd_rate = 1 / convert(itgs, 'USD', amt.currency)
usd_amount = Money(int(amt.minor * usd_rate),
'USD',
exp=2,
symbol='$',
symbol_on_left=True)
loans = Table('loans')
users = Table('users')
lenders = users.as_('lenders')
borrowers = users.as_('borrowers')
loan_creation_infos = Table('loan_creation_infos')
moneys = Table('moneys')
principals = moneys.as_('principals')
currencies = Table('currencies')
principal_currencies = currencies.as_('principal_currencies')
principal_repayments = moneys.as_('principal_repayments')
itgs.read_cursor.execute(
Query.from_(loans).select(
loans.id, loan_creation_infos.parent_fullname,
loan_creation_infos.comment_fullname).join(loan_creation_infos)
.on(loan_creation_infos.loan_id == loans.id).join(lenders).on(
lenders.id == loans.lender_id).join(borrowers).on(
borrowers.id == loans.borrower_id).join(principals).on(
principals.id ==
loans.principal_id).join(principal_currencies).on(
principal_currencies.id == principals.currency_id).
join(principal_repayments).on(
principal_repayments.id == loans.principal_repayment_id).where(
lenders.username == Parameter('%s')).where(
borrowers.username == Parameter('%s')).where(
principal_repayments.amount == 0).where(
loans.unpaid_at.isnull()).where(
loans.deleted_at.isnull()).
where(((principal_currencies.code == amt.currency)
& (principals.amount == amt.minor)) |
((principal_currencies.code != amt.currency)
& (principals.amount <= (usd_amount.minor + 100)))).orderby(
loans.created_at, order=Order.desc).limit(1).get_sql(),
(lender_username.lower(), borrower_username.lower()))
row = itgs.read_cursor.fetchone()
if row is None:
formatted_response = get_response(
itgs,
'confirm_no_loan',
borrower_username=borrower_username,
lender_username=lender_username,
amount=amt,
usd_amount=usd_amount)
else:
(loan_id, parent_fullname, comment_fullname) = row
formatted_response = get_response(
itgs,
'confirm',
borrower_username=borrower_username,
lender_username=lender_username,
amount=amt,
usd_amount=usd_amount,
loan_permalink=(
'https://www.reddit.com' if parent_fullname is None else
'https://www.reddit.com/comments/{}/redditloans/{}'.format(
parent_fullname[3:], comment_fullname[3:])),
loan_id=loan_id)
itgs.logger.print(
Level.INFO,
'/u/{} confirmed /u/{} sent him {} (matched loan: {}). Permalink: {}',
borrower_username, lender_username, amt,
'no' if row is None else f'yes, loan {loan_id}',
'https://www.reddit.com/comments/{}/redditloans/{}'.format(
comment['link_fullname'][3:], comment['fullname'][3:]))
utils.reddit_proxy.send_request(itgs, rpiden, rpversion,
'post_comment', {
'parent': comment['fullname'],
'text': formatted_response
})
def apply_repayment(itgs: LazyItgs, loan_id: int, amount: Money):
"""Applies up to the given amount of money to the given loan. This will
convert the amount to the loans currency if necessary. This will return the
primary key of the loan_repayment_events row that was created, the amount
of money that was applied to the loan (which will be in the loan currency),
and the amount of money which exceeded the remaining principal to be repaid
on this loan (which will be in the provided currency).
This does not commit anything and expects to be running with explicit
commits, i.e., where this is running in a transaction which it does not
itself commit.
For consistency this will use the same conversion rate to USD for the loan
as when the loan was initially created.
Example:
(repayment_event_id, amount_applied, amount_remaining) = apply_repayment(
itgs, loan_id, amount
)
Raises:
ValueError: If the loan does not exist, is already repaid or, the
amount is 0.
Arguments:
itgs (LazyIntegrations): The lazy loaded networked services connector
loan_id (int): The primary key of the loan to apply the repayment to
amount (Money): The amount of money to apply toward this loan. This may
be in any currency, although it will be converted to the loans
currency.
Returns:
repayment_event_id (int): The primary key of of the loan repayment
event that this created.
amount_applied (Money): The amount of money that was applied toward
the loan, in the loans currency.
amount_remaining (Money): The amount of money that is remaining, in
the same currency that the amount was given in.
"""
tus.check(itgs=(itgs, LazyItgs),
loan_id=(loan_id, int),
amount=(amount, Money))
if amount.minor <= 0:
raise ValueError(
f'Cannot apply {amount} toward a loan (only positive amounts can be applied)'
)
loans = Table('loans')
moneys = Table('moneys')
usrs = Table('users')
principals = moneys.as_('principals')
principal_repayments = moneys.as_('principal_repayments')
currencies = Table('currencies')
principal_currencies = currencies.as_('principal_currencies')
lenders = usrs.as_('lenders')
borrowers = usrs.as_('borrowers')
itgs.write_cursor.execute(
Query.from_(loans).select(
lenders.id, lenders.username, borrowers.id, borrowers.username,
principal_currencies.id, principal_currencies.code,
principal_currencies.exponent, principal_currencies.symbol,
principal_currencies.symbol_on_left, principals.amount,
principals.amount_usd_cents, principal_repayments.id,
principal_repayments.amount, loans.unpaid_at).join(principals).
on(principals.id == loans.principal_id).join(principal_currencies).on(
principal_currencies.id == principals.currency_id).
join(principal_repayments).on(
principal_repayments.id == loans.principal_repayment_id).join(
lenders).on(lenders.id == loans.lender_id).join(borrowers).on(
borrowers.id == loans.borrower_id).where(
loans.id == Parameter('%s')).get_sql(), (loan_id, ))
row = itgs.write_cursor.fetchone()
if row is None:
raise ValueError(f'Loan {loan_id} does not exist')
(lender_user_id, lender_username, borrower_user_id, borrower_username,
loan_currency_id, loan_currency, loan_currency_exp, loan_currency_symbol,
loan_currency_symbol_on_left, principal_amount, principal_usd_cents,
principal_repayment_id, principal_repayment_amount, unpaid_at) = row
rate_loan_to_usd = (principal_amount / float(principal_usd_cents))
if principal_amount == principal_repayment_amount:
raise ValueError(f'Loan {loan_id} is already repaid')
if loan_currency == amount.currency:
loan_currency_amount = amount
else:
rate_given_to_loan = convert(itgs, amount.currency, loan_currency)
loan_currency_amount = Money(
int(math.ceil(amount.minor * rate_given_to_loan)),
loan_currency,
exp=loan_currency_exp,
symbol=loan_currency_symbol,
symbol_on_left=loan_currency_symbol_on_left)
applied = Money(min(principal_amount - principal_repayment_amount,
loan_currency_amount.minor),
loan_currency,
exp=loan_currency_exp,
symbol=loan_currency_symbol,
symbol_on_left=loan_currency_symbol_on_left)
applied_usd_cents = int(math.ceil(applied.minor / rate_loan_to_usd))
if loan_currency == amount.currency:
remaining = Money(amount.minor - applied.minor,
loan_currency,
exp=loan_currency_exp,
symbol=loan_currency_symbol,
symbol_on_left=loan_currency_symbol_on_left)
else:
applied_in_given_currency = int(
math.ceil(applied.minor / rate_given_to_loan))
remaining = Money(max(0, amount.minor - applied_in_given_currency),
amount.currency,
exp=amount.exp,
symbol=amount.symbol,
symbol_on_left=amount.symbol_on_left)
repayment_event_money_id = utils.money_utils.find_or_create_money(
itgs, applied, applied_usd_cents)
loan_repayment_events = Table('loan_repayment_events')
itgs.write_cursor.execute(
Query.into(loan_repayment_events).columns(
loan_repayment_events.loan_id,
loan_repayment_events.repayment_id).insert(
*[Parameter('%s') for _ in range(2)]).returning(
loan_repayment_events.id).get_sql(),
(loan_id, repayment_event_money_id))
(repayment_event_id, ) = itgs.write_cursor.fetchone()
new_princ_repayment_amount = principal_repayment_amount + applied.minor
new_princ_repayment_usd_cents = int(
math.ceil(new_princ_repayment_amount / rate_loan_to_usd))
new_princ_repayment_id = utils.money_utils.find_or_create_money(
itgs,
Money(new_princ_repayment_amount,
loan_currency,
exp=loan_currency_exp,
symbol=loan_currency_symbol,
symbol_on_left=loan_currency_symbol_on_left),
new_princ_repayment_usd_cents)
itgs.write_cursor.execute(
Query.update(loans).set(
loans.principal_repayment_id,
Parameter('%s')).where(loans.id == Parameter('%s')).get_sql(),
(new_princ_repayment_id, loan_id))
if new_princ_repayment_amount == principal_amount:
itgs.write_cursor.execute(
Query.update(loans).set(loans.repaid_at, Now()).set(
loans.unpaid_at,
None).where(loans.id == Parameter('%s')).get_sql(),
(loan_id, ))
if unpaid_at is not None:
loan_unpaid_events = Table('loan_unpaid_events')
itgs.write_cursor.execute(
Query.into(loan_unpaid_events).columns(
loan_unpaid_events.loan_id,
loan_unpaid_events.unpaid).insert(
*[Parameter('%s') for _ in range(2)]).get_sql(),
(loan_id, False))
itgs.channel.exchange_declare('events', 'topic')
itgs.channel.basic_publish(
'events', 'loans.paid',
json.dumps({
'loan_id': loan_id,
'lender': {
'id': lender_user_id,
'username': lender_username
},
'borrower': {
'id': borrower_user_id,
'username': borrower_username
},
'amount': {
'minor': amount.minor,
'currency': amount.currency,
'exp': amount.exp,
'symbol': amount.symbol,
'symbol_on_left': amount.symbol_on_left
},
'was_unpaid': unpaid_at is not None
}))
return (repayment_event_id, applied, remaining)
def get_summary_info(itgs: LazyIntegrations, username: str, max_loans_per_table: int = 7):
"""Get all of the information for a loan summary for the given username in
a reasonably performant way.
Example:
print(format_loan_summary(*get_summary_info(itgs, username)))
Arguments:
itgs (LazyIntegrations): The integrations to use
username (str): The username to fetch summary information for.
max_loans_per_table (int): For the sections which we prefer to expand
the loans for, this is the maximum number of loans we're willing to
include in the table. If this is too large the table can become
very difficult to read on some mobile clients, and if this is way
too large we risk hitting the 5000 character limit.
Returns:
(str, dict, dict) The username, counts, and shown as described as the
arguments to format_loan_summary
"""
loans = Table('loans')
moneys = Table('moneys')
principals = moneys.as_('principals')
users = Table('users')
lenders = users.as_('lenders')
borrowers = users.as_('borrowers')
now = datetime.utcnow()
oldest_loans_in_table = datetime(now.year - 1, now.month, now.day)
counts = {}
shown = {}
itgs.read_cursor.execute(
Query.from_(loans)
.select(Count(Star()), Sum(principals.amount_usd_cents))
.join(lenders).on(lenders.id == loans.lender_id)
.join(principals).on(principals.id == loans.principal_id)
.where(lenders.username == Parameter('%s'))
.where(loans.repaid_at.notnull())
.where(loans.deleted_at.isnull())
.get_sql(),
(username.lower(),)
)
(num_loans, princ_loans) = itgs.read_cursor.fetchone()
counts['paid_as_lender'] = {'number_of_loans': num_loans, 'principal_of_loans': princ_loans}
shown['paid_as_lender'] = []
itgs.read_cursor.execute(
Query.from_(loans)
.select(Count(Star()), Sum(principals.amount_usd_cents))
.join(borrowers).on(borrowers.id == loans.borrower_id)
.join(principals).on(principals.id == loans.principal_id)
.where(borrowers.username == Parameter('%s'))
.where(loans.repaid_at.notnull())
.where(loans.deleted_at.isnull())
.get_sql(),
(username.lower(),)
)
(num_loans, princ_loans) = itgs.read_cursor.fetchone()
counts['paid_as_borrower'] = {
'number_of_loans': num_loans,
'principal_of_loans': princ_loans
}
shown['paid_as_borrower'] = []
itgs.read_cursor.execute(
Query.from_(loans)
.select(Count(Star()), Sum(principals.amount_usd_cents))
.join(lenders).on(lenders.id == loans.lender_id)
.join(principals).on(principals.id == loans.principal_id)
.where(lenders.username == Parameter('%s'))
.where(loans.unpaid_at.notnull())
.where(loans.deleted_at.isnull())
.get_sql(),
(username.lower(),)
)
(num_loans, princ_loans) = itgs.read_cursor.fetchone()
counts['unpaid_as_lender'] = {
'number_of_loans': num_loans,
'principal_of_loans': princ_loans
}
shown['unpaid_as_lender'] = []
if num_loans > 0:
itgs.read_cursor.execute(
create_loans_query()
.where(lenders.username == Parameter('%s'))
.where(loans.unpaid_at.notnull())
.where(loans.created_at > Parameter('%s'))
.orderby(loans.created_at, order=Order.desc)
.limit(max_loans_per_table)
.get_sql(),
(username.lower(), oldest_loans_in_table)
)
row = itgs.read_cursor.fetchone()
while row is not None:
shown['unpaid_as_lender'].append(fetch_loan(row))
row = itgs.read_cursor.fetchone()
itgs.read_cursor.execute(
Query.from_(loans)
.select(Count(Star()), Sum(principals.amount_usd_cents))
.join(borrowers).on(borrowers.id == loans.borrower_id)
.join(principals).on(principals.id == loans.principal_id)
.where(borrowers.username == Parameter('%s'))
.where(loans.unpaid_at.notnull())
.where(loans.deleted_at.isnull())
.get_sql(),
(username.lower(),)
)
(num_loans, princ_loans) = itgs.read_cursor.fetchone()
counts['unpaid_as_borrower'] = {
'number_of_loans': num_loans,
'principal_of_loans': princ_loans
}
shown['unpaid_as_borrower'] = []
if num_loans > 0:
itgs.read_cursor.execute(
create_loans_query()
.where(borrowers.username == Parameter('%s'))
.where(loans.unpaid_at.notnull())
.where(loans.created_at > Parameter('%s'))
.orderby(loans.created_at, order=Order.desc)
.limit(max_loans_per_table)
.get_sql(),
(username.lower(), oldest_loans_in_table)
)
row = itgs.read_cursor.fetchone()
while row is not None:
shown['unpaid_as_borrower'].append(fetch_loan(row))
row = itgs.read_cursor.fetchone()
itgs.read_cursor.execute(
Query.from_(loans)
.select(Count(Star()), Sum(principals.amount_usd_cents))
.join(lenders).on(lenders.id == loans.lender_id)
.join(principals).on(principals.id == loans.principal_id)
.where(lenders.username == Parameter('%s'))
.where(loans.unpaid_at.isnull())
.where(loans.repaid_at.isnull())
.where(loans.deleted_at.isnull())
.get_sql(),
(username.lower(),)
)
(num_loans, princ_loans) = itgs.read_cursor.fetchone()
counts['inprogress_as_lender'] = {
'number_of_loans': num_loans,
'principal_of_loans': princ_loans
}
shown['inprogress_as_lender'] = []
if num_loans > 0:
itgs.read_cursor.execute(
create_loans_query()
.where(lenders.username == Parameter('%s'))
.where(loans.unpaid_at.isnull())
.where(loans.repaid_at.isnull())
.where(loans.created_at > Parameter('%s'))
.orderby(loans.created_at, order=Order.desc)
.limit(max_loans_per_table)
.get_sql(),
(username.lower(), oldest_loans_in_table)
)
row = itgs.read_cursor.fetchone()
while row is not None:
shown['inprogress_as_lender'].append(fetch_loan(row))
row = itgs.read_cursor.fetchone()
itgs.read_cursor.execute(
Query.from_(loans)
.select(Count(Star()), Sum(principals.amount_usd_cents))
.join(borrowers).on(borrowers.id == loans.borrower_id)
.join(principals).on(principals.id == loans.principal_id)
.where(borrowers.username == Parameter('%s'))
.where(loans.unpaid_at.isnull())
.where(loans.repaid_at.isnull())
.where(loans.deleted_at.isnull())
.get_sql(),
(username.lower(),)
)
(num_loans, princ_loans) = itgs.read_cursor.fetchone()
counts['inprogress_as_borrower'] = {
'number_of_loans': num_loans,
'principal_of_loans': princ_loans
}
shown['inprogress_as_borrower'] = []
if num_loans > 0:
itgs.read_cursor.execute(
create_loans_query()
.where(borrowers.username == Parameter('%s'))
.where(loans.unpaid_at.isnull())
.where(loans.repaid_at.isnull())
.where(loans.created_at > Parameter('%s'))
.orderby(loans.created_at, order=Order.desc)
.limit(max_loans_per_table)
.get_sql(),
(username.lower(), oldest_loans_in_table)
)
row = itgs.read_cursor.fetchone()
while row is not None:
shown['inprogress_as_borrower'].append(fetch_loan(row))
row = itgs.read_cursor.fetchone()
for cnt in counts.values():
princ = cnt['principal_of_loans']
if princ is None:
princ = 0
cnt['principal_of_loans'] = Money(
princ, 'USD',
exp=2, symbol='$', symbol_on_left=True
)
return (username, counts, shown)
def update(loan_id: int,
loan: edit_models.LoanBasicFields,
dry_run: bool = False,
dry_run_text: bool = False,
if_match: str = Header(None),
authorization: str = Header(None)):
"""Allows modifying the standard fields on a loan. Must provide an If-Match
header which is the etag of the loan being modified.
"""
if if_match is None:
return Response(status_code=428)
with LazyItgs(no_read_only=True) as itgs:
has_perm, user_id = users.helper.check_permissions_from_header(
itgs, authorization, (helper.EDIT_LOANS_PERMISSION, ))
if not has_perm:
return Response(status_code=403)
etag = helper.calculate_etag(itgs, loan_id)
if etag is None:
return Response(status_code=410)
if etag != if_match:
return Response(status_code=412)
loans = Table('loans')
moneys = Table('moneys')
principals = moneys.as_('principals')
principal_repayments = moneys.as_('principal_repayments')
currencies = Table('currencies')
itgs.read_cursor.execute(
Query.from_(loans).select(
currencies.id, currencies.code).join(principals).on(
principals.id == loans.principal_id).join(currencies).on(
currencies.id == principals.currency_id).where(
loans.id == Parameter('%s')).get_sql(),
(loan_id, ))
(currency_id, currency_code) = itgs.read_cursor.fetchone()
is_repaid = None
if (loan.principal_minor is None) != (loan.principal_repayment_minor is
None):
itgs.read_cursor.execute(
Query.from_(loans).select(
principals.amount,
principal_repayments.amount).join(principals).on(
principals.id == loans.principal_id).join(
principal_repayments).on(
principal_repayments.id ==
loans.principal_repayment_id).where(
loans.id == Parameter('%s')).get_sql(),
(loan_id, ))
princ_amt, princ_repay_amt = itgs.read_cursor.fetchone()
new_princ_amt = loan.principal_minor or princ_amt
new_princ_repay_amt = loan.principal_repayment_minor or princ_repay_amt
if new_princ_amt < new_princ_repay_amt:
return JSONResponse(
status_code=422,
content={
'detail': {
'loc': [
'loan',
(loan.principal_minor is None
and 'principal_minor'
or 'principal_repayment_minor')
],
'msg':
'Cannot have principal repayment higher than principal',
'type':
'value_error'
}
})
is_repaid = new_princ_amt == new_princ_repay_amt
elif loan.principal_minor is not None:
is_repaid = loan.principal_minor == loan.principal_repayment_minor
admin_events = Table('loan_admin_events')
query = (Query.into(admin_events).columns(
admin_events.loan_id, admin_events.admin_id, admin_events.reason,
admin_events.old_principal_id, admin_events.new_principal_id,
admin_events.old_principal_repayment_id,
admin_events.new_principal_repayment_id,
admin_events.old_created_at, admin_events.new_created_at,
admin_events.old_repaid_at, admin_events.new_repaid_at,
admin_events.old_unpaid_at, admin_events.new_unpaid_at,
admin_events.old_deleted_at, admin_events.new_deleted_at))
query = (query.from_(loans).select(
Parameter('$1'), Parameter('$2'),
Parameter('$3')).where(loans.id == Parameter('$1')))
query_params = [loan_id, user_id, loan.reason]
update_query = (Query.update(loans).where(loans.id == Parameter('$1')))
update_params = [loan_id]
# Principal
query = query.select(loans.principal_id)
if loan.principal_minor is None:
query = query.select(loans.principal_id)
else:
usd_amount = (
loan.principal_minor *
(1 / lbshared.convert.convert(itgs, 'USD', currency_code)))
itgs.write_cursor.execute(
Query.into(moneys).columns(
moneys.currency_id, moneys.amount,
moneys.amount_usd_cents).insert(
*[Parameter('%s')
for _ in range(3)]).returning(moneys.id).get_sql(),
(currency_id, loan.principal_minor, usd_amount))
(new_principal_id, ) = itgs.write_cursor.fetchone()
query = query.select(Parameter(f'${len(query_params) + 1}'))
query_params.append(new_principal_id)
update_query = update_query.set(
loans.principal_id, Parameter(f'${len(update_params) + 1}'))
update_params.append(new_principal_id)
# Principal Repayment
query = query.select(loans.principal_repayment_id)
if loan.principal_repayment_minor is None:
query = query.select(loans.principal_repayment_id)
else:
usd_amount = (
loan.principal_repayment_minor *
(1 / lbshared.convert.convert(itgs, 'USD', currency_code)))
itgs.write_cursor.execute(
Query.into(moneys).columns(
moneys.currency_id, moneys.amount,
moneys.amount_usd_cents).insert(
*[Parameter('%s')
for _ in range(3)]).returning(moneys.id).get_sql(),
(currency_id, loan.principal_repayment_minor, usd_amount))
(new_principal_repayment_id, ) = itgs.write_cursor.fetchone()
query = query.select(Parameter(f'${len(query_params) + 1}'))
query_params.append(new_principal_repayment_id)
update_query = update_query.set(
loans.principal_repayment_id,
Parameter(f'${len(update_params) + 1}'))
update_params.append(new_principal_repayment_id)
# Created At
query = query.select(loans.created_at)
if loan.created_at is None:
query = query.select(loans.created_at)
else:
new_created_at = datetime.fromtimestamp(loan.created_at)
query = query.select(Parameter(f'${len(query_params) + 1}'))
query_params.append(new_created_at)
update_query = update_query.set(
loans.created_at, Parameter(f'${len(update_params) + 1}'))
update_params.append(new_created_at)
# Repaid
query = query.select(loans.repaid_at)
if is_repaid is None:
query = query.select(loans.repaid_at)
else:
query = query.select(Parameter(f'${len(query_params) + 1}'))
update_query = update_query.set(
loans.repaid_at, Parameter(f'${len(update_params) + 1}'))
if is_repaid:
val = datetime.now()
query_params.append(val)
update_params.append(val)
else:
query_params.append(None)
update_params.append(None)
# Unpaid
query = query.select(loans.unpaid_at)
if is_repaid:
query = query.select(Parameter(f'${len(query_params) + 1}'))
update_query = update_query.set(
loans.unpaid_at, Parameter(f'${len(update_params) + 1}'))
query_params.append(None)
update_params.append(None)
elif loan.unpaid is None:
query = query.select(loans.unpaid_at)
else:
query = query.select(Parameter(f'${len(query_params) + 1}'))
update_query = update_query.set(
loans.unpaid_at, Parameter(f'${len(update_params) + 1}'))
if loan.unpaid:
val = datetime.now()
query_params.append(val)
update_params.append(val)
else:
query_params.append(None)
update_params.append(None)
# Deleted
query = query.select(loans.deleted_at)
if loan.deleted is None:
query = query.select(loans.deleted_at)
else:
query = query.select(Parameter(f'${len(query_params) + 1}'))
update_query = update_query.set(
loans.deleted_at, Parameter(f'${len(update_params) + 1}'))
if loan.deleted:
val = datetime.now()
query_params.append(val)
update_params.append(val)
else:
query_params.append(None)
update_params.append(None)
admin_event_insert_sql, admin_event_insert_params = (
lbshared.queries.convert_numbered_args(query.get_sql(),
query_params))
update_loan_sql, update_loan_params = (
lbshared.queries.convert_numbered_args(update_query.get_sql(),
update_params))
itgs.write_cursor.execute(admin_event_insert_sql,
admin_event_insert_params)
if update_loan_sql.strip():
itgs.write_cursor.execute(update_loan_sql, update_loan_params)
if not dry_run:
itgs.write_conn.commit()
itgs.logger.print(Level.INFO,
'Admin user {} just modified loan {}', user_id,
loan_id)
return Response(status_code=200)
else:
itgs.write_conn.rollback()
fmtted_admin_event_insert_sql = sqlparse.format(
admin_event_insert_sql, keyword_case='upper', reindent=True)
fmtted_update_loan_sql = sqlparse.format(update_loan_sql,
keyword_case='upper',
reindent=True)
if not dry_run_text:
return JSONResponse(
status_code=200,
content={
'loan_id':
loan_id,
'loan':
loan.dict(),
'dry_run':
dry_run,
'dry_run_text':
dry_run_text,
'admin_event_insert_sql':
fmtted_admin_event_insert_sql,
'admin_event_insert_params':
jsonable_encoder(admin_event_insert_params),
'update_loan_sql':
fmtted_update_loan_sql,
'update_loan_params':
jsonable_encoder(update_loan_params)
})
spaces = ' ' * 20
return Response(status_code=200,
headers={'Content-Type': 'plain/text'},
content=("\n".join(
(line[20:] if line[:20] == spaces else line)
for line in f"""
loan_id: {loan_id},
loan:
principal_minor: {loan.principal_minor},
principal_repayment_minor: {loan.principal_repayment_minor},
unpaid: {loan.unpaid},
created_at: {loan.created_at}
deleted: {loan.deleted}
reason: {repr(loan.reason)}
admin_event_insert_sql:
{fmtted_admin_event_insert_sql}
admin_event_insert_params: {admin_event_insert_params}
update_loan_sql:
{fmtted_update_loan_sql}
update_loan_params:
{update_loan_params}
""".splitlines())))
def index(loan_id: int = None,
after_id: int = None,
before_id: int = None,
after_time: int = None,
before_time: int = None,
borrower_name: str = None,
lender_name: str = None,
user_operator: str = 'AND',
unpaid: bool = None,
repaid: bool = None,
include_deleted: bool = False,
order: str = 'natural',
limit: int = 25,
fmt: int = 0,
dry_run: bool = False,
dry_run_text: bool = False,
authorization: str = Header(None)):
if limit <= 0:
return JSONResponse(status_code=422,
content={
'detail': {
'loc': ['limit'],
'msg': 'Must be positive',
'type': 'range_error'
}
})
if user_operator not in ('AND', 'OR'):
return JSONResponse(status_code=422,
content={
'detail': {
'loc': ['user_operator'],
'msg':
'Must be AND or OR (defaults to AND)',
'type': 'value_error'
}
})
if lender_name is None or borrower_name is None:
user_operator = 'AND'
acceptable_orders = ('natural', 'date_desc', 'date_asc', 'id_desc',
'id_asc')
if order not in acceptable_orders:
return JSONResponse(status_code=422,
content={
'detail': {
'loc': ['order'],
'msg':
f'Must be one of {acceptable_orders}',
'type': 'value_error'
}
})
now_time = time.time()
if before_time is not None and before_time > now_time * 10:
return JSONResponse(
status_code=422,
content={
'detail': {
'loc': ['before_time'],
'msg':
'Absurd value; are you using milliseconds instead of seconds?',
'type': 'range_error'
}
})
if after_time is not None and after_time > now_time * 10:
return JSONResponse(
status_code=422,
content={
'detail': {
'loc': ['after_time'],
'msg':
'Absurd value; are you using milliseconds instead of seconds?',
'type': 'range_error'
}
})
if lender_name == '':
lender_name = None
if borrower_name == '':
borrower_name = None
request_cost = limit
if order != 'natural':
# This isn't significantly more theoretically expensive since every
# sort is indexed, but it is probably less cache-local which is
# going to inflate the cost
request_cost *= 2
if loan_id is not None:
request_cost = 1
if fmt == 0:
# You're doing what we want you to do! The only real cost for us is the
# postgres computations.
request_cost = math.ceil(math.log(request_cost + 1))
elif fmt == 1:
# This is essentially increasing our cost in exchange for simplifying
# their implementation. We will punish them for doing this in
# comparison to the approach we want them to take (fmt 0 then fetch
# each loan individually), but not too severely for small requests.
# They are going from log(N) + N to 2N
request_cost = 25 + request_cost * 2
else:
return JSONResponse(status_code=422,
content={
'detail': {
'loc': ['fmt'],
'msg': 'Must be 0 or 1 (defaults to 0)',
'type': 'range_error'
}
})
with LazyItgs() as itgs:
user_id, _, perms = users.helper.get_permissions_from_header(
itgs, authorization, (helper.DELETED_LOANS_PERM,
*ratelimit_helper.RATELIMIT_PERMISSIONS))
real_req_cost = 1 if dry_run else request_cost
if not ratelimit_helper.check_ratelimit(itgs, user_id, perms,
real_req_cost):
return Response(status_code=429,
headers={'x-request-cost': str(request_cost)})
loans = Table('loans')
usrs = Table('users')
lenders = usrs.as_('lenders')
borrowers = usrs.as_('borrowers')
args = []
if fmt == 0:
query = Query.from_(loans).select(loans.id)
joins = set()
else:
query = helper.get_basic_loan_info_query()
joins = {'lenders', 'borrowers'}
if loan_id is not None:
query = query.where(loans.id == Parameter(f'${len(args) + 1}'))
args.append(loan_id)
if after_id is not None:
query = query.where(loans.id > Parameter(f'${len(args) + 1}'))
args.append(after_id)
if before_id is not None:
query = query.where(loans.id < Parameter(f'${len(args) + 1}'))
args.append(before_id)
if after_time is not None:
after_datetime = datetime.fromtimestamp(after_time)
query = query.where(
loans.created_at > Parameter(f'${len(args) + 1}'))
args.append(after_datetime)
if before_time is not None:
before_datetime = datetime.fromtimestamp(before_time)
query = query.where(
loans.created_at < Parameter(f'${len(args) + 1}'))
args.append(before_datetime)
if borrower_name is not None:
if 'borrowers' not in joins:
query = query.join(borrowers).on(
borrowers.id == loans.borrower_id)
joins.add('borrowers')
if user_operator == 'AND':
query = query.where(
borrowers.username == Parameter(f'${len(args) + 1}'))
args.append(borrower_name.lower())
if lender_name is not None:
if 'lenders' not in joins:
query = query.join(lenders).on(lenders.id == loans.lender_id)
joins.add('lenders')
if user_operator == 'AND':
query = query.where(
lenders.username == Parameter(f'${len(args) + 1}'))
args.append(lender_name.lower())
if user_operator == 'OR' and borrower_name is not None and lender_name is not None:
query = query.where(
(lenders.username == Parameter(f'${len(args) + 1}'))
| (borrowers.username == Parameter(f'${len(args) + 2}')))
args.append(lender_name.lower())
args.append(borrower_name.lower())
if unpaid is False:
query = query.where(loans.unpaid_at.isnull())
elif unpaid:
query = query.where(loans.unpaid_at.notnull())
if repaid is False:
query = query.where(loans.repaid_at.isnull())
elif repaid:
query = query.where(loans.repaid_at.notnull())
can_see_deleted = helper.DELETED_LOANS_PERM in perms
if not can_see_deleted or not include_deleted:
query = query.where(loans.deleted_at.isnull())
if order == 'date_desc':
query = query.orderby(loans.created_at, order=Order.desc)
elif order == 'date_asc':
query = query.orderby(loans.created_at)
elif order == 'id_desc':
query = query.orderby(loans.id, order=Order.desc)
elif order == 'id_asc':
query = query.orderby(loans.id)
query = query.limit(limit)
sql = query.get_sql()
sql, args = lbshared.queries.convert_numbered_args(sql, args)
if dry_run:
func_args = f'''
loan_id: {loan_id},
after_id: {after_id},
before_id: {before_id},
after_time: {after_time},
before_time: {before_time},
borrower_name: {repr(borrower_name)},
lender_name: {repr(lender_name)},
user_operator: '{user_operator}'; (accepts ('AND', 'OR')),
unpaid: {unpaid},
repaid: {repaid},
include_deleted: {include_deleted}; (ignored? ({not can_see_deleted})),
limit: {limit},
order: '{order}'; (accepts {acceptable_orders}),
fmt: {fmt},
dry_run: {dry_run},
dry_run_text: {dry_run_text},
authorization: <REDACTED>; (null? ({authorization is None}))
'''
func_args = '\n'.join(
[l.strip() for l in func_args.splitlines() if l.strip()])
formatted_sql = sqlparse.format(sql,
keyword_case='upper',
reindent=True)
if dry_run_text:
return Response(
status_code=200,
content=
(f'Your request had the following arguments:\n\n```\n{func_args}\n```\n\n '
+ 'It would have executed the following SQL:\n\n```\n' +
formatted_sql + '\n```\n\n' +
'With the following arguments:\n\n```\n' +
'\n'.join([str(s) for s in args]) + '\n```\n\n' +
f'The request would have cost {request_cost} towards your quota.'
),
headers={
'Content-Type': 'text/plain',
'x-request-cost': '1'
})
return JSONResponse(status_code=200,
content={
'func_args': func_args,
'query': sql,
'formatted_query': formatted_sql,
'query_args': args,
'request_cost': request_cost
},
headers={'x-request-cost': '1'})
itgs.read_cursor.execute(sql, args)
if fmt == 0:
result = itgs.read_cursor.fetchall()
result = [i[0] for i in result]
else:
result = []
row = itgs.read_cursor.fetchone()
while row is not None:
result.append(helper.parse_basic_loan_info(row).dict())
row = itgs.read_cursor.fetchone()
return JSONResponse(content=result,
status_code=200,
headers={'x-request-cost': str(request_cost)})