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)
