def review_phams(db_filter, verbose=False):
"""Finds and stores phams with discrepant function calls in a Filter.
"""
notes = db_filter.get_column("gene.Notes")
if verbose:
print("Reviewing phams...")
reviewed_phams = []
for index in range(len(db_filter.values)):
pham = db_filter.values[index]
if verbose:
print(f"...Analyzing Pham {pham}...")
query = querying.build_count(db_filter.graph,
notes.distinct(),
where=(db_filter.key == pham))
func_count = mysqldb_basic.scalar(db_filter.engine, query)
if func_count <= 1:
continue
if verbose:
print(f"......Detected discrepencies in Pham {pham}")
reviewed_phams.append(pham)
if verbose:
print(f"Detected {len(reviewed_phams)} disrepent phams...")
db_filter.values = reviewed_phams
def test_scalar_1(self):
"""Verify dictionary is returned when there are two rows of data."""
phage_data1 = test_data_utils.get_trixie_phage_data()
phage_data2 = test_data_utils.get_trixie_phage_data()
phage_data1["PhageID"] = "Trixie"
phage_data2["PhageID"] = "L5"
test_db_utils.insert_data(PHAGE, phage_data1)
test_db_utils.insert_data(PHAGE, phage_data2)
count = mysqldb_basic.scalar(self.engine, COUNT_QUERY)
self.assertEqual(count, 2)
def update_field(alchemist, update_ticket):
"""Attempts to update a field using information from an update_ticket.
:param alchemist: A connected and fully build AlchemyHandler object.
:type alchemist: AlchemyHandler
:param update_ticket: Dictionary with instructions to update a field.
:type update_ticket: dict
"""
try:
table_map = alchemist.mapper.classes[update_ticket["table"]]
except:
print(f"\nInvalid table '{update_ticket['table']}'")
return 0
table_obj = table_map.__table__
try:
field = table_obj.c[update_ticket["field"]]
except:
print(f"\nInvalid replacement field '{update_ticket['field']}' "
f"for table '{table_obj.name}'")
return 0
try:
key_field = table_obj.c[update_ticket["key_name"]]
except:
print(f"\nInvalid selection key '{update_ticket['key_name']}' "
f"for table '{table_obj.name}'")
return 0
primary_keys = list(table_obj.primary_key.columns)
if key_field not in primary_keys:
print(f"\nInvalid selection key '{update_ticket['key_name']}' "
f"for table '{table_obj.name}'")
return 0
key_value_clause = (key_field == update_ticket["key_value"])
key_value_query = querying.build_count(alchemist.graph,
key_field,
where=key_value_clause)
key_value_count = mysqldb_basic.scalar(alchemist.engine, key_value_query)
if key_value_count != 1:
print(f"\nInvalid selection value '{update_ticket['key_value']}' "
f"for key '{key_field.name}' in table '{table_obj.name}'")
return 0
if update_ticket["value"] == "NULL":
update_ticket["value"] = None
statement = update(table_obj).where(key_value_clause).values(
{field.name: update_ticket["value"]})
alchemist.engine.execute(statement)
return 1
def main(unparsed_args_list):
"""Run main freeze database pipeline."""
args = parse_args(unparsed_args_list)
ref_database = args.database
reset = args.reset
new_database = args.new_database_name
prefix = args.prefix
# Filters input: phage.Status=draft AND phage.HostGenus=Mycobacterium
# Args structure: [['phage.Status=draft'], ['phage.HostGenus=Mycobacterium']]
filters = args.filters
# Create config object with data obtained from file and/or defaults.
config = configfile.build_complete_config(args.config_file)
mysql_creds = config["mysql"]
# Verify database connection and schema compatibility.
print("Connecting to the MySQL database...")
alchemist1 = AlchemyHandler(database=ref_database,
username=mysql_creds["user"],
password=mysql_creds["password"])
alchemist1.connect(pipeline=True)
engine1 = alchemist1.engine
mysqldb.check_schema_compatibility(engine1, "the freeze pipeline")
# Get SQLAlchemy metadata Table object
# table_obj.primary_key.columns is a
# SQLAlchemy ColumnCollection iterable object
# Set primary key = 'phage.PhageID'
alchemist1.build_metadata()
table = querying.get_table(alchemist1.metadata, TARGET_TABLE)
for column in table.primary_key.columns:
primary_key = column
# Create filter object and then add command line filter strings
db_filter = Filter(alchemist=alchemist1, key=primary_key)
db_filter.values = []
# Attempt to add filters and exit if needed.
add_filters(db_filter, filters)
# Performs the query
db_filter.update()
# db_filter.values now contains list of PhageIDs that pass the filters.
# Get the number of genomes that will be retained and build the
# MYSQL DELETE statement.
keep_set = set(db_filter.values)
delete_stmt = construct_delete_stmt(TARGET_TABLE, primary_key, keep_set)
count_query = construct_count_query(TARGET_TABLE, primary_key, keep_set)
phage_count = mysqldb_basic.scalar(alchemist1.engine, count_query)
# Determine the name of the new database.
if new_database is None:
if prefix is None:
prefix = get_prefix()
new_database = f"{prefix}_{phage_count}"
# Create the new database, but prevent overwriting of current database.
if engine1.url.database != new_database:
result = mysqldb_basic.drop_create_db(engine1, new_database)
else:
print(
"Error: names of the reference and frozen databases are the same.")
print("No database will be created.")
result = 1
# Copy database.
if result == 0:
print(f"Reference database: {ref_database}")
print(f"New database: {new_database}")
result = mysqldb_basic.copy_db(engine1, new_database)
if result == 0:
print(f"Deleting genomes...")
alchemist2 = AlchemyHandler(database=new_database,
username=engine1.url.username,
password=engine1.url.password)
alchemist2.connect(pipeline=True)
engine2 = alchemist2.engine
engine2.execute(delete_stmt)
if reset:
engine2.execute(RESET_VERSION)
# Close up all connections in the connection pool.
engine2.dispose()
else:
print("Unable to copy the database.")
# Close up all connections in the connection pool.
engine1.dispose()
else:
print(f"Error creating new database: {new_database}.")
print("Freeze database script completed.")
