class Database:
""" I'm the database. """
def __init__(self):
""" """
self.connection = Connection()
self.db_name = self.connection.db_name
self.tables = Tables()
self.view = Views()
def db_create(self):
""" I create the database. """
try:
self.connection.execute(
"CREATE DATABASE IF NOT EXISTS {} DEFAULT CHARACTER SET 'utf8'"
.format(self.db_name))
self.view.display_text("REUSSITE : la base est crée.")
except Exception as error:
self.view.display_text_error("ECHEC : la base n'est pas crée.",
f"Type de l'erreur : {error}")
def tables_create(self):
""" I create tables in the database. """
for table_name in self.tables.TABLES:
table_description = self.tables.TABLES[table_name]
try:
self.connection.execute(table_description)
self.view.display_text(
f"REUSSITE : la table {table_name.upper()} est active.")
except Exception as error:
self.view.display_text_error(
"ECHEC : les tables ne sont pas crées.",
f"Type de l'erreur : {error}")
def tables_delete(self):
""" I delete all the tables in the database. """
# self.connection.close()
# self.connection = Connection()
query = "SET FOREIGN_KEY_CHECKS = 0;"
self.connection.execute(query)
for n in range(len(self.tables.tab_names)):
try:
query = f"TRUNCATE TABLE {self.tables.tab_names[n]};"
self.connection.execute(query)
self.view.display_text(
f"REUSSITE : la table {self.tables.tab_names[n].upper()} est remise à zéro."
)
time.sleep(1)
except Exception as error:
self.view.display_text_error(
"ECHEC : problème lors du delete des tables",
f"Type de l'erreur : {error}")
class Load:
def __init__(self):
""" """
self.views = Views()
self.connection = Connection()
self.open_json()
def open_json(self):
""" I open the json. """
with open(
"thesubstitute/off_data_transform.json", encoding="utf-8"
) as json_file:
self.my_products = json.load(json_file)
def load_nutriscore(self):
""" I load the nutriscore and their id into the table. """
try:
query = "INSERT INTO nutriscore (nut_id, nut_type) VALUES (1, 'A'), (2, 'B'), (3, 'C'), (4, 'D'), (5, 'E')"
self.connection.execute(query)
self.connection.commit()
self.views.display_text(
"""
REUSSITE :
Les différents Nutriscore ont été chargés dans la base."""
)
except:
self.views.display_text_error("REUSSITE : Les Nutriscore étaient dans la base.")
def load_data(self):
""" I load all the data from transform.json to their table. """
# Loading the nutriscore
self.load_nutriscore()
for prod_key in list(self.my_products.keys()):
prod_to_load = self.my_products[prod_key]
# Insert Products
if self.read_produits(prod_key) is False:
nut_id = self.read_nutriscore(prod_to_load["nutriscore_grade"][0])
add_product = ("INSERT INTO produits SET prod_id=%s, prod_nom=%s, prod_url=%s, nut_id=%s")
data_product = (prod_key, prod_to_load['product_name_fr'], prod_to_load['url'], nut_id)
self.connection.execute(add_product, data_product)
self.connection.commit()
else:
pass
# Insert Categories
for n in range(len(prod_to_load["categories"])):
# In categories table
if self.read_categorie(prod_to_load["categories"][n]) is False:
add_categorie = ("INSERT INTO categories SET cat_nom=%s")
self.connection.execute(add_categorie, (prod_to_load['categories'][n],))
self.connection.commit()
# In prodcat table
cat_id = self.read_categorie(prod_to_load["categories"][n])
check = self.search_id(
f"SELECT * FROM prodcat WHERE cat_id='{cat_id}' AND prod_id='{prod_key}' "
)
if not (check):
add_prodcat = ("INSERT INTO prodcat SET cat_id=%s, prod_id=%s")
self.connection.execute(add_prodcat, (cat_id, prod_key))
self.connection.commit()
# Insert Marques
for n in range(len(prod_to_load["brands"])):
# In marques table
if self.read_marque(prod_to_load["brands"][n]) is False:
add_marque = ("INSERT INTO marques SET marq_nom=%s")
self.connection.execute(add_marque, (prod_to_load['brands'][n],))
self.connection.commit()
# In prodmarq table
marq_id = self.read_marque(prod_to_load["brands"][n])
check = self.search_id(
f"SELECT * FROM prodmarq WHERE marq_id='{marq_id}' AND prod_id='{prod_key}' "
)
if not (check):
add_prodmarq = ("INSERT INTO prodmarq SET marq_id=%s, prod_id=%s")
self.connection.execute(add_prodmarq, (marq_id, prod_key))
self.connection.commit()
# Insert Shops
for n in range(len(prod_to_load["stores"])):
# In shops table
if self.read_shop(prod_to_load["stores"][n]) is False:
add_shop = ("INSERT INTO shops SET shop_nom=%s")
self.connection.execute(add_shop, (prod_to_load['stores'][n],))
self.connection.commit()
# In prodshop table
shop_id = self.read_shop(prod_to_load["stores"][n])
check = self.search_id(
f"SELECT * FROM prodshop WHERE shop_id='{shop_id}' AND prod_id='{prod_key}' "
)
if not (check):
add_prodshop = ("INSERT INTO prodshop SET shop_id=%s, prod_id=%s")
self.connection.execute(add_prodshop, (shop_id, prod_key))
self.connection.commit()
self.views.display_text(
f"""
REUSSITE du chargement des produits :
{len(self.my_products.keys())} produits sont entrés en base."""
)
def read_categorie(self, value):
""" I search if the category is already in the table. """
query = ("SELECT cat_id FROM categories WHERE cat_nom LIKE %s")
self.connection.execute(query, (value,))
result = self.connection.fetchall()
if len(result) < 1:
return False
else:
return int(result[0][0])
def read_marque(self, value):
""" I search if the brand is already in the table. """
query = ("SELECT marq_id FROM marques WHERE marq_nom LIKE %s")
self.connection.execute(query, (value,))
result = self.connection.fetchall()
if len(result) < 1:
return False
else:
return int(result[0][0])
def read_shop(self, value):
""" I search if the shop is already in the table. """
query = ("SELECT shop_id FROM shops WHERE shop_nom LIKE %s")
self.connection.execute(query, (value,))
result = self.connection.fetchall()
if len(result) < 1:
return False
else:
return int(result[0][0])
def read_produits(self, value):
""" I search if the product is already in the table. """
query = ("SELECT prod_id FROM produits WHERE prod_id LIKE '%s'")
self.connection.execute(query, (int(value),))
result = self.connection.fetchall()
if len(result) < 1:
return False
else:
return int(result[0][0])
def read_nutriscore(self, value):
""" I search if the nutriscore is already in the table. """
query = ("SELECT nut_id FROM nutriscore WHERE nut_type LIKE %s")
self.connection.execute(query, (value,))
result = self.connection.fetchall()
if len(result) < 1:
return False
else:
return int(result[0][0])
def search_id(self, query):
""" I search an ID. """
self.connection.execute(query)
rows = self.connection.fetchall()
return rows
