def insert_product_values(self, print_count_values=False):
product_insert = 0
product_already_check = 0
for element in self.json_products.products:
all_products = product_insert + product_already_check
VERIF = ["Vérification des produits enregistrés.",
"Produits dans la base de données :",
f"{all_products} / {len(self.json_products.products)}"]
if all_products %10 == 0:
system("cls")
if print_count_values:
print(display(150, "=", VERIF, 2, "|"))
self.brow_value = Make_Query(SQL_CONNECTORS,
f'SELECT name_product FROM product WHERE name_product = "{element[0]}"',
"READ", DTB).result
if self.brow_value == []:
self.category_id = int(Make_Query(SQL_CONNECTORS,
f"SELECT id FROM category WHERE name='{element[1]}'",
"READ", DTB).result[0][0])
Make_Query(SQL_CONNECTORS,
f'INSERT INTO product(name_product, category_ID,\
nutrition_grades_product, store_product, \
description_product, url_product)\
VALUES("{element[0]}","{self.category_id}","{element[2].upper()}",\
"{element[3]}","{element[4]}","{element[5]}")',
"UPDATE", DTB)
product_insert += 1
self.all_prod.append(element[0])
else:
product_already_check +=1
return self.all_prod
def check_if_exists(self):
try:
Make_Query(SQL_CONNECTORS, f"USE {self.dtb}", "EXECUTE")
except self.check_err_prog:
Make_Query(SQL_CONNECTORS,
f"DROP DATABASE IF EXISTS {self.dtb};",
"DELETE")
Make_Query(SQL_CONNECTORS,
f"CREATE DATABASE {self.dtb};",
"CREATE")
def read_column_sql(column, where=None):
column_result = []
if where != None:
query = Make_Query(SQL_CONNECTORS,
f"SELECT `{column}` FROM category {where}",
"READ", DTB).result
else:
query = Make_Query(SQL_CONNECTORS,
f"SELECT `{column}` FROM category", "READ",
DTB).result
for elements in query:
for element in elements:
column_result.append(element)
return column_result
def init_table():
Make_Query(
SQL_CONNECTORS, '''CREATE TABLE IF NOT EXISTS category
(
id SMALLINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL UNIQUE,
url VARCHAR(100) NOT NULL
)
ENGINE=INNODB;''', "CREATE", DTB)
def init_table():
Make_Query(
SQL_CONNECTORS, """CREATE TABLE IF NOT EXISTS favourites
(
id_favourite SMALLINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
favourite_product_ID SMALLINT UNSIGNED NOT NULL,
substituted_product_ID SMALLINT UNSIGNED NOT NULL,
CONSTRAINT fk_favourite_product_ID FOREIGN KEY (favourite_product_ID) REFERENCES product(id_product) ON DELETE CASCADE,
CONSTRAINT fk_substituted_product_ID FOREIGN KEY (substituted_product_ID) REFERENCES product(id_product) ON DELETE CASCADE
)
ENGINE=INNODB;""", "CREATE", DTB)
def display_favourite_product(ident, ident2):
favourite_prod = Make_Query(
SQL_CONNECTORS, f"SELECT * FROM product WHERE id_product={ident}",
"READ", DTB).result[0]
substituted_prod = Make_Query(
SQL_CONNECTORS, f"SELECT * FROM product WHERE id_product={ident2}",
"READ", DTB).result[0]
replace_fp = favourite_prod[4].replace("[", "").replace("]", "")
replace_sp = substituted_prod[4].replace("[", "").replace("]", "")
fav_and_sub_details = [
f"Produit favoris : {favourite_prod[1]}", "Description : ",
f"{favourite_prod[5]}", "Valeur nutritionnelle : ",
f"{favourite_prod[3]}", "URL : ", f"{favourite_prod[6]}",
"Magasins : ", f"""{replace_fp.replace(",", ", ")}""", "",
"Produit substitué :", f"{substituted_prod[1]}", "Description :",
f"{substituted_prod[5]}", "Valeur nutritionnelle :",
f"{substituted_prod[3]}", "URL :", f"{substituted_prod[6]}",
"Magasins :", f"""{replace_sp.replace(",", ", ")}"""
]
system("cls")
input(display(150, "=", fav_and_sub_details, False, "|"))
def display_product(column=[], ref_to_check="", value_to_check="",
comparator="AND", second_ref="", second_value="",
order_by="", numlimit=0, numtypelimit=""):
selected_column = ",".join(column)
condition = f"WHERE {ref_to_check} LIKE '{value_to_check}'"
condition_2 = f" {comparator} {second_ref} LIKE '{second_value}'"
number_to_check = f" ORDER BY {order_by} {numtypelimit} LIMIT {numlimit}"
if ref_to_check == "" and order_by == "":
display = Make_Query(SQL_CONNECTORS,
f"SELECT {selected_column} FROM product",
"READ", DTB).result
elif ref_to_check != "" and order_by == "":
if second_ref == "":
display = Make_Query(SQL_CONNECTORS,
f"SELECT {selected_column} FROM product {condition}",
"READ", DTB).result
else:
display = Make_Query(SQL_CONNECTORS,
f"SELECT {selected_column} FROM product {condition}{condition_2}",
"READ", DTB).result
elif ref_to_check == "" and order_by != "":
display = Make_Query(SQL_CONNECTORS,
f"SELECT {selected_column} FROM product {number_to_check}",
"READ", DTB).result
elif ref_to_check != "" and order_by != "":
if second_ref == "":
display = Make_Query(SQL_CONNECTORS,
f"SELECT {selected_column} FROM product {condition}{number_to_check}",
"READ", DTB).result
else:
display = Make_Query(SQL_CONNECTORS,
f"SELECT {selected_column} FROM product {condition}{condition_2}{number_to_check}",
"READ", DTB).result
return display
def init_table():
Make_Query(
SQL_CONNECTORS,
'''CREATE TABLE IF NOT EXISTS product
(
id_product SMALLINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
name_product VARCHAR(150) NOT NULL,
category_ID SMALLINT UNSIGNED NOT NULL,
nutrition_grades_product VARCHAR(1) NOT NULL,
store_product VARCHAR(100),
description_product VARCHAR(1000) NOT NULL,
url_product VARCHAR(1000) NOT NULL,
CONSTRAINT fk_product_category_ID FOREIGN KEY (category_ID) REFERENCES category(id)
)
ENGINE=INNODB;''',
"CREATE", DTB)
def update_category_values(self):
for name in self.categories:
Make_Query(
SQL_CONNECTORS,
f"INSERT IGNORE INTO category(name, url) VALUES('{name}', '{self.url}')",
"UPDATE", DTB)
def suppress_all():
Make_Query(SQL_CONNECTORS, "TRUNCATE TABLE favourites;", "DELETE", DTB)
def display_all_favourite_product():
all_fav = Make_Query(
SQL_CONNECTORS,
"SELECT favourite_product_ID,substituted_product_ID FROM favourites;",
"READ", DTB).result
return all_fav
def record_product(favorite_prod_ids, substituted_prod_ids):
Make_Query(
SQL_CONNECTORS, f"""INSERT IGNORE INTO\
favourites(favourite_product_ID,substituted_product_ID)\
VALUES({favorite_prod_ids},{substituted_prod_ids})""", "UPDATE",
DTB)