def test_sql_select_invalid(self):
db = SQLDatabase(':memory:')
db.add_table('test')
db.add_column('test', 'a', data=np.arange(10, 20))
db.add_column('test', 'b', data=np.arange(20, 30))
with pytest.raises(sqlite3.OperationalError,
match='no such column: c'):
db.select('test', columns=['c'])
with pytest.raises(ValueError,
match='offset cannot be used without limit.'):
db.select('test', columns='a', offset=1)
with pytest.raises(TypeError, match='where must be'):
db.select('test', columns='a', where=1)
with pytest.raises(TypeError, match='if where is a list'):
db.select('test', columns='a', where=[1, 2, 3])
with pytest.raises(sqlite3.IntegrityError):
db.select('test', limit=3.14)
with pytest.raises(sqlite3.OperationalError):
db.select('test', order=5)
def test_sql_len(self):
db = SQLDatabase(':memory:')
db.add_table('test')
db.add_table('test2')
db.add_table('test3')
assert_equal(len(db), 3)
def test_sql_select_order(self):
db = SQLDatabase(':memory:')
db.add_table('test')
db.add_column('test', 'a', data=np.arange(10, 20))
db.add_column('test', 'b', data=np.arange(20, 30)[::-1])
a = db.select('test', order='b')
assert_equal(
a,
list(zip(np.arange(10, 20),
np.arange(20, 30)[::-1]))[::-1])
a = db.select('test', order='b', limit=2)
assert_equal(a, [(19, 20), (18, 21)])
a = db.select('test', order='b', limit=2, offset=2)
assert_equal(a, [(17, 22), (16, 23)])
a = db.select('test', order='b', where='a < 15')
assert_equal(a, [(14, 25), (13, 26), (12, 27), (11, 28), (10, 29)])
a = db.select('test', order='b', where='a < 15', limit=3)
assert_equal(a, [(14, 25), (13, 26), (12, 27)])
a = db.select('test', order='b', where='a < 15', limit=3, offset=2)
assert_equal(a, [(12, 27), (11, 28), (10, 29)])
def test_sql_add_row_invalid(self):
db = SQLDatabase(':memory:')
db.add_table('test')
db.add_column('test', 'a')
db.add_column('test', 'b')
with assert_raises(TypeError):
db.add_row('test', [1, 2, 3])
def test_sql_getitem(self):
db = SQLDatabase(':memory:')
db.add_table('test')
db.add_column('test', 'a', data=np.arange(10, 20))
db.add_column('test', 'b', data=np.arange(20, 30))
assert_equal(db['test']['a'].values, np.arange(10, 20))
assert_equal(db['test']['b'].values, np.arange(20, 30))
assert_is_instance(db['test']['a'], SQLColumn)
assert_is_instance(db['test']['b'], SQLColumn)
assert_equal(db['test'][4].values, (14, 24))
assert_is_instance(db['test'][4], SQLRow)
assert_equal(db['test'][-1].values, (19, 29))
assert_is_instance(db['test'][-1], SQLRow)
with pytest.raises(KeyError):
db['test']['c']
with pytest.raises(KeyError):
db['not_a_table']['a']
with pytest.raises(IndexError):
db['test'][11]
with pytest.raises(IndexError):
db['test'][-11]
def test_sql_db_creation(self):
db = SQLDatabase(':memory:')
assert_equal(db.table_names, [])
assert_equal(len(db), 0)
db.add_table('test')
assert_equal(db.table_names, ['test'])
assert_equal(len(db), 1)
def test_table_as_table_empty(self):
db = SQLDatabase(':memory:')
db.add_table('test')
table = db['test']
a = table.as_table()
assert_is_instance(a, Table)
assert_equal(a.colnames, [])
assert_equal(a, Table())
def test_sql_add_table_columns(self):
db = SQLDatabase(':memory:')
db.add_table('test', columns=['a', 'b'])
assert_equal(db.get_column('test', 'a').values, [])
assert_equal(db.get_column('test', 'b').values, [])
assert_equal(db.column_names('test'), ['a', 'b'])
assert_equal(len(db), 1)
assert_equal(db.table_names, ['test'])
def test_sql_add_table_from_data_dict(self):
d = {'a': np.arange(10, 20), 'b': np.arange(20, 30)}
db = SQLDatabase(':memory:')
db.add_table('test', data=d)
assert_equal(db.get_column('test', 'a').values, np.arange(10, 20))
assert_equal(db.get_column('test', 'b').values, np.arange(20, 30))
assert_equal(db.column_names('test'), ['a', 'b'])
assert_equal(len(db), 1)
assert_equal(db.table_names, ['test'])
def test_sql_add_table_from_data_ndarray(self):
dtype = [('a', 'i4'), ('b', 'f8')]
data = np.array([(1, 2.0), (3, 4.0), (5, 6.0), (7, 8.0)], dtype=dtype)
db = SQLDatabase(':memory:')
db.add_table('test', data=data)
assert_equal(db.get_column('test', 'a').values, [1, 3, 5, 7])
assert_equal(db.get_column('test', 'b').values, [2.0, 4.0, 6.0, 8.0])
assert_equal(db.column_names('test'), ['a', 'b'])
assert_equal(len(db), 1)
assert_equal(db.table_names, ['test'])
def test_sql_select_limit_offset(self):
db = SQLDatabase(':memory:')
db.add_table('test')
db.add_column('test', 'a', data=np.arange(10, 20))
db.add_column('test', 'b', data=np.arange(20, 30))
a = db.select('test', columns='a', limit=1)
assert_equal(a, 10)
a = db.select('test', columns='a', limit=3, offset=2)
assert_equal(a, [[12], [13], [14]])
def test_sql_add_column_name_and_data(self):
db = SQLDatabase(':memory:')
db.add_table('test')
db.add_column('test', 'a', data=np.arange(10, 20))
db.add_column('test', 'b', data=np.arange(20, 30))
assert_equal(db.get_column('test', 'a').values, np.arange(10, 20))
assert_equal(db.get_column('test', 'b').values, np.arange(20, 30))
assert_equal(db.column_names('test'), ['a', 'b'])
assert_equal(len(db), 1)
assert_equal(db.table_names, ['test'])
def test_sql_count(self):
db = SQLDatabase(':memory:')
db.add_table('test')
db.add_column('test', 'a', data=np.arange(10, 20))
db.add_column('test', 'b', data=np.arange(20, 30))
assert_equal(db.count('test'), 10)
assert_equal(db.count('test', where={'a': 15}), 1)
assert_equal(db.count('test', where={'a': 15, 'b': 22}), 0)
assert_equal(db.count('test', where='a > 15'), 4)
assert_equal(db.count('test', where=['a > 15', 'b < 27']), 1)
def test_sql_add_row(self):
db = SQLDatabase(':memory:')
db.add_table('test')
db.add_column('test', 'a')
db.add_column('test', 'b')
db.add_row('test', dict(a=1, b=2))
db.add_row('test', dict(a=3, b=4))
db.add_row('test', dict(a=5, b=6))
assert_equal(db.get_column('test', 'a').values, [1, 3, 5])
assert_equal(db.get_column('test', 'b').values, [2, 4, 6])
assert_equal(len(db), 1)
assert_equal(db.table_names, ['test'])
def test_sql_set_item(self):
db = SQLDatabase(':memory:')
db.add_table('test')
db.add_column('test', 'a')
db.add_column('test', 'b')
db.add_row('test', dict(a=1, b=2))
db.add_row('test', dict(a=3, b=4))
db.add_row('test', dict(a=5, b=6))
db.set_item('test', 'a', 0, 10)
db.set_item('test', 'b', 1, 'a')
assert_equal(db.get_column('test', 'a').values, [10, 3, 5])
assert_equal(db.get_column('test', 'b').values, [2, 'a', 6])
def test_sql_get_table(self):
db = SQLDatabase(':memory:')
db.add_table('test')
db.add_column('test', 'a', data=np.arange(10, 20))
db.add_column('test', 'b', data=np.arange(20, 30))
assert_equal(
db.get_table('test').values,
list(zip(np.arange(10, 20), np.arange(20, 30))))
assert_is_instance(db.get_table('test'), SQLTable)
with pytest.raises(KeyError):
db.get_table('not_a_table')
def test_sql_droptable(self):
db = SQLDatabase(':memory:')
db.add_table('test')
db.add_column('test', 'a')
db.add_column('test', 'b')
db.add_row('test', dict(a=1, b=2))
db.add_row('test', dict(a=3, b=4))
db.add_row('test', dict(a=5, b=6))
db.drop_table('test')
assert_equal(db.table_names, [])
with pytest.raises(KeyError):
db['test']
def test_sql_getitem_table_force(self):
db = SQLDatabase(':memory:')
db.add_table('test')
db.add_column('test', 'a', data=np.arange(10, 20))
db.add_column('test', 'b', data=np.arange(20, 30))
with pytest.raises(ValueError):
db[1]
with pytest.raises(ValueError):
db[1, 2]
with pytest.raises(ValueError):
db[1, 2, 'test']
with pytest.raises(ValueError):
db[[1, 2], 'test']
def test_sqltable_add_column(self):
db = SQLDatabase(':memory:')
db.add_table('test')
db['test'].add_column('a')
db['test'].add_column('b')
db['test'].add_column('c', data=[1, 2, 3])
assert_equal(db.get_column('test', 'a').values, [None, None, None])
assert_equal(db.get_column('test', 'b').values, [None, None, None])
assert_equal(db.get_column('test', 'c').values, [1, 2, 3])
assert_equal(len(db), 1)
assert_equal(db.table_names, ['test'])
assert_equal(db.column_names('test'), ['a', 'b', 'c'])
assert_equal(len(db['test']), 3)
def test_sql_copy(self):
db = SQLDatabase(':memory:')
db.add_table('test')
db.add_column('test', 'a')
db.add_column('test', 'b')
db.add_row('test', dict(a=1, b=2))
db.add_row('test', dict(a=3, b=4))
db.add_row('test', dict(a=5, b=6))
db2 = db.copy()
assert_equal(db2.table_names, ['test'])
assert_equal(db2.column_names('test'), ['a', 'b'])
assert_equal(db2.get_column('test', 'a').values, [1, 3, 5])
assert_equal(db2.get_column('test', 'b').values, [2, 4, 6])
def test_sqltable_add_row_add_columns(self):
db = SQLDatabase(':memory:')
db.add_table('test')
db.add_column('test', 'a')
db.add_column('test', 'b')
db['test'].add_row(dict(a=1, b=2))
db['test'].add_row(dict(a=3, c=4), add_columns=False)
db['test'].add_row(dict(a=5, d=6), add_columns=True)
assert_equal(db.get_column('test', 'a').values, [1, 3, 5])
assert_equal(db.get_column('test', 'b').values, [2, None, None])
assert_equal(len(db), 1)
assert_equal(db.table_names, ['test'])
assert_equal(db.column_names('test'), ['a', 'b', 'd'])
def test_sql_setitem_tuple_only(self):
db = SQLDatabase(':memory:')
db.add_table('test')
db.add_column('test', 'a', data=np.arange(10, 20))
db.add_column('test', 'b', data=np.arange(20, 30))
with pytest.raises(KeyError):
db[1] = 0
with pytest.raises(KeyError):
db['notable'] = 0
with pytest.raises(KeyError):
db[['test', 0]] = 0
with pytest.raises(KeyError):
db[1, 0] = 0
def test_sql_setitem(self):
db = SQLDatabase(':memory:')
db.add_table('test')
db.add_column('test', 'a', data=np.arange(10, 20))
db.add_column('test', 'b', data=np.arange(20, 30))
db['test', 'a'] = np.arange(50, 60)
db['test', 0] = {'a': 1, 'b': 2}
db['test', 'b', 5] = -999
expect = np.transpose([np.arange(50, 60), np.arange(20, 30)])
expect[0] = [1, 2]
expect[5, 1] = -999
assert_equal(db['test'].values, expect)
def test_sql_select_where(self):
db = SQLDatabase(':memory:')
db.add_table('test')
db.add_column('test', 'a', data=np.arange(10, 20))
db.add_column('test', 'b', data=np.arange(20, 30))
a = db.select('test', columns='a', where={'a': 15})
assert_equal(a, 15)
a = db.select('test', columns=['a', 'b'], where={'b': 22})
assert_equal(a, [(12, 22)])
a = db.select('test', columns=['a', 'b'], where=None)
assert_equal(a, list(zip(np.arange(10, 20), np.arange(20, 30))))
a = db.select('test', columns=['a', 'b'], where=['a > 12', 'b < 26'])
assert_equal(a, [(13, 23), (14, 24), (15, 25)])
def test_sql_add_table_from_data_invalid(self):
db = SQLDatabase(':memory:')
with assert_raises(TypeError):
db.add_table('test', data=[1, 2, 3])
with assert_raises(TypeError):
db.add_table('test', data=1)
with assert_raises(TypeError):
db.add_table('test', data=1.0)
with assert_raises(TypeError):
db.add_table('test', data='test')
def test_sql_repr(self):
db = SQLDatabase(':memory:')
db.add_table('test')
db.add_column('test', 'a', data=np.arange(10, 20))
db.add_column('test', 'b', data=np.arange(20, 30))
db.add_table('test2')
db.add_column('test2', 'a', data=np.arange(10, 20))
db.add_column('test2', 'b', data=np.arange(20, 30))
expect = f"SQLDatabase ':memory:' at {hex(id(db))}:\n"
expect += "\ttest: 2 columns 10 rows\n"
expect += "\ttest2: 2 columns 10 rows"
assert_equal(repr(db), expect)
db = SQLDatabase(':memory:')
expect = f"SQLDatabase ':memory:' at {hex(id(db))}:\n"
expect += "\tEmpty database."
assert_equal(repr(db), expect)
def test_sql_set_column(self):
db = SQLDatabase(':memory:')
db.add_table('test')
db.add_column('test', 'a')
db.add_column('test', 'b')
db.add_row('test', dict(a=1, b=2))
db.add_row('test', dict(a=3, b=4))
db.add_row('test', dict(a=5, b=6))
db.set_column('test', 'a', [10, 20, 30])
db.set_column('test', 'b', [20, 40, 60])
assert_equal(db.get_column('test', 'a').values, [10, 20, 30])
assert_equal(db.get_column('test', 'b').values, [20, 40, 60])
with pytest.raises(KeyError):
db.set_column('test', 'c', [10, 20, 30])
with pytest.raises(ValueError):
db.set_column('test', 'a', [10, 20, 30, 40])
def test_sql_set_row(self):
db = SQLDatabase(':memory:')
db.add_table('test')
db.add_column('test', 'a')
db.add_column('test', 'b')
db.add_row('test', dict(a=1, b=2))
db.add_row('test', dict(a=3, b=4))
db.add_row('test', dict(a=5, b=6))
db.set_row('test', 0, dict(a=10, b=20))
db.set_row('test', 1, dict(a=20, b=40))
db.set_row('test', 2, dict(a=30, b=60))
assert_equal(db.get_column('test', 'a').values, [10, 20, 30])
assert_equal(db.get_column('test', 'b').values, [20, 40, 60])
with pytest.raises(IndexError):
db.set_row('test', 3, dict(a=10, b=20))
with pytest.raises(IndexError):
db.set_row('test', -4, dict(a=10, b=20))
def test_sql_get_column(self):
db = SQLDatabase(':memory:')
db.add_table('test')
db.add_column('test', 'a', data=np.arange(10, 20))
db.add_column('test', 'b', data=np.arange(20, 30))
assert_equal(db.get_column('test', 'a').values, np.arange(10, 20))
assert_equal(db.get_column('test', 'b').values, np.arange(20, 30))
assert_is_instance(db.get_column('test', 'a'), SQLColumn)
assert_is_instance(db.get_column('test', 'b'), SQLColumn)
# same access from table
assert_equal(
db.get_table('test').get_column('a').values, np.arange(10, 20))
assert_equal(
db.get_table('test').get_column('b').values, np.arange(20, 30))
assert_is_instance(db.get_table('test').get_column('a'), SQLColumn)
assert_is_instance(db.get_table('test').get_column('b'), SQLColumn)
with pytest.raises(KeyError):
db.get_column('test', 'c')
with pytest.raises(KeyError):
db.get_table('test').get_column('c')
def test_sql_getitem_tuple(self):
db = SQLDatabase(':memory:')
db.add_table('test')
db.add_column('test', 'a', data=np.arange(10, 20))
db.add_column('test', 'b', data=np.arange(20, 30))
assert_equal(db['test', 'a'].values, np.arange(10, 20))
assert_equal(db['test', 'b'].values, np.arange(20, 30))
assert_is_instance(db['test', 'a'], SQLColumn)
assert_is_instance(db['test', 'b'], SQLColumn)
assert_equal(db['test', 4].values, (14, 24))
assert_is_instance(db['test', 4], SQLRow)
assert_equal(db['test', -1].values, (19, 29))
assert_is_instance(db['test', -1], SQLRow)
assert_equal(db['test', 'a', 4], 14)
assert_equal(db['test', 'b', 4], 24)
assert_equal(db['test', 'a', -1], 19)
assert_equal(db['test', 'b', -1], 29)
assert_equal(db['test', 4, 'a'], 14)
assert_equal(db['test', 4, 'b'], 24)
assert_equal(db['test', -1, 'a'], 19)
assert_equal(db['test', -1, 'b'], 29)