def add_cache_miss(full_title,
start_date,
end_date,
lookup_date,
html,
db_path=files['base_db']):
schema = ', '.join(
Schema.get_names(
BankSchema.get_schema_table(BankSchema.PLOT_CACHE_TB_NAME)))
# a single quote within a sqlite query is escaped with double quotes
html = html.replace("'", "''")
# note: insert into because the logic flow guarantees that value combination will be unique
sql_query = f"INSERT INTO {BankSchema.PLOT_CACHE_TB_NAME} ({schema})" \
f" VALUES ('{end_date}', '{full_title}', '{html}', '{lookup_date}', '{start_date}')"
# note that delimiter is None because the string field html itself could have ';'
SqliteHelper.execute_sqlite(sql_query, db_path, delimiter=None)
def get_most_recent_html_from_id(image_id, db_path=files['base_db']):
sql_query = f"SELECT {BankSchema.SCHEMA_PLOT_CACHE_HTML.name} FROM {BankSchema.PLOT_CACHE_TB_NAME}" \
f" WHERE {BankSchema.SCHEMA_PLOT_CACHE_FULL_TITLE.name}='{image_id}'" \
f" ORDER BY {BankSchema._SCHEMA_PLOT_CACHE_TIMESTAMP.name} DESC LIMIT 1"
df = SqliteHelper.execute_sqlite(sql_query, db_path, as_dataframe=True)
return None if df is None or df.shape[0] <= 0 else df[
BankSchema.SCHEMA_PLOT_CACHE_HTML.name][0]
def get_heatmap_df(date_range, db_path=files['base_db']):
sql_query = f"""
SELECT {BankSchema.SCHEMA_LOCATION_LON.name}, {BankSchema.SCHEMA_LOCATION_LAT.name}, {BankSchema.SCHEMA_BANK_AMOUNT.name}
FROM {BankSchema.BANK_TB_NAME} INNER JOIN {BankSchema.LOCATION_TB_NAME} ON {BankSchema.BANK_TB_NAME}.{BankSchema.SCHEMA_BANK_TRANSACTION_ID.name}
WHERE DATE({BankSchema.SCHEMA_BANK_DATE.name}) BETWEEN '{date_range.start}' AND '{date_range.end}'
"""
df = SqliteHelper.execute_sqlite(sql_query, db_path, as_dataframe=True)
return None if df is None or df.shape[0] <= 0 else df
def get_most_recent_transaction_date(tb_name, db_path):
sql_query = f"SELECT {BankSchema.SCHEMA_BANK_DATE.name} FROM {tb_name}" \
f" ORDER BY DATE({BankSchema.SCHEMA_BANK_DATE.name}) DESC LIMIT 1"
df = SqliteHelper.execute_sqlite(sql_query, db_path, as_dataframe=True)
# empty table - no most recent transaction
if df.shape[0] != 1:
return None
return df[BankSchema.SCHEMA_BANK_DATE.name][0]
def get_missing_categories(as_dataframe=True):
"""
obj: identify unlabeled data
"""
query = f"SELECT * FROM {BankSchema.BANK_TB_NAME}\n" \
f"WHERE {BankSchema.SCHEMA_BANK_C1.name} IS NULL OR {BankSchema.SCHEMA_BANK_C2.name}=''"
return SqliteHelper.execute_sqlite(query,
files['base_db'],
as_dataframe=as_dataframe)
def get_current_unique_categories(self):
"""
obj: identify all the unique categories currently recognized by the user
"""
if self.current_categories_dict:
return self.current_categories_dict
cat_fields = BankSchema.get_table_chase_y_fields()
query = f"SELECT DISTINCT {','.join(cat_fields)}\n" \
f"FROM {BankSchema.TABLE_NAME_CHASE_DEBIT}\n"
_df_dict = SqliteHelper.execute_sqlite(
query, files['base_db'], as_dataframe=True).to_dict('record')
root_d = defaultdict(set)
for d in _df_dict:
root_d[d[cat_fields[0]]].add(d[cat_fields[1]])
return root_d
def update_missing_categories(filled_list_dict, cat_ids, pk_ids):
"""
obj: build a query to update the missing categories in the database
"""
def start():
query = f"UPDATE {BankSchema.BANK_TB_NAME}\n"
query += "SET "
return query
def middle(d, column_ids):
return ', '.join(
f"{col_name}={SqliteHelper.frmt_sql_val(d[col_name])}"
for col_name in column_ids)
def end(d, column_ids):
query = '\nWHERE\n'
query += ' AND '.join(
f"{col_name}={SqliteHelper.frmt_sql_val(d[col_name])}"
for col_name in column_ids)
return query + ';\n'
all_queries = ''.join(start() + middle(d, pk_ids) + end(d, cat_ids)
for d in filled_list_dict)
SqliteHelper.execute_sqlite(all_queries, files['base_db'])
def hit(full_title,
start_date,
end_date,
lookup_date,
db_path=files['base_db']):
"""
obj: return the html if it was previously plotted today --
- keys: plot name, data range, lookup date
"""
sql_query = f"SELECT {BankSchema.SCHEMA_PLOT_CACHE_HTML.name} FROM {BankSchema.PLOT_CACHE_TB_NAME}" \
f" WHERE {BankSchema.SCHEMA_PLOT_CACHE_FULL_TITLE.name}='{full_title}' AND" \
f" {BankSchema.SCHEMA_PLOT_CACHE_START_DATE.name}='{start_date}' AND" \
f" {BankSchema.SCHEMA_PLOT_CACHE_END_DATE.name}='{end_date}' AND " \
f" {BankSchema.SCHEMA_PLOT_CACHE_LOOKUP_DATE.name}='{lookup_date}'"
sql_result = SqliteHelper.execute_sqlite(sql_query,
db_path,
as_dataframe=True)
if sql_result is not None and sql_result.shape[0] > 0:
return sql_result[BankSchema.SCHEMA_PLOT_CACHE_HTML.name].values[0]
else:
return None
def get_table_as_df(date_range,
table_name,
order_by_col_name=BankSchema.SCHEMA_BANK_DATE.name,
order='DESC',
db_path=files['base_db']):
"""
obj: a common request for the application. get a table from base db using two high
level parameters
"""
query_select = f"SELECT * FROM {table_name}\n"
query_where = f"WHERE DATE({BankSchema.SCHEMA_BANK_DATE.name}) BETWEEN " \
f"'{date_range.start}' AND '{date_range.end}'\n" if date_range else ''
query_order = f"ORDER BY {order_by_col_name} {order}" if order else ''
query = query_select + query_where + query_order
# this is useful in the scenario of not breaking down the app if there
# is no database to begin with (static initializers in /front_end/routes.py)
try:
return SqliteHelper.execute_sqlite(query,
db_path,
as_dataframe=True)
except Exception:
return None
def invalidate_cache(db_path=files['base_db']):
logging.info('Invalidating plot cache by clearing contents.')
sql_query = f"""
DELETE FROM {BankSchema.PLOT_CACHE_TB_NAME};
"""
SqliteHelper.execute_sqlite(sql_query, db_path)
def truncate(table_name):
"""
obj: drop table in database
"""
query = f"DELETE FROM {table_name};"
SqliteHelper.execute_sqlite(query, files['base_db'])
def create_base_db():
"""
obj: create standard base database
"""
SqliteHelper.execute_sqlite(files['create_db'], files['base_db'])