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")