def build_where_clause(db_graph, filter_expression):
"""Creates a SQLAlchemy BinaryExpression object from a MySQL WHERE
clause expression.
:param db_graph: SQLAlchemy structured NetworkX Graph object.
:type db_graph: Graph
:param filter_expression: MySQL where clause expression.
:type filter_expression: str
:returns: MySQL expression-related SQLAlchemy BinaryExpression object.
:rtype: BinaryExpression
"""
filter_params = parsing.parse_filter(filter_expression)
# Retrieve Table and Column objects from split filter expression strings
table_object = db_graph.nodes[filter_params[0]]["table"]
column_name = parsing.translate_column(
db_graph.graph["metadata"],
f"{table_object.name}.{filter_params[1]}")
column_object = table_object.columns[column_name]
# Checks the operator and Column type compatability
parsing.check_operator(filter_params[2], column_object)
right = filter_params[3] # Stores the expressions 'right' value
if column_object.type.python_type == bytes:
if isinstance(right, list):
for i in range(len(right)):
right[i] = right[i].encode("utf-8")
else:
right = right.encode("utf-8")
if right == "None":
right = None
where_clause = None
if filter_params[2] == "=":
where_clause = (column_object == right)
elif filter_params[2] == "LIKE":
where_clause = (column_object.like(right))
elif filter_params[2] == "!=" or filter_params[2] == "IS NOT":
where_clause = (column_object != right)
elif filter_params[2] == ">":
where_clause = (column_object > right)
elif filter_params[2] == ">=":
where_clause = (column_object >= right)
elif filter_params[2] == "<":
where_clause = (column_object < right)
elif filter_params[2] == "<=":
where_clause = (column_object <= right)
elif filter_params[2] == "IN":
where_clause = (column_object.in_(right))
elif filter_params[2] == "NOT IN":
where_clause = (column_object.notin_(right))
return where_clause
def get_column(metadata, column):
parsed_column = parsing.parse_column(column)
table_obj = get_table(metadata, parsed_column[0])
column = parsing.translate_column(metadata, column)
columns_dict = dict(table_obj.columns)
column = columns_dict[parsed_column[1]]
return column
def get_column(metadata, column):
"""Get a SQLAlchemy Column object, with a case-insensitive input.
Input must be formatted {Table_name}.{Column_name}.
:param metadata: Reflected SQLAlchemy MetaData object.
:type metadata: MetaData
:param table: Case-insensitive column name.
:type table: str
:returns: Corresponding SQLAlchemy Column object.
:rtype: Column
"""
parsed_column = parsing.parse_column(column)
table_obj = get_table(metadata, parsed_column[0]) # Retrieves SQL Table.
# Ensures column name is accurate before indexing Table.
column = parsing.translate_column(metadata, column)
columns_dict = dict(table_obj.columns) # Converts to indexable object.
column_obj = columns_dict[column]
return column_obj
def test_translate_table_2(self):
with self.assertRaises(ValueError):
parsing.translate_column(self.metadata, "phage.Cluster")
def test_translate_table_1(self):
for column in self.column_inputs:
with self.subTest(column_input=column):
translated = parsing.translate_column(self.metadata, column)
def test_translate_column_2(self):
"""Verify translate_column() raises ValueError from invalid column name.
"""
with self.assertRaises(ValueError):
parsing.translate_column(self.metadata, "phage.NotCluster")
def test_translate_column_1(self):
"""Verify translate_column() returns case-sensitive column names.
"""
for column in self.column_inputs:
with self.subTest(column_input=column):
translated = parsing.translate_column(self.metadata, column)
def test_translate_column_3(self):
"""Verify translate_column() returns case-sensitive column names.
"""
column = parsing.translate_column(self.metadata, "tRNA.geneid")
self.assertEqual(column, "GeneID")
def test_translate_column_1(self):
"""Verify translate_column() conserves case-sensitive table names.
"""
column = parsing.translate_column(self.metadata, "phage.PhageID")
self.assertEqual(column, "PhageID")