def test_ok_1(self): """ Input csv file is only one """ TEST_FILE = "sqlite_write_test.csv" try: with open(TEST_FILE, "w", encoding="utf-8", newline="") as f: writer = csv.writer(f) writer.writerow(["No", "TEXT"]) writer.writerow(["1", "A"]) instance = self._create_instance(TEST_FILE, True) instance.execute() adapter = SqliteAdapter() adapter.connect(self.DB_NAME) cur = adapter.fetch("SELECT * FROM %s" % self.TBL_NAME, row_factory=self._dict_factory) count = 0 for row in cur: if count == 0: assert row == {"No": "1", "TEXT": "A"} count += 1 finally: self._clean(self.DB_NAME) self._clean(TEST_FILE)
class SqliteTableExistence(object): """ Validation for the table of sqlite """ def __init__(self, dbname, tblname, returns_bool=False): """ Args: dbname: database name tblname: table name returns_bool: return bool or not """ self.__sqlite_adptr = SqliteAdapter() self.__dbname = dbname self.__tblname = tblname self.__returns_bool = returns_bool self._logger = LisboaLog.get_logger(__name__) def __call__(self): try: self.__sqlite_adptr.connect(self.__dbname) cur = self.__sqlite_adptr.fetch( 'SELECT name FROM sqlite_master WHERE type="table" AND name="%s"' % self.__tblname ) result = cur.fetchall() if self.__returns_bool is True: return True if result else False if not result and self.__returns_bool is False: raise SqliteInvalid("Sqlite table %s not found" % self.__tblname) finally: self.__sqlite_adptr.close()
def test_ok_5(self): """ Input csv file is only one. Sqlite database already exists. Csv columns and table columns are not the same. force_insert is True. """ TEST_FILE_1 = "sqlite_write_test_1.csv" TEST_FILE_2 = "sqlite_write_test_2.csv" try: with open(TEST_FILE_1, "w", encoding="utf-8", newline="") as f: writer = csv.writer(f) writer.writerow(["No", "TEXT"]) writer.writerow(["1", "A"]) with open(TEST_FILE_2, "w", encoding="utf-8", newline="") as f: writer = csv.writer(f) writer.writerow(["No", "NAME"]) writer.writerow(["2", "John"]) instance = self._create_instance(TEST_FILE_1, True) instance.execute() instance = self._create_instance(TEST_FILE_2, False) Helper.set_property(instance, "force_insert", True) instance.execute() adapter = SqliteAdapter() adapter.connect(self.DB_NAME) cur = adapter.fetch( "SELECT * FROM %s" % self.TBL_NAME, row_factory=self._dict_factory ) count = 0 for row in cur: if count == 0: assert row == {"No": "1", "TEXT": "A", "NAME": None} elif count == 1: assert row == {"No": "2", "TEXT": None, "NAME": "John"} count += 1 finally: self._clean(self.DB_NAME) self._clean(TEST_FILE_1) self._clean(TEST_FILE_2)
def test_ok_6(self): """ Input csv file is only one. Sqlite database already exists. refresh is False. """ TEST_FILE_1 = "sqlite_write_test_1.csv" TEST_FILE_2 = "sqlite_write_test_2.csv" try: with open(TEST_FILE_1, "w", encoding="utf-8", newline="") as f: writer = csv.writer(f) writer.writerow(["No", "TEXT"]) writer.writerow(["1", "A"]) with open(TEST_FILE_2, "w", encoding="utf-8", newline="") as f: writer = csv.writer(f) writer.writerow(["No", "TEXT"]) writer.writerow(["2", "B"]) instance = self._create_instance(TEST_FILE_1, False) instance.execute() instance = self._create_instance(TEST_FILE_2, False) instance.execute() adapter = SqliteAdapter() adapter.connect(self.DB_NAME) cur = adapter.fetch( "SELECT * FROM %s" % self.TBL_NAME, row_factory=self._dict_factory ) count = 0 for row in cur: if count == 0: assert row == {"No": "1", "TEXT": "A"} elif count == 1: assert row == {"No": "2", "TEXT": "B"} count += 1 finally: self._clean(self.DB_NAME) self._clean(TEST_FILE_1) self._clean(TEST_FILE_2)
def test_ok_3(self): """ Input csv file is plural. Csv files format are not the same. """ TEST_FILE_1 = "sqlite_write_test_1.csv" TEST_FILE_2 = "sqlite_write_test_2.csv" try: with open(TEST_FILE_1, "w", encoding="utf-8", newline="") as f: writer = csv.writer(f) writer.writerow(["No", "TEXT"]) writer.writerow(["1", "A"]) with open(TEST_FILE_2, "w", encoding="utf-8", newline="") as f: writer = csv.writer(f) writer.writerow(["No", "NAME"]) writer.writerow(["2", "John"]) instance = self._create_instance(r"sqlite_write_test_.*.csv", True) instance.execute() adapter = SqliteAdapter() adapter.connect(self.DB_NAME) cur = adapter.fetch( "SELECT * FROM %s" % self.TBL_NAME, row_factory=self._dict_factory ) count = 0 for row in cur: if count == 0: assert row == {"No": "1", "TEXT": "A", "NAME": None} elif count == 1: assert row == {"No": "2", "TEXT": None, "NAME": "John"} count += 1 finally: self._clean(self.DB_NAME) self._clean(TEST_FILE_1) self._clean(TEST_FILE_2)
class TestSqliteCreation(object): def setup_method(self, method): self._db_dir = os.path.join(env.BASE_DIR, "db") self._adptr = SqliteAdapter() def test_connect_ok(self): # create spam.db os.makedirs(self._db_dir) db_file = os.path.join(self._db_dir, "spam.db") self._adptr.connect(db_file) exists_db_file = os.path.exists(db_file) shutil.rmtree(self._db_dir) assert exists_db_file is True def test_close_ok(self): # create spam.db os.makedirs(self._db_dir) db_file = os.path.join(self._db_dir, "spam.db") self._adptr.connect(db_file) self._adptr.close() exists_db_file = os.path.exists(db_file) shutil.rmtree(self._db_dir) assert exists_db_file is True def test_fetch_ok(self): # create spam.db os.makedirs(self._db_dir) db_file = os.path.join(self._db_dir, "spam.db") self._adptr.connect(db_file) self._adptr.execute("create table spam_table (id, name, age);") cursor1 = self._adptr.fetch("select * from spam_table") cursor2 = self._adptr.fetch("select * from spam_table") os.path.exists(db_file) shutil.rmtree(self._db_dir) assert isinstance(type(cursor1), type(cursor2)) is False def test_execute_many_insert_ok(self): # create spam.db os.makedirs(self._db_dir) db_file = os.path.join(self._db_dir, "spam.db") self._adptr.connect(db_file) self._adptr.execute("create table spam_table (id, name, age);") def test_insert(): dummy_data = [{"id": 1, "name": "spam1", "age": 24}] self._adptr.execute_many_insert("spam_table", ["id", "name", "age"], dummy_data) test_insert() self._adptr.commit() cursor = self._adptr.fetch("select * from spam_table;") shutil.rmtree(self._db_dir) for c in cursor: assert c == (1, "spam1", 24) def test_execute_many_insert_ng_no_tblname(self): # create spam.db os.makedirs(self._db_dir) db_file = os.path.join(self._db_dir, "spam.db") self._adptr.connect(db_file) self._adptr.execute("create table spam_table (id, name, age);") def test_insert(): dummy_data = [{"id": 1, "name": "spam1", "age": 24}] self._adptr.execute_many_insert(None, ["id", "name", "age"], dummy_data) with pytest.raises(ValueError) as execinfo: test_insert() shutil.rmtree(self._db_dir) assert str(execinfo.value) == "Parameters are missing" def test_create_table(self): # create spam.db os.makedirs(self._db_dir) db_file = os.path.join(self._db_dir, "spam.db") self._adptr.connect(db_file) columns = ["id", "na\"me", "a'ge"] self._adptr.create_table("spam_table", columns) result = self._adptr.get_column_names("spam_table") shutil.rmtree(self._db_dir) assert columns == result