def transpose(self, column, return_dict=False):
self.check()
if not self.values:
return []
name = ""
if isinstance(column, str):
column = q.get_column(self.graph.graph["metadata"], column)
name = column.name
elif isinstance(column, Column):
name = column.name
else:
raise TypeError
where_clause = (self.key.in_(self.values))
query = q.build_distinct(self.graph, [column], where=[where_clause])
proxy = self.engine.execute(query)
results = proxy.fetchall()
values = []
for result in results:
values.append(result[0])
if return_dict:
values = {name: values}
return values
def test_build_distinct_1(self, BuildSelect):
ExecutableMock = Mock()
DistinctMock = Mock()
type(ExecutableMock).distinct = DistinctMock
BuildSelect.return_value = ExecutableMock
querying.build_distinct(self.graph,
self.columns,
where=self.whereclauses,
order_by=self.columns)
BuildSelect.assert_called_with(self.graph,
self.columns,
where=self.whereclauses,
order_by=self.columns)
def test_build_distinct_2(self):
"""Verify build_distinct() cannot variable aggregated columns.
MySQL does not accept DISTINCT queries with aggregated
and non-aggregated columns.
"""
distinct_query = querying.build_distinct(
self.graph, [self.PhageID, func.count(self.Cluster)])
with self.assertRaises(InternalError):
dict_list = query_dict_list(self.engine, distinct_query)
def test_build_distinct_1(self, build_select_mock):
"""Verify function structure of build_distinct()
"""
executable_mock = Mock()
DistinctMock = Mock()
type(executable_mock).distinct = DistinctMock
build_select_mock.return_value = executable_mock
querying.build_distinct(self.graph,
self.columns,
where=self.whereclauses,
order_by=self.columns)
build_select_mock.assert_called_with(self.graph,
self.columns,
where=self.whereclauses,
order_by=self.columns,
add_in=None)
def build_values(self,
where=None,
column=None,
raw_bytes=False,
limit=8000):
"""Queries for values from stored WHERE clauses and Filter key.
:param where: MySQL WHERE clause_related SQLAlchemy object(s).
:type where: BinaryExpression
:type where: list
:param order_by: MySQL ORDER BY clause-related SQLAlchemy object(s).
:type order_by: Column
:type order_by: list
:param column: SQLAlchemy Column object or object name.
:type column: Column
:type column: str
:param limit: SQLAlchemy IN clause query length limiter.
:type limit: int
:returns: Distinct values fetched from given and innate constraints.
:rtype: list
"""
self.check()
if column is None:
column_obj = self._key
else:
column_obj = self.get_column(column)
if not where is None:
if isinstance(where, list) or isinstance(where, BooleanClauseList):
base_clauses = where
else:
base_clauses = [where]
else:
base_clauses = []
query = q.build_distinct(self.graph,
column_obj,
where=base_clauses,
add_in=self._key)
values = q.first_column(self.engine,
query,
in_column=self._key,
values=self._values,
limit=limit)
if not raw_bytes:
if column_obj.type.python_type is bytes:
values = parsing.convert_to_decoded(values)
return values
def test_build_distinct_1(self):
"""Verify build_distinct() handles many-to-one relations as expected.
build_distinct() should not duplicate 'one' when handling 'many.'
"""
where_clause = (self.Subcluster == "A2")
distinct_query = querying.build_distinct(self.graph,
self.Cluster,
where=where_clause)
dict_list = query_dict_list(self.engine, distinct_query)
self.assertEqual(len(dict_list), 1)
distinct_dict = dict_list[0]
self.assertEqual(distinct_dict["Cluster"], "A")
def retrieve(self, raw_columns, raw_bytes=False, filter=False):
"""Queries for distinct data for each value in the Filter object.
:param columns: SQLAlchemy Column object(s)
:type columns: Column
:type columns: str
:type columns: list[Column]
:type columns: list[str]
:returns: Distinct values for each Filter value.
:rtype: dict{dict}
"""
self.check()
if not self._values:
return {}
columns = self.get_columns(raw_columns)
where_clauses = []
if filter:
where_clauses = self.build_where_clauses()
values = {}
for value in self._values:
compare_value = value
if self._key.type.python_type == bytes and value is not None:
compare_value = value.encode("utf-8")
value_clauses = where_clauses + [(self._key == compare_value)]
values.update({value: {}})
# For each column for each value, add the respective data to a dict
for i in range(len(columns)):
query = q.build_distinct(self._graph,
columns[i],
where=value_clauses)
value_data = q.first_column(self._engine, query)
if not raw_bytes:
if columns[i].type.python_type == bytes:
value_data = basic.convert_to_decoded(value_data)
values[value].update({columns[i].name: value_data})
return values