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