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