def insert_products_in_database(self, products: List[Product],
session: Session) -> None:
"""
Insert products in user's database.
:param products: List of products to insert in database
:param session: Session
:return: None
"""
columns = sorted(products[0].__dict__.keys())
category_association = []
store_association = []
values = []
for product in products:
product_id = str(uuid1())
values.append(
(product.brands, ', '.join(product.categories_tags),
product_id, product.nutriscore_grade,
', '.join(product.packaging_tags)
if product.packaging_tags else None, product.product_name_fr,
', '.join(product.stores_tags), product.url))
for category in product.categories_tags:
category_association.append((product_id, category))
for store in product.stores_tags:
store_association.append((product_id, store))
stmt_category_association = """
INSERT IGNORE INTO Product_Category_Association
(product_id, category_id)
VALUES (%s, (SELECT id FROM Categories WHERE off_id = %s))
"""
stmt_store_association = """
INSERT IGNORE INTO Product_Store_Association
(product_id, store_id)
VALUES (%s, (SELECT id FROM Stores WHERE name = %s))
"""
stmt = session.prepare_insert_statement(self.table, columns)
# Insert product values in Products
session.insert(stmt, values)
# Insert Product_Category_Association
session.insert(stmt_category_association, category_association)
# Insert Product_Store_Association
session.insert(stmt_store_association, store_association)
def insert_in_database(self, session: Session,
stores: List[Store]) -> None:
"""
Method to put all stores in database.
:param session: Session
:param stores: list containing all stores on OpenFoodFacts
:return: None
"""
columns = sorted(stores[0].__dict__.keys())
values = []
for store in stores:
values.append((str(uuid1()), store.name, store.url))
stmt = session.prepare_insert_statement(self.table, columns)
session.insert(stmt, values)
def insert_in_database(self, categories: List[Category],
session: Session) -> None:
"""
Put categories in user's database.
:param categories: List containing all categories on OpenFoodFacts
:param session: Session
:return: None
"""
columns = sorted(categories[0].__dict__.keys())
values = []
for category in categories:
values.append(
(str(uuid1()), category.name, category.off_id, category.url))
stmt = session.prepare_insert_statement(self.table, columns)
session.insert(stmt, values)
