def rebuild_test_database(test_app_client_sql_backed):
# drop all tables associated with the testing app Sqlalchemy Base
engine = create_engine(TESTING_DB_URI)
current_app.engine = engine
current_app.Base.metadata.drop_all(bind=engine)
current_app.Base.metadata.create_all(bind=engine)
test_app_data_path = os.path.join(TEST_APP_DEPLOY_DATA, DATA)
data_sources = os.listdir(test_app_data_path)
for data_source in data_sources:
if data_source == DATA_UPLOAD_METADATA or data_source.startswith("."):
continue
data_inventory = SqlDataInventory(
data_sources={MAIN_DATA_SOURCE: {
DATA_SOURCE_TYPE: data_source
}})
data_source_path = os.path.join(test_app_data_path, data_source)
files = os.listdir(data_source_path)
for file in files:
df = pd.read_csv(os.path.join(data_source_path, file),
sep=",",
comment="#")
data_inventory.write_data_upload_to_backend(
df, username="******", notes="test case upload")
return True
def test_build_combined_data_table_with_filtered_data_source(
get_sql_handler_fixture):
# only the one included penguin size is loaded, not the second
penguin_size = pd.read_csv(
"test_app_deploy_data/data/penguin_size/penguin_size.csv")
penguin_mean = pd.read_csv(
"test_app_deploy_data/data/mean_penguin_stat/mean_penguin_stat.csv")
inner_join_table = pd.merge(penguin_size,
penguin_mean,
how="inner",
on=["study_name", "sex", "species"])
num_rows_in_inner_table = inner_join_table.shape[0]
SqlDataInventory.update_data_upload_metadata_active(
PENGUIN_SIZE, {
1: "ACTIVE",
2: "INACTIVE"
})
num_rows_in_combined_table = len(
get_sql_handler_fixture.get_column_data(
[f"{PENGUIN_SIZE}:{ISLAND}"],
[{
"type": FILTER,
"column": f"{PENGUIN_SIZE}:upload_id",
"selected": [1],
}],
)[f"{PENGUIN_SIZE}:{ISLAND}"])
assert num_rows_in_inner_table == num_rows_in_combined_table
def test_get_available_data_sources(rebuild_test_database):
file_names = SqlDataInventory.get_available_data_sources()
assert "penguin_size_small" in file_names
assert "penguin_size" in file_names
assert "mean_penguin_stat" in file_names
assert "penguin_lter_small" in file_names
assert len(file_names) == 4
def sql_backend_file_to_table_upload(upload_form, csvfiles):
"""
:param upload_form: flask request form
:param csvfiles: flask request files list
:return: None. Raises a validation error if there are problems with the formatting
"""
table_name = sanitize_string(upload_form.get(DATA_SOURCE))
username = upload_form.get(USERNAME)
notes = upload_form.get(NOTES)
csv_sql_creator = CreateTablesFromCSVs(
current_app.config[SQLALCHEMY_DATABASE_URI])
data = validate_wizard_upload_submission(table_name=table_name,
csvfiles=csvfiles,
csv_sql_creator=csv_sql_creator)
(
upload_id,
upload_time,
table_name,
) = csv_sql_creator.create_and_fill_new_sql_table_from_df(
table_name, data, REPLACE)
# remove any existing metadata for this table name and write a new row
SqlDataInventory.remove_metadata_rows_for_table_name(table_name)
SqlDataInventory.write_upload_metadata_row(
upload_id=upload_id,
upload_time=upload_time,
table_name=table_name,
active=True,
username=username,
notes=notes,
)
# Generate a new models.py
# update the metadata to include all tables in the db
csv_sql_creator.meta.reflect()
# write the database schema to models.py
generator = CodeGenerator(csv_sql_creator.meta, noinflect=True)
# Write the generated model code to the specified file or standard output
models_filepath = os.path.join(APP_DEPLOY_DATA, "models.py")
# remove and write new file to trigger file watcher and refresh flask app
if os.path.exists(models_filepath):
os.remove(models_filepath)
with io_open(os.path.join(models_filepath), "w",
encoding="utf-8") as outfile:
generator.render(outfile)
def sql_data_inventory_fixture(rebuild_test_database):
data_sources = {
MAIN_DATA_SOURCE: {DATA_SOURCE_TYPE: "penguin_size"},
}
return SqlDataInventory(data_sources)