def modify_some_orders_status(self): """The objective of this method is to create some interactions with orders which had been injected and pass them from 'en attente' statut to 'en preparation' status """ # We get 10 order_ids on a random way orders = self._get_10_orders() # on modifie leur statut de commande à en preparations en ajoutant 30 minutes ou 2heures with SQLconnexion() as connexion: for order in orders: step_date = order["date_debut_operation"] + datetime.timedelta( minutes=random.randint(10, 75)) with connexion.cursor() as cursor: sql = """UPDATE evolution_preparation SET date_fin_operation = %s WHERE id_commande = %s""" cursor.execute(sql, (step_date, order["id_commande"])) connexion.commit() with connexion.cursor() as cursor: sql = """INSERT INTO evolution_preparation (id_commande, id_statut_commande, date_debut_operation, date_fin_operation) VALUES (%s, %s, %s, NULL)""" cursor.execute(sql, (order["id_commande"], "2", step_date)) connexion.commit()
def _get_delivery_type_ids(self): with SQLconnexion() as connexion: with connexion.cursor() as cursor: sql = """SELECT id FROM type_livraison""" cursor.execute(sql) delivery_ids = cursor.fetchall() return delivery_ids
def _get_employee_ids(self): with SQLconnexion() as connexion: with connexion.cursor() as cursor: sql = """SELECT id_utilisateur FROM employe""" cursor.execute(sql) employe_id = cursor.fetchall() return employe_id
def set_one_employee(self, user_id, role_id): with SQLconnexion() as connexion: with connexion.cursor() as cursor: sql = """INSERT INTO employe (id_utilisateur, id_role) VALUES (%s, %s)""" cursor.execute(sql, (user_id, role_id)) connexion.commit()
def _get_ingredient_infos(self): with SQLconnexion() as connexion: with connexion.cursor() as cursor: sql = """SELECT id, quantite_globale FROM ingredient""" cursor.execute(sql) ingredient_infos = cursor.fetchall() return ingredient_infos
def get_last_user_id_injected(self): with SQLconnexion() as connexion: with connexion.cursor() as cursor: sql = """SELECT id FROM utilisateur ORDER BY id DESC LIMIT 1""" cursor.execute(sql) user_id = cursor.fetchone() return user_id["id"]
def get_role_id(self, role_name): with SQLconnexion() as connexion: with connexion.cursor() as cursor: sql = """SELECT id FROM role WHERE nom = %s""" cursor.execute(sql, (role_name)) role = cursor.fetchone() return role["id"]
def _get_payment_status_ids(self): with SQLconnexion() as connexion: with connexion.cursor() as cursor: sql = """SELECT id FROM statut_paiement""" cursor.execute(sql) payment_status_ids = cursor.fetchall() return payment_status_ids
def set_recipes(self, number): words_list = [ "recette", "pizza", "four", "sauce tomate", "fromage", "origan", "enfourner", "ajouter le jambon", "cuire", "étaler la pate", "croquante", "saler", "poivrer" ] with SQLconnexion() as connexion: numb_recipes = 0 while numb_recipes < number: recipe = { "recipe": "La recette de " + self.fake_data.random_element(elements=self.pizzas), "description": self.fake_data.sentence(nb_words=30, ext_word_list=words_list) } with connexion.cursor() as cursor: sql = """INSERT INTO recette (recette, description) VALUES (%s, %s)""" cursor.execute(sql, (recipe["recipe"], recipe["description"])) connexion.commit() numb_recipes += 1
def get_last_adress_id_injected(self): with SQLconnexion() as connexion: with connexion.cursor() as cursor: sql = """SELECT id FROM adresse ORDER BY id DESC LIMIT 1""" cursor.execute(sql) address_id = cursor.fetchone() return address_id["id"]
def _get_ingredients_quantity_per_restaurant(self): with SQLconnexion() as connexion: with connexion.cursor() as cursor: sql = """SELECT * FROM stock_ingredient_par_restaurant""" cursor.execute(sql) result = cursor.fetchall() return result
def _get_pizza_ids(self): with SQLconnexion() as connexion: with connexion.cursor() as cursor: sql = """SELECT id FROM pizza""" cursor.execute(sql) pizza_ids = cursor.fetchall() return pizza_ids
def _get_restaurant_ids(self): with SQLconnexion() as connexion: with connexion.cursor() as cursor: sql = """SELECT id FROM restaurant""" cursor.execute(sql) restaurant_ids = cursor.fetchall() return restaurant_ids
def set_categories(self): with SQLconnexion() as connexion: for category in self.categories: category_info = { "id": "", "name": category, "sub_categories": random.sample(self.categories, random.randint(2, 4)) } with connexion.cursor() as cursor: sql = """INSERT INTO categorie_pizza (nom_categorie) VALUES (%s)""" cursor.execute(sql, (category_info["name"])) connexion.commit() with connexion.cursor() as cursor: sql = """SELECT id FROM categorie_pizza WHERE nom_categorie = %s""" cursor.execute(sql, (category_info["name"])) result = cursor.fetchone() category_info["id"] = result["id"] for sub_category in category_info["sub_categories"]: with connexion.cursor() as cursor: sql = """INSERT INTO sous_cat_par_cat (id_cat_parent, id_cat_enfant) SELECT %s, id AS id_enfant FROM categorie_pizza WHERE nom_categorie = %s""" cursor.execute(sql, (category_info['id'], sub_category)) connexion.commit()
def _get_pizza_informations(self): with SQLconnexion() as connexion: with connexion.cursor() as cursor: sql = """SELECT id, prix_unitaire FROM pizza""" cursor.execute(sql) pizza_infos = cursor.fetchall() return pizza_infos
def _get_address_ids(self): with SQLconnexion() as connexion: with connexion.cursor() as cursor: sql = """SELECT id FROM adresse""" cursor.execute(sql) address_ids = cursor.fetchall() return address_ids
def _get_payment_method_ids(self): with SQLconnexion() as connexion: with connexion.cursor() as cursor: sql = """SELECT id FROM mode_reglement""" cursor.execute(sql) payment_method_ids = cursor.fetchall() return payment_method_ids
def _get_10_orders(self): with SQLconnexion() as connexion: with connexion.cursor() as cursor: sql = """SELECT id_commande, date_debut_operation FROM evolution_preparation ORDER BY RAND() LIMIT 10""" cursor.execute(sql) orders = cursor.fetchall() return orders
def set_delivery_types(self): with SQLconnexion() as connexion: for delivery_type in self.delivery_types: with connexion.cursor() as cursor: sql = """INSERT INTO type_livraison (type_livraison) VALUES (%s)""" cursor.execute(sql, (delivery_type)) connexion.commit()
def _get_category_ids_association(self): with SQLconnexion() as connexion: with connexion.cursor() as cursor: sql = """SELECT id_cat_parent, id_cat_enfant FROM sous_cat_par_cat""" cursor.execute(sql) category_associations = cursor.fetchall() return category_associations
def set_order_status(self): with SQLconnexion() as connexion: for status in self.order_status: with connexion.cursor() as cursor: sql = """INSERT INTO statut_commande (statut_commande) VALUES (%s)""" cursor.execute(sql, (status)) connexion.commit()
def _get_ingredient_ids(self): with SQLconnexion() as connexion: with connexion.cursor() as cursor: sql = """SELECT id FROM ingredient""" cursor.execute(sql) ingredient_ids = cursor.fetchall() return ingredient_ids
def set_payment_status(self): with SQLconnexion() as connexion: for status in self.payment_status: with connexion.cursor() as cursor: sql = """INSERT INTO statut_paiement (statut_paiement) VALUES (%s)""" cursor.execute(sql, (status)) connexion.commit()
def set_payment_methods(self): with SQLconnexion() as connexion: for payment_method in self.payment_methods: with connexion.cursor() as cursor: sql = """INSERT INTO mode_reglement (mode_reglement) VALUES (%s)""" cursor.execute(sql, (payment_method)) connexion.commit()
def set_one_customer(self, user_id, delivery_address_id, invoice_address_id): with SQLconnexion() as connexion: with connexion.cursor() as cursor: sql = """INSERT INTO client (id_utilisateur, id_adresse_livraison, id_adresse_facturation) VALUES (%s, %s, %s )""" cursor.execute( sql, (user_id, delivery_address_id, invoice_address_id)) connexion.commit()
def set_user(self, user_elements): with SQLconnexion() as connexion: with connexion.cursor() as cursor: sql = """INSERT INTO utilisateur (nom, prenom, mail, password, date_creation) VALUES (%s, %s, %s, %s, %s)""" cursor.execute( sql, (user_elements["name"], user_elements["first_name"], user_elements["mail"], user_elements["password"], user_elements["creation_date"])) connexion.commit()
def set_role(self): with SQLconnexion() as connexion: with connexion.cursor() as cursor: for role in self.roles: sql = """SELECT nom FROM role WHERE nom = %s""" cursor.execute(sql, (role)) result = cursor.fetchone() if not result: sql = "INSERT INTO role (nom) VALUES (%s)" cursor.execute(sql, (role)) connexion.commit()
def set_address(self, address_elements): with SQLconnexion() as connexion: with connexion.cursor() as cursor: sql = """INSERT INTO adresse (destinataire, voie, code_postal, ville, renseignement_supplementaire, telephone, numero_voie) VALUES (%s, %s, %s, %s, %s, %s, %s)""" cursor.execute( sql, (address_elements["person"], address_elements["street"], address_elements["zip_code"], address_elements["city"], address_elements["info_bonus"], address_elements["phone_number"], address_elements["street_number"])) connexion.commit()
def set_ingredients(self): with SQLconnexion() as connexion: for ingredient in self.ingredients: ingredient_info = { "name": ingredient, "price_per_unit": round(random.uniform(1.0, 15.0), 2), "global_quantity": random.randint(1, 50) } with connexion.cursor() as cursor: sql = """INSERT INTO ingredient (nom, prix_unitaire, quantite_globale) VALUES (%s, %s, %s)""" cursor.execute(sql, (ingredient_info["name"], ingredient_info["price_per_unit"], ingredient_info["global_quantity"])) connexion.commit()
def set_pizza(self): # We get information already inserted ingredient_ids = self._get_ingredient_ids() recipe_ids = self._get_recipe_ids() category_associations = self._get_category_ids_association() # For each pizza in the list, we insert all the information for pizza in self.pizzas: ## WE SET UP THE NECESSARY DATAS FOR EACH PIZZA ## pizza_info = { "id": "", "name": pizza, "description": self.fake_data.sentence(15), "price_per_unit": round(random.uniform(9.90, 16.90), 2), "recipe_id": recipe_ids.pop(), # get a {'id': X} "ingredient_ids": random.sample(ingredient_ids, 3), # get a ({'id': X}, {'id': X}, {'id': X}) "category_associations": random.sample(category_associations, 2) } # We define a simple list of ingedient ids from the sample simple_ingredient_ids = [ ingredient['id'] for ingredient in pizza_info["ingredient_ids"] ] # We defin a coherent and simple list of category_ids all_category_ids = [category['id_cat_parent'] for category in pizza_info["category_associations"]] + \ [category['id_cat_enfant'] for category in pizza_info["category_associations"]] simple_category_ids = list(set(all_category_ids)) ## WE START THE INSERTION ## with SQLconnexion() as connexion: # We had basic pizza info into pizza Table with connexion.cursor() as cursor: sql = """INSERT INTO pizza (nom, description, prix_unitaire, id_recette) VALUES (%s, %s, %s, %s)""" cursor.execute( sql, (pizza_info["name"], pizza_info["description"], pizza_info["price_per_unit"], pizza_info['recipe_id']["id"])) connexion.commit() # We get the pizza id we just created thanks to th insertion with connexion.cursor() as cursor: sql = """SELECT id FROM pizza ORDER BY id DESC LIMIT 1""" cursor.execute(sql) result = cursor.fetchone() pizza_info["id"] = result["id"] # For each category ids, we insert the pizza_id and category_id associations for category_id in simple_category_ids: with connexion.cursor() as cursor: sql = """INSERT INTO categorie_par_pizza (id_pizza, id_categorie) VALUES (%s, %s)""" cursor.execute(sql, (pizza_info["id"], category_id)) connexion.commit() # For each ingredients_ids, we link ingredients to the pizza and set up arbitrary the global_quantity for ingredient_id in simple_ingredient_ids: necessary_quantity = round(random.random(), 2) with connexion.cursor() as cursor: sql = """INSERT INTO quantite_ingredient_par_pizza (id_ingredient, id_pizza, quantite_necessaire) VALUES (%s, %s, %s)""" cursor.execute(sql, (ingredient_id, pizza_info["id"], necessary_quantity)) connexion.commit()