class TestCreateAPI:
db = SQLiteDB(':memory:')
def test_is_slotted(self):
with pytest.raises(AttributeError):
CreateQuery(self.db).__dict__
def test_attributes(self):
expected = ('_db', '_fields', '_table')
result = CreateQuery(self.db).__slots__
assert result == expected
def test_has_build_method(self):
assert hasattr(CreateQuery(self.db), 'build') is True
def test_has_execute_method(self):
assert hasattr(CreateQuery(self.db), 'execute') is True
def test_has_table_method(self):
assert hasattr(CreateQuery(self.db), 'table') is True
def test_has_fields_method(self):
assert hasattr(CreateQuery(self.db), 'fields') is True
def test_has_from_model_method(self):
assert hasattr(CreateQuery(self.db), 'from_model') is True
def test_has_str_method(self):
assert hasattr(CreateQuery(self.db), '__str__') is True
class TestUpdateQueryAPI:
db = SQLiteDB(':memory:')
def test_is_slotted(self):
with pytest.raises(AttributeError):
UpdateQuery(db=self.db).__dict__
def test_attributes(self):
expected = ('_db', '_fields', '_table')
result = UpdateQuery(db=self.db).__slots__
assert result == expected
def test_is_filterable(self):
assert hasattr(UpdateQuery(db=self.db), 'where') is True
def test_can_set_updates_rules(self):
query = UpdateQuery(db=self.db).table(Trainer)
assert hasattr(query, 'fields') is True
query.fields(Trainer.age == 18)
expected = 'UPDATE trainer SET age = 18'
assert query.build() == expected
def test_updates_rules_can_be_chained(self):
query = UpdateQuery(db=self.db).table(Trainer).fields(Trainer.name == 'Giovanni')
query.fields(Trainer.age == 18)
expected = "UPDATE trainer SET name = 'Giovanni', age = 18"
assert query.build() == expected
def test_can_output_selectquery_as_string(self):
query = UpdateQuery(db=self.db).table(Trainer).fields(Trainer.name == 'Jessie').where(Trainer.age < 18)
expected = "(UPDATE trainer SET name = 'Jessie' WHERE trainer.age < 18)"
assert str(query) == expected
class TestInsertQueryAPI:
db = SQLiteDB(':memory:')
def test_is_slotted(self):
with pytest.raises(AttributeError):
InsertQuery(Trainer).__dict__
def test_attributes(self):
expected = ('_db', '_fields', '_table', '_values')
result = InsertQuery(self.db).__slots__
assert result == expected
def test_can_specify_fields_to_insert(self):
assert hasattr(InsertQuery(self.db), 'fields') is True
def test_can_specify_dict_values(self):
assert hasattr(InsertQuery(self.db), 'from_dicts') is True
def test_can_specify_table(self):
assert hasattr(InsertQuery(self.db), 'table') is True
def test_can_be_output_as_string(self):
query = InsertQuery(self.db).table(Trainer).from_dicts({
'name': 'Giovanni',
'age': 42
})
expected = "(INSERT INTO trainer (age, name) VALUES (?, ?))"
assert str(query) == expected
class TestSQLiteDBDropQueryBuilder:
db = SQLiteDB(':memory:')
def test_drop_returns_a_DropQuery(self):
assert isinstance(self.db.drop(), DropQuery) is True
def test_drop_a_table(self):
query = self.db.drop(Trainer)
expected = 'DROP TABLE IF EXISTS trainer'
assert self.db.build_drop(query) == expected
class TestSQLiteDBCreateQueryBuilder:
db = SQLiteDB(':memory:')
def test_create_returns_a_CreateQuery(self):
assert isinstance(self.db.create(), CreateQuery) is True
def test_create_from_model(self):
query = self.db.create().from_model(Trainer)
expected = (
"CREATE TABLE IF NOT EXISTS trainer (age INTEGER NOT NULL, "
"name TEXT NOT NULL, pk INTEGER PRIMARY KEY AUTOINCREMENT)"
)
assert self.db.build_create(query) == expected
class TestSQLiteDBInsertQueryBuilder:
db = SQLiteDB(':memory:')
def test_insert_returns_a_InsertQuery(self):
assert isinstance(self.db.insert(), InsertQuery) is True
def test_update_one_row_from_dicts(self):
query = self.db.insert().from_dicts({
'name': 'Giovanni',
'age': 42
}).table(Trainer)
expected = "INSERT INTO trainer (age, name) VALUES (?, ?)"
assert self.db.build_insert(query) == expected
class TestDropTableAPI:
db = SQLiteDB(':memory:')
def test_is_slotted(self):
with pytest.raises(AttributeError):
DeleteQuery(db=self.db).__dict__
def test_attributes(self):
expected = ('_db', '_table')
result = DeleteQuery(db=self.db).__slots__
assert result == expected
def test_has_table_method(self):
assert hasattr(DeleteQuery(db=self.db), 'table') is True
class TestSQLiteDBDeleteQueryBuilder:
db = SQLiteDB(':memory:')
def test_delete_returns_a_DeleteQuery(self):
assert isinstance(self.db.delete(), DeleteQuery) is True
def test_delete_all_rows(self):
query = self.db.delete().table(Trainer)
expected = 'DELETE FROM trainer'
assert self.db.build_delete(query) == expected
def test_delete_filtered_rows(self):
query = self.db.delete().table(Trainer).where(Trainer.name == 'Giovanni')
expected = "DELETE FROM trainer WHERE trainer.name = 'Giovanni'"
assert self.db.build_delete(query) == expected
class TestCreateQueries():
db = SQLiteDB(':memory:')
def test_can_output_raw_sql_query(self):
query = CreateQuery(self.db).table('trainer').fields(
TextField(name='name'), IntegerField(name='age'))
result = query.build()
expected = "CREATE TABLE IF NOT EXISTS trainer (age INTEGER NOT NULL, name TEXT NOT NULL)"
assert result == expected
def test_can_output_raw_sql_query_from_model(self):
query = CreateQuery(self.db).from_model(Trainer)
result = query.build()
print(result)
expected = ('CREATE TABLE IF NOT EXISTS trainer '
'(age INTEGER NOT NULL, name TEXT NOT NULL, '
'pk INTEGER PRIMARY KEY AUTOINCREMENT)')
assert result == expected
def test_set_table_field_names(self):
CreateQuery(self.db).table('trainer').fields(
TextField(name='name'), IntegerField(name='age')).execute()
# Collect field names of the pokemon table
db_fields = [
row[1] for row in self.db._connection.execute(
'PRAGMA table_info(trainer)').fetchall()
]
assert len(db_fields) == 2
for expected_field in ['name', 'age']:
assert expected_field in db_fields
def test_set_table_field_names_from_model(self):
CreateQuery(self.db).from_model(Trainer).execute()
# Collect field names of the pokemon table
db_fields = [
row[1] for row in self.db._connection.execute(
'PRAGMA table_info(trainer)').fetchall()
]
assert len(db_fields) == 2
for expected_field in ['name', 'age']:
assert expected_field in db_fields
class TestDeleteQueryAPI:
db = SQLiteDB(':memory:')
def test_is_slotted(self):
with pytest.raises(AttributeError):
DeleteQuery(self.db).__dict__
def test_attributes(self):
expected = ('_db', '_table')
result = DeleteQuery(self.db).__slots__
assert result == expected
def test_is_filterable(self):
assert hasattr(DeleteQuery(self.db), 'where') is True
def test_can_output_selectquery_as_string(self):
result = str(
DeleteQuery(self.db).table(Trainer).where(Trainer.age > 18))
expected = '(DELETE FROM trainer WHERE trainer.age > 18)'
assert result == expected
class TestSQLiteDBUpdateQueryBuilder:
db = SQLiteDB(':memory:')
def test_update_returns_a_UpdateQuery(self):
assert isinstance(self.db.update(), UpdateQuery) is True
def test_update_with_table_and_field(self):
query = self.db.update(Trainer.name == 'Giovanni').table(Trainer)
expected = "UPDATE trainer SET name = 'Giovanni'"
assert self.db.build_update(query) == expected
def test_update_with_table_and_fields(self):
query = self.db.update(Trainer.name == 'Giovanni', Trainer.age == 18).table(Trainer)
expected = "UPDATE trainer SET name = 'Giovanni', age = 18"
assert self.db.build_update(query) == expected
def test_update_with_filters(self):
query = self.db.update(Trainer.name == 'Giovanni').table(Trainer).where(Trainer.age > 18)
expected = "UPDATE trainer SET name = 'Giovanni' WHERE trainer.age > 18"
assert self.db.build_update(query) == expected
class TestSQLiteDBSelectQueryBuilder:
db = SQLiteDB(':memory:')
def test_select_returns_a_SelectQuery(self):
assert isinstance(self.db.select(), SelectQuery) is True
def test_select_from_one_table_with_all_fields(self):
query = self.db.select().tables(Trainer)
expected = 'SELECT * FROM trainer'
assert self.db.build_select(query) == expected
def test_select_from_several_tables_with_all_fields(self):
query = self.db.select().tables(Trainer, Pokemon)
expected = 'SELECT * FROM trainer, pokemon'
assert self.db.build_select(query) == expected
def test_select_from_one_table_with_several_fields(self):
query = self.db.select(Trainer.name, Trainer.age).tables(Trainer)
expected = 'SELECT trainer.name, trainer.age FROM trainer'
assert self.db.build_select(query) == expected
def test_select_from_one_table_with_one_criterion(self):
query = self.db.select().tables(Trainer).where(Trainer.age > 18)
expected = 'SELECT * FROM trainer WHERE trainer.age > 18'
assert self.db.build_select(query) == expected
def test_select_from_one_table_with_several_criteria(self):
query = self.db.select().tables(Trainer).where(Trainer.age > 18, Trainer.name == 'Giovanni')
expected = "SELECT * FROM trainer WHERE trainer.name = 'Giovanni' AND trainer.age > 18"
assert self.db.build_select(query) == expected
def test_select_from_one_table_with_limit(self):
query = self.db.select().tables(Trainer).limit(10)
expected = 'SELECT * FROM trainer LIMIT 10'
assert self.db.build_select(query) == expected
def test_select_from_one_table_with_limit_and_offset(self):
query = self.db.select().tables(Trainer).limit(10).offset(42)
expected = 'SELECT * FROM trainer LIMIT 10 OFFSET 42'
assert self.db.build_select(query) == expected
class BaseTestCase:
TRAINERS = {
'Giovanni': {
'name': 'Giovanni',
'age': 42
},
'James': {
'name': 'James',
'age': 21
},
'Jessie': {
'name': 'Jessie',
'age': 17
},
}
POKEMONS = {
'Kangaskhan': {
'name': 'Kangaskhan',
'level': 29,
'trainer': 1
},
'Koffing': {
'name': 'Koffing',
'level': 9,
'trainer': 2
},
'Wobbuffet': {
'name': 'Wobbuffet',
'level': 19,
'trainer': 3
},
}
ATTACKS = {
'Rage': {
'name': 'Rage',
'accuracy': 1.0
},
'Smog': {
'name': 'Smog',
'accuracy': 0.7
},
'Safeguard': {
'name': 'Safeguard',
'accuracy': 0.10
}
}
def setup_method(self):
self.db = SQLiteDB(DB_NAME)
self.db.register(Trainer, Pokemon, Attack)
def add_attack(self, names):
try:
names = names.split()
except:
pass
for name in names:
InsertQuery(self.db).table(Attack).from_dicts(
self.ATTACKS[name]).execute()
def add_trainer(self, names):
try:
names = names.split()
except:
pass
for name in names:
InsertQuery(self.db).table(Trainer).from_dicts(
self.TRAINERS[name]).execute()
def add_pokemon(self, names):
try:
names = names.split()
except:
pass
for name in names:
InsertQuery(self.db).table(Pokemon).from_dicts(
self.POKEMONS[name]).execute()
def test_drop_table_with_foreign_keys(self):
db = SQLiteDB(':memory:')
db.create().from_model(Trainer).execute()
db.create().from_model(Pokemon).execute()
DropQuery(db).table(Pokemon).execute()
def test_drop_table(self):
db = SQLiteDB(':memory:')
db.create().from_model(Trainer).execute()
DropQuery(db).table(Trainer).execute()
def test_drop_uncreated_table(self):
db = SQLiteDB(':memory:')
DropQuery(db).table(Trainer).execute()
class TestSQLiteDBAPI:
def setup_method(self):
self.db = SQLiteDB(':memory:')
def table_exists(self, model):
with closing(self.db._connection.cursor()) as cursor:
query = cursor.execute(
"SELECT name FROM sqlite_master WHERE type='table' AND name='{}'".format(model.__name__.lower())
)
return bool(len(query.fetchall()))
def count_tables(self):
with closing(self.db._connection.cursor()) as cursor:
query = cursor.execute("SELECT count(*) as number FROM sqlite_master WHERE type='table'").fetchall()
return query[0][0]
def test_is_slotted(self):
with pytest.raises(AttributeError):
self.db.__dict__
def test_has_atomic_method(self):
assert hasattr(self.db, 'atomic') is True
def test_register_a_custom_model(self):
assert self.table_exists(Trainer) == False
self.db.register(Trainer)
assert self.table_exists(Trainer) == True
def test_register_several_custom_models(self):
assert self.count_tables() == 0
self.db.register(Trainer, Pokemon)
assert self.table_exists(Trainer) == True
assert self.table_exists(Pokemon) == True
def test_store_a_database_reference_into_model_when_registered(self):
self.db.register(Trainer)
try:
Trainer._db
except AttributeError:
pytest.fail()
def test_no_database_reference_in_unregistered_model(self):
class CustomModel(Model):
pass
with pytest.raises(AttributeError):
CustomModel._db
def test_register_an_existing_table(self):
self.db.register(Trainer)
try:
self.db.register(Trainer)
except:
pytest.fail()
def test_pragma_foreign_key_is_on(self):
result = self.db._connection.execute('PRAGMA foreign_keys').fetchone()[0]
assert result == 1
def teardown_method(self):
try:
os.remove(DB_NAME)
except:
return
def setup_method(self):
self.db = SQLiteDB(':memory:')
def setup_method(self):
self.db = SQLiteDB(DB_NAME)
self.db.register(Trainer, Pokemon, Attack)
