def test_get_db_env_vars(self):
prefix = 'FINANCE_SCRAPING_'
os.environ[f'{prefix}DB_NAME'] = 'scraping_db'
os.environ[f'{prefix}DB_TABLE'] = 'mytable'
os.environ[f'{prefix}DB_USERNAME'] = '******'
os.environ[f'{prefix}DB_PASSWORD'] = '******'
os.environ[f'{prefix}HOST'] = 'localhost'
os.environ[f'{prefix}PORT'] = '1234'
configuration = config.get_db_env_vars()
self.assertEqual(self.config_dict["DATABASE"], configuration)
def get_all_data():
db_params = config.get_db_env_vars()
con = loading.get_connection(db_params)
try:
data = pd.read_sql(
get_all_data_sql.format(db_params["table"]),
con)
finally:
con.close()
data.set_index(pd.to_datetime(data["collection_date"]), inplace=True)
return data
def top_ten_values(attribute):
con = loading.get_connection(config.get_db_env_vars())
df = pd.read_sql(top_val_sql.format(attribute=attribute), con)
con.close()
if attribute == "yield_percent":
df["yield_percent"] = df["yield_percent"] * 100
if max(df[attribute]) > 1e6:
df[attribute] = (df[attribute] / 1e6).round(5).apply("{:,}".format)
df.rename(columns={attribute: f"{attribute} (M)"}, inplace=True)
else:
df[attribute] = df[attribute].round(5)
output_table = html.Table(
[html.Tr([html.Th(capitalize(col)) for col in df.columns])] + [
html.Tr([html.Td(df.iloc[i][col]) for col in df.columns])
for i in range(10)
])
return output_table
def bottom_ten_prog(attribute):
con = loading.get_connection(config.get_db_env_vars())
df = pd.read_sql(bottom_prog_sql.format(attribute=attribute), con)
con.close()
if attribute == "yield_percent":
df["diff"] = df["diff"] * 100
if max(df["diff"]) > 1e6:
df["diff"] = (df["diff"] / 1e6).round(5).apply("{:,}".format)
df.rename(columns={"diff": f"diff (M)"}, inplace=True)
elif max(df["diff"]) > 1e3:
df["diff"] = (df["diff"] / 1e3).round(5).apply("{:,}".format)
df.rename(columns={"diff": f"diff (K)"}, inplace=True)
else:
df["diff"] = df["diff"].round(5)
output_table = html.Table(
[html.Tr([html.Th(capitalize(col)) for col in df.columns])] + [
html.Tr([html.Td(df.iloc[i][col]) for col in df.columns])
for i in range(10)
])
return output_table
def get_min_date():
db_params = config.get_db_env_vars()
rows = query_db(get_min_date_sql.format(db_params["table"]), db_params)
return rows[0][0]
def get_companies():
db_params = config.get_db_env_vars()
rows = query_db(get_companies_sql.format(db_params["table"]), db_params)
companies = [row[0] for row in rows]
return [{"label": c, "value": c} for c in companies]