def test_salesperson_crud(database_obj):
table = "salesperson"
name = "Dwight Kurt Schrute"
email = "*****@*****.**"
# Init
sp = SalesPerson(name=name, email=email)
assert sp.table_name == table
assert sp.id == -1
assert sp.name == name
assert sp.email == email
assert sp.created is None
assert sp.exists_in_db(database_obj) is False
# INSERT
sp.insert_into_db(database_obj)
assert sp.created <= datetime.now()
assert sp.exists_in_db(database_obj) is True
assert sp.id >= 0
# UPDATE
sp.name = "Dwight K. Schrute"
sp.email = "*****@*****.**"
assert sp.exists_in_db(database_obj) is True
rows = database_obj.select_rows(
table=sp.table_name,
fields=("id", "name", "email"),
filter=sql.SQL("WHERE {field} = {email}").format(
field=sql.Identifier("email"), email=sql.Literal(sp.email)
),
)
assert len(rows) == 0
assert sp.update_data_in_db(database_obj) is True
rows = database_obj.select_rows(
table=sp.table_name,
fields=("id", "name", "email"),
filter=sql.SQL("WHERE {field} = {email}").format(
field=sql.Identifier("email"), email=sql.Literal(sp.email)
),
)
assert len(rows) == 1
assert sp.exists_in_db(database_obj) is True
# SOFT DELETE
assert sp.is_active(database_obj) is True
sp.soft_delete_data_in_db(database_obj)
assert sp.is_active(database_obj) is False
def update_data_in_db(self, db_obj: Database) -> bool:
if self.exists_in_db(db_obj) is False:
log.error(f"Salesperson was not found in DB: {self}")
return False
self.modified = datetime.now()
update_data = self._export_dict_for_query()
return db_obj.update_rows_by_dict(
table=self.table_name,
field_value_dict=update_data,
filter=sql.SQL("\nWHERE {id} = {self_id}").format(
id=sql.Identifier("id"), self_id=sql.Literal(self.id)),
)
def exists_in_db(self, db_obj: Database) -> bool:
if self.id >= 0:
filter = sql.SQL("WHERE {id} = {value}").format(
id=sql.Identifier("id"),
value=sql.Literal(self.id),
)
else:
filter = sql.SQL("WHERE {email} = {value}").format(
email=sql.Identifier("email"),
value=sql.Literal(self.email),
)
select = db_obj.select_rows(
table=self.table_name,
fields=["id", "name", "email", "created", "modified", "active"],
filter=filter,
)
log.debug(f"SalesPerson.exists_in_db(): select={select}")
if len(select) == 1:
self.set_id(int(select[0]["id"]))
return True
return False
def is_active(self, db_obj: Database) -> bool:
if self.id <= -1:
if not self.exists_in_db(db_obj):
return False
select = db_obj.select_rows(
table=self.table_name,
fields=["id", "name", "email", "created", "modified", "active"],
filter=sql.SQL("WHERE {id} = {value}").format(
id=sql.Identifier("id"),
value=sql.Literal(self.id),
),
)
log.debug(f"SalesPerson.is_active(): select={select}")
return select[0]["active"]
def test_fill_template_w_keys_n_values():
keys_values = dict(key1="value_A", key2="value_B", keyX="value_Y")
template = build_key_value_template(len(keys_values.keys()))
query_ = sql.SQL("UPDATE {table} SET ").format(table=sql.Identifier("table_name"))
query = fill_template_w_keys_n_values(query_, keys_values, template)
assert type(query) is sql.Composed