def test_type_hints(self): table = sql_table.SqlTable(self.conn, self.iris, inspect_values=True) self.assertEqual(len(table.domain), 5) self.assertEqual(len(table.domain.metas), 0) table = sql_table.SqlTable(self.conn, self.iris, inspect_values=True, type_hints=Domain([], [], metas=[ StringVariable("iris")])) self.assertEqual(len(table.domain), 4) self.assertEqual(len(table.domain.metas), 1)
def test_metas_type_hints(self): iris = sql_table.SqlTable(self.conn, self.iris, type_hints=Domain([], [], metas=[ DiscreteVariable("iris", values=['Iris-setosa', 'Iris-virginica', 'Iris-versicolor'])])) self.assertEqual(len(iris.domain.metas), 1) self.assertEqual(iris.domain.metas[0].name, 'iris')
def test_select_all(self): iris = sql_table.SqlTable( self.conn, "SELECT * FROM iris", type_hints=Domain( [], DiscreteVariable("iris", values=['Iris-setosa', 'Iris-virginica', 'Iris-versicolor'])) ) self.assertEqual(len(iris.domain), 5)
def test_query_subset_of_attributes(self): table = sql_table.SqlTable(self.conn, self.iris) attributes = [ self._mock_attribute("sepal length"), self._mock_attribute("sepal width"), self._mock_attribute("double width", '2 * "sepal width"') ] results = list(table._query(attributes)) self.assertSequenceEqual(results[:5], [(5.1, 3.5, 7.0), (4.9, 3.0, 6.0), (4.7, 3.2, 6.4), (4.6, 3.1, 6.2), (5.0, 3.6, 7.2)])
def test_joins(self): table = sql_table.SqlTable( self.conn, """SELECT a."sepal length", b. "petal length", CASE WHEN b."petal length" < 3 THEN '<' ELSE '>' END AS "qualitative petal length" FROM iris a INNER JOIN iris b ON a."sepal width" = b."sepal width" WHERE a."petal width" < 1 ORDER BY a."sepal length", b. "petal length" ASC""", type_hints=Domain([DiscreteVariable( name="qualitative petal length", values=['<', '>'])], [])) self.assertEqual(len(table), 498) self.assertAlmostEqual(list(table[497]), [5.8, 1.2, 0.])
def test_query_subset_of_rows(self): table = sql_table.SqlTable(self.conn, self.iris) all_results = list(table._query()) results = list(table._query(rows=range(10))) self.assertEqual(len(results), 10) self.assertSequenceEqual(results, all_results[:10]) results = list(table._query(rows=range(10))) self.assertEqual(len(results), 10) self.assertSequenceEqual(results, all_results[:10]) results = list(table._query(rows=slice(None, 10))) self.assertEqual(len(results), 10) self.assertSequenceEqual(results, all_results[:10]) results = list(table._query(rows=slice(10, None))) self.assertEqual(len(results), 140) self.assertSequenceEqual(results, all_results[10:])
def test_universal_table(self): uri, table_name = self.construct_universal_table() table = sql_table.SqlTable(self.conn, """ SELECT v1.col2 as v1, v2.col2 as v2, v3.col2 as v3, v4.col2 as v4, v5.col2 as v5 FROM %(table_name)s v1 INNER JOIN %(table_name)s v2 ON v2.col0 = v1.col0 AND v2.col1 = 2 INNER JOIN %(table_name)s v3 ON v3.col0 = v2.col0 AND v3.col1 = 3 INNER JOIN %(table_name)s v4 ON v4.col0 = v1.col0 AND v4.col1 = 4 INNER JOIN %(table_name)s v5 ON v5.col0 = v1.col0 AND v5.col1 = 5 WHERE v1.col1 = 1 ORDER BY v1.col0 """ % dict(table_name='"%s"' % table_name)) self.drop_sql_table(table_name)
def test_query_all(self): table = sql_table.SqlTable(self.conn, self.iris, inspect_values=True) results = list(table) self.assertEqual(len(results), 150)
def test_unavailable_row(self): table = sql_table.SqlTable(self.conn, self.iris) with self.assertRaises(IndexError): table[151]
def sql_table_from_data(self, data): table_name = self._create_sql_table(data) yield sql_table.SqlTable(get_dburi() + '/' + str(table_name)) self.drop_sql_table(table_name)