def create_table(db):
"""Create table save."""
query = """
CREATE TABLE IF NOT EXISTS save (
product_choose VARCHAR(200) NOT NULL,
substitut_choose VARCHAR(200) NOT NULL,
FOREIGN KEY (product_choose) REFERENCES aliments(product_name),
FOREIGN KEY (substitut_choose) REFERENCES aliments(product_name)
) ENGINE=InnoDB;
"""
Base_fn.execute_query(db, query)
def add_data(db, data_dict):
"""Insert value in table."""
query = """
INSERT INTO categories (
name, url, products)
VALUES
(%s, %s, %s);
"""
for value in data_dict.values():
data = (value.name, value.url, value.products)
Base_fn.execute_query(db, query, data)
def add_data(db, data_list):
"""Initialize aliments table."""
query = """
INSERT INTO aliments (
store, product_name, categorie, nutrition_grades, id_off, url)
VALUES (
%s, %s, %s, %s, %s, %s);
"""
for element in data_list:
data_aliment = (element.stores, element.product_name,
element.categorie, element.nutrition_grades,
element.id_off, element.url)
Base_fn.execute_query(db, query, data_aliment)
def create_table(db):
"""Create the categories table."""
query = """
CREATE TABLE IF NOT EXISTS categories (
id SMALLINT AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
url TEXT,
products INT,
PRIMARY KEY (id),
INDEX index_name (name)
) ENGINE=InnoDB;
"""
Base_fn.execute_query(db, query)
def create_table(db):
"""Create table aliments."""
query = """
CREATE TABLE IF NOT EXISTS aliments (
product_name VARCHAR(200) NOT NULL,
id_off BIGINT,
url VARCHAR(200),
store VARCHAR(40),
nutrition_grades VARCHAR(5),
categorie VARCHAR(50) NOT NULL,
PRIMARY KEY (product_name),
FOREIGN KEY fk_name (categorie) REFERENCES categories (name)
) ENGINE=InnoDB;
"""
Base_fn.execute_query(db, query)
def get_aliments_by_category(db, category):
"""Return aliments by a categorie."""
b_query = """SELECT product_name FROM aliments WHERE categorie='{}';"""
query = b_query.format(category)
result = Base_fn.select_query(db, query)
result_filter = []
for element in result:
result_filter.append(element[0])
return result_filter
def get_data(db, data):
"""Return data from aliments table."""
b_query = """SELECT {} FROM aliments."""
query = b_query.format(data)
result = Base_fn.select_query(db, query)
result_filter = []
for element in result:
result_filter.append(element[0])
return result_filter
def get_all(db):
"""Select all table save."""
query = """SELECT save.substitut_choose,
save.product_choose, aliments.store, aliments.url
FROM aliments
INNER JOIN save
ON aliments.product_name = save.product_choose;"""
result = Base_fn.select_query(db, query)
return result
def get_data(db, data):
"""Return the column=data in categories."""
b_query = """SELECT {} FROM categories;"""
query = b_query.format(data)
result = Base_fn.select_query(db, query)
result_filter = []
for element in result:
result_filter.append(element[0])
return result_filter
def substitute_aliment(db, category):
"""Return a list of MAX_PROD healthier aliments."""
b_query = """
SELECT
product_name, store, url
FROM
aliments
WHERE
categorie='{}'
ORDER BY
nutrition_grades;
"""
query = b_query.format(category)
result = Base_fn.select_query(db, query)
result = result[0:MAX_PROD]
return result
def del_table(db):
"""Delete table aliments."""
query = """DROP TABLE IF EXISTS aliments;"""
Base_fn.execute_query(db, query)
def launcher():
"""Function to start the application."""
connection = Base_fn.create_connection(HOST, USER, PWD, DATABASE)
system = System(connection)
system.menu()
def del_table(db):
"""Delete save table."""
query = """DROP TABLE IF EXISTS save;"""
Base_fn.execute_query(db, query)
def add_data(db, data):
"""Add a data to table save."""
query = """INSERT INTO save VALUES (%s, %s);"""
Base_fn.execute_query(db, query, data)
def del_table(db):
"""Delete table."""
query = """DROP TABLE IF EXISTS categories;"""
Base_fn.execute_query(db, query)