def update_table(table_name: str, requesting_user: str="NO_USER_SPECIFIED") -> Union[int, str]:
if table_name in reserved_tables:
return "Sorry, this is managed"
meta = sqlalchemy.MetaData()
dbConnection, engine = db_utils.db_connect(ret_engine=True)
user_uploads_table = sqlalchemy.Table(db_config.USER_DB_UPLOADS_TBL_NAME, meta, autoload_with=engine)
s = sqlalchemy.select(user_uploads_table).where(sqlalchemy.and_(user_uploads_table.c.username == requesting_user, user_uploads_table.c.table_name == table_name))
try:
result = dbConnection.execute(s)
if not result:
return db_return_codes.UNHANDLED_ERROR
except sqlalchemy.exc.IntegrityError as e:
db_logger.log_error(e, "Error: DB SELECT failed")
return db_return_codes.UAT_ERROR_SELECT_FAILED
if result.rowcount == 0:
return f"Error: no table {table_name} found created by {requesting_user}."
elif result.rowcount == 1: # Execute
table_name, target_url, username = result.fetchone()
df_table_replacement = pd.read_csv(target_url)
df_table_replacement.to_sql(table_name, dbConnection, if_exists='replace')
return f"Successfully updated table {table_name} from {target_url}"
else:
return f"Error: Multiple matches found under {table_name}, please contact an administrator."
def insert_user(username: str, password: str):
meta = sqlalchemy.MetaData()
dbConnection, engine = db_utils.db_connect(ret_engine=True)
user_accounts = sqlalchemy.Table(db_config.USER_ACCOUNTS_TBL_NAME, meta, autoload_with=engine)
ins = user_accounts.insert().values(username=username, password=password)
try:
result = dbConnection.execute(ins)
if not result:
return db_return_codes.UNHANDLED_ERROR
except sqlalchemy.exc.IntegrityError as e:
print(f"Attempted DB Creation of Duplicate username {username}")
db_logger.log_error(e, "Warning: Attempted DB Creation of Duplicate User Name")
return db_return_codes.UA_INSERT_FAILED_DUPLICATE
print(f"User Accounts: Creation of username {username} successful.")
db_logger.log_message(f"User Accounts: Creation of username {username} successful.")
return db_return_codes.UA_INSERT_SUCCESS
def query_user(username: str, password: str):
meta = sqlalchemy.MetaData()
dbConnection, engine = db_utils.db_connect(ret_engine=True)
user_accounts = sqlalchemy.Table(db_config.USER_ACCOUNTS_TBL_NAME, meta, autoload_with=engine)
s = sqlalchemy.select(user_accounts.c.username).where(
sqlalchemy.and_(user_accounts.c.username == username, user_accounts.c.password == password))
try:
result = dbConnection.execute(s)
if not result:
return db_return_codes.UNHANDLED_ERROR
except sqlalchemy.exc.IntegrityError as e:
db_logger.log_error(e, "Error: DB SELECT Failed")
return db_return_codes.UA_ERROR_SELECT_FAILED
if result.rowcount == 0: # If it doesn't match, it doesn't exist
# Return false
print(f"Login Failed, returning {db_return_codes.UA_LOGIN_FAILED}")
return db_return_codes.UA_LOGIN_FAILED
else:
print(f"login Success, returning {db_return_codes.UA_LOGIN_SUCCESS}")
return db_return_codes.UA_LOGIN_SUCCESS
def get_user_hash(username: str) -> tuple:
meta = sqlalchemy.MetaData()
dbConnection, engine = db_utils.db_connect(ret_engine=True)
user_accounts = sqlalchemy.Table(db_config.USER_ACCOUNTS_TBL_NAME, meta, autoload_with=engine)
s = sqlalchemy.select(user_accounts).where(user_accounts.c.username == username)
try:
result = dbConnection.execute(s)
if not result:
return db_return_codes.UNHANDLED_ERROR, 0
except sqlalchemy.exc.IntegrityError as e:
db_logger.log_error(e, "Error: DB SELECT Failed")
return db_return_codes.UA_ERROR_SELECT_FAILED, 0
if result.rowcount == 0: # If it doesn't match, it doesn't exist
# Return false
# print(f"Login Failed, returning {db_return_codes.UA_LOGIN_FAILED}")
return db_return_codes.UA_LOGIN_FAILED, 0
elif result.rowcount == 1: # Execute
username, password = result.fetchone()
else:
# print(f"login Success, returning {db_return_codes.UA_LOGIN_SUCCESS}")
return db_return_codes.UNHANDLED_ERROR
return db_return_codes.UA_QUERY_SUCCESS, password
def delete_user(username: str, password: str):
# Check if the password is valid
result = query_user(username=username, password=password)
if result == db_return_codes.UA_LOGIN_SUCCESS:
# Delete
meta = sqlalchemy.MetaData()
dbConnection, engine = db_utils.db_connect(ret_engine=True)
user_accounts = sqlalchemy.Table(db_config.USER_ACCOUNTS_TBL_NAME, meta, autoload_with=engine)
try:
dbConnection.execute(user_accounts.delete().where(user_accounts.c.username == username))
if not result:
print("Error: Unhandled DB Exception -- delete_user (No Result)")
return db_return_codes.UNHANDLED_ERROR
except Exception as e:
print("Error: Unhandled DB Exception -- delete_user")
db_logger.log_error(e, "Error: Unhandled DB Exception -- delete_user")
return db_return_codes.UNHANDLED_ERROR
db_logger.log_message(f"User Accounts: Deletion of username {username} successful")
print(f"Deletion of user {username} successful")
return db_return_codes.UA_DELETE_USER_SUCCESS
else:
print("Delete User: Login Failed, cannot delete without valid un/pw")
return db_return_codes.UA_DELETE_USER_FAILED
def update(
) -> int: # int-type error codes listed above. All negative values = errors
try:
df_covid = pd.read_csv(MAIN_COVID_DATA_URL)
df_prison = pd.read_csv(MAIN_PRISON_DATA_URL)
except Exception as err: # TODO: Refine Exception clauses
db_logger.log_error(
exception=err,
err_type="DB_CSV_Read Error",
optional_message="Check Main COVID Data/Prison Data URLs")
return RETURN_ERROR_DB_CSV_READ
# Data Processing: Covid table
try:
df_covid = df_covid[df_covid['state'] == "California"]
numeric_values = ['cases', 'deaths']
df_covid[numeric_values] = df_covid[numeric_values].astype(int)
df_covid.drop_duplicates(subset=['county', 'date'], keep='last')
# Data Processing: Prison table
kept_columns = [
'Name', 'Date', 'Address', 'County', 'Residents.Confirmed',
'Staff.Confirmed', 'Residents.Active', 'Staff.Active',
'Residents.Deaths', 'Staff.Deaths'
]
df_prison = df_prison[kept_columns]
better_names = [
'name', 'date', 'address', 'county', 'residents_confirmed',
'staff_confirmed', 'residents_active', 'staff_active',
'residents_deaths', 'staff_deaths'
]
df_prison.columns = better_names
numeric_values = [
'residents_confirmed', 'staff_confirmed', 'residents_active',
'staff_active', 'residents_deaths', 'staff_deaths'
]
df_prison[numeric_values] = df_prison[numeric_values].fillna(0)
df_prison[numeric_values] = df_prison[numeric_values].astype(int)
df_prison.dropna(subset=['address'], inplace=True)
df_prison['name'] = df_prison['name'].str.title()
df_prison['address'] = df_prison['address'].str.upper()
except Exception as e: # TODO: Refine Exception clauses
db_logger.log_error(
exception=e,
err_type="DB_Data_Processing Error",
optional_message="Column data names likely changed")
return RETURN_ERROR_DB_DATA_PROCESSING
# Connect
try:
dbConnection = db_utils.db_connect()
except Exception as e: # TODO: Refine Exception clauses
db_logger.log_error(exception=e,
err_type="DB_db_connect error",
optional_message="")
return RETURN_ERROR_DB_CONNECT
# Update
try:
df_covid.to_sql(COVID_DATA_TBL_NAME,
dbConnection,
if_exists='replace',
dtype=DTYPE_COVID_DATA)
df_prison.to_sql(PRISON_DATA_TBL_NAME,
dbConnection,
if_exists='replace',
dtype=DTYPE_PRISON_DATA)
except TypeError as e: # Table not properly made
db_logger.log_error(
exception=e,
err_type="DB_TOSQL_Table_Creation Error",
optional_message="Fixing data processing likely required")
return RETURN_ERROR_TOSQL
except ValueError as e: # Table already exists, should not happen due to if_exists='replace'
db_logger.log_error(
exception=e,
err_type="DB_TOSQL_ValueError",
optional_message=
"Table likely being replaced without if_exists='replace'")
return RETURN_ERROR_TOSQL
except Exception as e: # Unknown errors logged
db_logger.log_error(exception=e,
err_type="DB_TOSQL_Unknown Error",
optional_message="Unknown error received")
return RETURN_ERROR_TOSQL
# Close conn
dbConnection.close()
db_logger.log_message(f"Success: Successful update.")
return 0