def db_main_url_tests():
test_1 = requests.get(db_config.MAIN_COVID_DATA_URL)
if test_1.status_code != 200:
db_logger.log_message("Test 1 Failed: Could not reach Main COVID County Data URL")
test_2 = requests.get(db_config.MAIN_PRISON_DATA_URL)
if test_2.status_code != 200:
db_logger.log_message("Test 2 Failed: Could not reach Main COVID Prison Data URL")
def db_main_url_tests() -> list:
failed_tests = []
test_1 = requests.get(db_config.MAIN_COVID_DATA_URL)
if test_1.status_code != 200:
db_logger.log_message("Test 1 Failed: Could not reach Main COVID County Data URL")
failed_tests.append("DB_MAIN_URL TEST: County URL/Connectivity Test FAILED")
test_2 = requests.get(db_config.MAIN_PRISON_DATA_URL)
if test_2.status_code != 200:
db_logger.log_message("Test 2 Failed: Could not reach Main COVID Prison Data URL")
failed_tests.append("DB_MAIN_URL TEST: Prison URL/Connectivity Test FAILED")
return failed_tests
def remove_user_table(rm_table_name: str, requesting_user: str= "NO_USER_SPECIFIED") -> str:
dbConnection = db_utils.db_connect()
tn = json.dumps(rm_table_name)
un = json.dumps(requesting_user)
result = pd.read_sql(f"SELECT table_name FROM {db_config.USER_DB_UPLOADS_TBL_NAME} where table_name={tn} and username={un};", dbConnection)
if not result.empty:
delete_this_table = result['table_name'].tolist()[0]
dbConnection.execute(f"DROP TABLE IF EXISTS {delete_this_table}")
dbConnection.execute(f"DELETE FROM {db_config.USER_DB_UPLOADS_TBL_NAME} WHERE table_name={tn}")
db_logger.log_message(f"Success: Deleted Table {delete_this_table}, requesting_user={requesting_user}")
return f"Success: Deleted Table {delete_this_table}"
else:
db_logger.log_message(f"Invalid Delete of table {rm_table_name}, requesting_user={requesting_user}")
return f"Invalid delete of table {rm_table_name}"
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 create_new_table(csv_url: str, new_table_name: str, requesting_user: str="NO_USER_SPECIFIED") -> str:
# Perhaps check if the url is bad here:
if new_table_name in reserved_tables:
print(f"Sorry, {new_table_name} is reserved")
return f"Sorry, {new_table_name} is reserved"
# Connect
dbConnection = db_utils.db_connect()
# Check if table is in used_tables
result = pd.read_sql(f"SELECT table_name FROM {db_config.USER_DB_UPLOADS_TBL_NAME};", dbConnection)
list_of_used_tables = result['table_name'].tolist()
if new_table_name in list_of_used_tables:
# Fail
print(f"Error, the table{new_table_name} is currently in use. Please select a different table name.")
return f"Error, the table{new_table_name} is currently in use. Please select a different table name."
else:
# Successful login, return token perhaps? (SESS_ID token?)
pass
# Continue
try:
df_new_table = pd.read_csv(csv_url)
except Exception as e: # TODO: Actually split the errors, currently it just fails
return f"Error, URL could not be read"
# Make new table
df_new_table.to_sql(new_table_name, dbConnection, if_exists='replace')
# Update USER_DB_UPLOADS_TBL
tn = json.dumps(new_table_name)
du = json.dumps(csv_url)
un = json.dumps(requesting_user)
try:
result = dbConnection.execute(f"INSERT INTO {db_config.USER_DB_UPLOADS_TBL_NAME}(table_name, data_url, username) "
f"VALUES ({tn}, {du}, {un});")
except Exception as e: # On fail, rollback
result = dbConnection.execute(f"DROP TABLE IF EXISTS {new_table_name}")
dbConnection.close()
db_logger.log_message(f"UAT Success: Created New Table {new_table_name} by {requesting_user}")
return f"Success, Table Created: {new_table_name}"
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
test_2 = f"UAT Test 2: Valid Update Table"
res2 = db_update_more_databases.update_table(table_name=test_tbl_name,
requesting_user=test_username)
if res2 != f"Successfully updated table {test_tbl_name} from {test_data_csv_url}":
failed_tests.append(test_2)
test_3 = f"UAT Test 3: Valid Remove User Table"
res3 = db_update_more_databases.remove_user_table(rm_table_name=test_tbl_name,
requesting_user=test_username)
if res3 != f"Success: Deleted Table {test_tbl_name}":
failed_tests.append(test_3)
test_0_2 = f"UAT Test 0-2: Remove Make User: WARNING: IF THIS FAILS AND TEST 0 SUCCEEDS, JUNK USER {test_username} MADE"
res4 = database_queries_users.delete_user(username=test_username,
password=test_password)
if res4 != db_return_codes.UA_DELETE_USER_SUCCESS:
failed_tests.append(test_0_2)
return failed_tests
# If ran, do test suite
if __name__ == "__main__":
failed_test_list = []
failed_test_list.extend(perform_user_tests())
failed_test_list.extend(db_main_url_tests())
failed_test_list.extend(db_main_query_tests())
failed_test_list.extend(db_UAT_tests())
print(f"Failing tests: " + str(failed_test_list))
if len(failed_test_list) > 0:
db_logger.log_message(f"Failing tests: {str(failed_test_list)}", output_location=db_logger.LOG_LOCATION_TESTS)
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")
print("Unknown error")
return RETURN_ERROR_TOSQL
# Close conn
dbConnection.close()
db_logger.log_message(f"Success: Successful update.")
return 0
#######################
# If ran, do update then tests
if __name__ == '__main__':
# Read data from site
update()
# Run test suite
db_logger.log_message("Starting tests")
# db_tests.db_tests()
db_logger.log_message("Tests completed")
print("Tests Completed!")