def find(user_product):
"""
used for finding substitute from selected product
"""
user_product.categories = Category.load_from_id(user_product.id)
i = 1
substitute = list()
cur = Database.createCursor()
cur.execute("""
SELECT product_id
FROM categories_products
INNER JOIN categories
ON categories_products.category_id = categories.id
INNER JOIN products
ON categories_products.product_id = products.id
WHERE categories.name = %s
AND products.nutriscore = 'a' limit 5""",
(user_product.categories[0], ))
for elt in cur.fetchall():
substituts_with_id = dict()
substituts_with_id['id'] = i
substituts_with_id['product'] = Product.get(elt[0])
substitute.append(substituts_with_id)
i += 1
cur.close()
return substitute
def save(self):
"""
method for inserting data into mysql database
"""
cur = Database.createCursor()
sql = """
INSERT INTO
categories (name, count, url, url_id)
VALUES (%s, %s, %s, %s)"""
val = (self.name, self.count, self.url, self.url_id)
cur.execute(sql, val)
Database.databaseConnection.commit()
cur.close()
print(cur.rowcount, "record inserted.")
def save(self):
"""
Used for inserting stores on database
"""
cur = Database.createCursor()
sql = """
INSERT
INTO stores (name)
VALUES (%s) """
val = (self.name, )
cur.execute(sql, val)
Database.databaseConnection.commit()
cur.close()
print(cur.rowcount, "record inserted.")
def save(self):
"""
insert substitute in db
"""
cur = Database.createCursor()
sql = """
INSERT INTO substitutes_products (
substitute_id,
product_id)
VALUES (%s, %s) """
val = (self.id,
self.product_id)
cur.execute(sql, val)
Database.databaseConnection.commit()
cur.close()
def get(product_id):
"""
get product from a product id
"""
cur = Database.createCursor()
cur.execute(
"""
SELECT *
FROM products
WHERE id = %s """, (product_id, ))
product = cur.fetchone()
products = Product(product[0], product[6], product[1], product[2],
product[3], product[5], product[4], None, None)
cur.close()
return products
def load_from_id(product_id):
"""
load stores from product id
"""
cur = Database.createCursor()
cur.execute(
"""
SELECT name
FROM stores
INNER JOIN stores_products
ON stores.id = stores_products.store_id
WHERE product_id = %s""", (product_id, ))
stores = [''.join(x) for x in cur.fetchall()]
cur.close()
return stores
def load_from_id(product_id):
"""
find categories from a product id
"""
cur = Database.createCursor()
cur.execute(
"""
SELECT name
FROM categories
INNER JOIN categories_products
ON categories.id = categories_products.category_id
WHERE product_id = %s""", (product_id, ))
cur.close()
categories = [''.join(x) for x in cur.fetchall()]
cur.close()
return categories
def save(self):
"""
Used for inserting products on database
"""
cur = Database.createCursor()
sql = """ INSERT INTO products (
brand,
name,
image,
url,
description,
nutriscore)
VALUES (%s, %s, %s, %s, %s, %s) """
val = (self.brands, self.name, self.image, self.url, self.description,
self.nutriscore)
cur.execute(sql, val)
last_product_id = cur.lastrowid
Database.databaseConnection.commit()
if len(self.stores) > 0 and len(self.categories) > 0:
for elt in self.stores:
cur.execute(
""" select id from stores
where name = %s """, (str(elt), ))
if cur.rowcount:
id = cur.fetchone()[0]
sql = """ INSERT INTO stores_products
(store_id, product_id) VALUES (%s, %s)"""
cur.execute(sql, (int(id), int(last_product_id)))
Database.databaseConnection.commit()
cur.close()
for elt in self.categories:
cur.execute(
""" select id from categories
where url_id = %s """, (str(elt), ))
if cur.rowcount:
id = cur.fetchone()[0]
sql = """ INSERT INTO categories_products
(category_id, product_id) VALUES (%s, %s) """
cur.execute(sql, (int(id), int(last_product_id)))
Database.databaseConnection.commit()
cur.close()
cur.close()
def load(category):
"""
load products from a specific categories
"""
products_with_id = list()
i = 1
cur = Database.createCursor()
cur.execute(
"""SELECT product_id
FROM categories_products
WHERE category_id = %s""", (category, ))
product_id = cur.fetchall()
for id in product_id:
products = dict()
products['id'] = i
products['product'] = Product.get(id[0])
i += 1
products_with_id.append(products)
cur.close()
return products_with_id