def add_products(data_xls): invalid_row = {} connection = db_connection.get_connection() try: with connection.cursor() as cursor: sql = "INSERT INTO PRODUCT(`Product_id`,`title`,`description`,`price`,`category`,`occasion`,`image`,`model_id`) VALUES(DEFAULT,%s,%s,%s,%s,%s,%s,%s)" for i, row in data_xls.iterrows(): img1 = "No File" img2 = "No file" img3 = "No File" with open(tuple(row)[5], "rb") as image_file: img1 = utils.image_encoding(image_file) with open(tuple(row)[6], "rb") as image_file: img2 = utils.image_encoding(image_file) with open(tuple(row)[7], "rb") as image_file: img3 = utils.image_encoding(image_file) if img1 == "No File": Exception("Please specify correct path") valid, message = is_valid_product(row) #print(i, message) if valid: # print(row[i]) row['product_image'] = img1 modal_id = add_modal(img3, img2) product = tuple(row[:6], ) + (modal_id, ) cursor.execute(sql, product) connection.commit() else: invalid_row[i] = message print(invalid_row[i]) finally: connection.close() cursor.close() return invalid_row
def add_to_cart(product_id, image, quantity, wood_id, pattern_id, user_name, total_cost): """Creates a new order in the cart Arguments: product_id: product id image: customized image quantity: number of items wood_id: selected wood type pattern_id: selected wood pattern user_name: name of the user total_cost: cost of the final order """ connection = db_connection.get_connection() try: with connection.cursor() as cursor: order_date = datetime.today().strftime('%Y-%m-%d') sql = "INSERT INTO CUSTOMER_ORDER(`product_id`,`user_id`,`email_id`,`woodtype_id`,`woodpattern_id`," \ "`total_cost`,`state`,`order_date`,`quantity`,`Order_Id`,`image`) VALUES(%s, null, %s, %s, %s, %s," \ "'In Cart', %s, %s, null, %s) " cart_details = (product_id, user_name, wood_id, pattern_id, total_cost, order_date, quantity, image) result = cursor.execute(sql, cart_details) order_id = cursor.lastrowid connection.commit() return result, order_id except Exception as e: print(e) finally: connection.close() cursor.close()
def get_product_details_cart(product_id): connection = db_connection.get_connection() try: with connection.cursor() as cursor: sql = "SELECT Product_id, title, price from PRODUCT where `Product_id`=%s" cursor.execute(sql, product_id) result = cursor.fetchone() finally: connection.close() cursor.close() return result
def place_order(user_name, price, address, zipcode, card_number, expiry, cvv, contact): """Place an order Arguments: user_name: name of the user price: total payment amount address: shipping address zipcode: zipcode card_number: encrypted card number expiry: expiry date on the card cvv: encrypted cvv contact: contact numnber Returns: result: details of the order order_id: new id for the order """ connection = db_connection.get_connection() try: with connection.cursor() as cursor: sql = "INSERT INTO ORDERS(`Address`,`Pincode`,`Contact_No`,`Card_No`,`cvv`,`expiry_date`,`total_price`) " \ "VALUES(%s, %s, %s, %s, %s, %s, %s) " cart_details = (address, zipcode, contact, card_number, cvv, expiry, price) result = cursor.execute(sql, cart_details) connection.commit() order_id = cursor.lastrowid sql1 = "UPDATE CUSTOMER_ORDER SET `Order_Id`=%s where `email_id`=%s AND `state`=%s" id_update = (order_id, user_name, 'In Cart') cursor.execute(sql1, id_update) connection.commit() sql2 = "UPDATE CUSTOMER_ORDER SET `state`=%s where `Order_Id`=%s" order_update = ('Order Received', order_id) cursor.execute(sql2, order_update) connection.commit() order_date = datetime.today().strftime('%Y-%m-%d') sql2 = "UPDATE CUSTOMER_ORDER SET `order_date`=%s where `Order_Id`=%s" date_update = (order_date, order_id) cursor.execute(sql2, date_update) connection.commit() except Exception as e: print(e) finally: connection.close() cursor.close() return result, order_id
def get_design(): connection = db_connection.get_connection() try: with connection.cursor() as cursor: sql = "SELECT * from WOOD_PATTERN ORDER BY ID DESC;" cursor.execute(sql) result = cursor.fetchall() finally: connection.close() cursor.close() return result
def remove(product_id): connection = db_connection.get_connection() try: with connection.cursor() as cursor: sql = "DELETE FROM PRODUCT where `Product_id`=%s" cursor.execute(sql, product_id) connection.commit() except Exception as e: print(e) finally: connection.close() cursor.close()
def get_design_by_id(design_id): connection = db_connection.get_connection() try: with connection.cursor() as cursor: sql = "SELECT * from WOOD_PATTERN where `ID`=%s" cursor.execute(sql, design_id) result = cursor.fetchone() finally: connection.close() cursor.close() return result
def get_wood(): connection = db_connection.get_connection() try: with connection.cursor() as cursor: sql = "SELECT * from WOOD_TYPE" cursor.execute(sql) result = cursor.fetchall() finally: connection.close() cursor.close() return result
def get_products(): connection = db_connection.get_connection() try: with connection.cursor() as cursor: sql = "SELECT * from PRODUCT" cursor.execute(sql) result = cursor.fetchall() finally: connection.close() cursor.close() return result
def get_products_by_category(category): connection = db_connection.get_connection() try: with connection.cursor() as cursor: sql = "SELECT * from PRODUCT where `category`=%s" cursor.execute(sql, category) result = cursor.fetchall() finally: connection.close() cursor.close() return result
def get_products_mask(model_id): connection = db_connection.get_connection() try: with connection.cursor() as cursor: sql = "SELECT * from MODAL where `ID`=%s" cursor.execute(sql, model_id) result = cursor.fetchall() finally: connection.close() cursor.close() return result
def get_products_by_occasion(occasion): connection = db_connection.get_connection() try: with connection.cursor() as cursor: sql = "SELECT * from PRODUCT where occasion like %s" cursor.execute(sql, '%' + occasion + '%') result = cursor.fetchall() finally: connection.close() cursor.close() return result
def search_product_by_name(name): connection = db_connection.get_connection() try: with connection.cursor() as cursor: sql = "SELECT * from PRODUCT where `title`=%s" cursor.execute(sql, name) result = cursor.fetchall() finally: connection.close() cursor.close() return result
def get_wood_by_id(wood_id): connection = db_connection.get_connection() try: with connection.cursor() as cursor: sql = "SELECT * from WOOD_TYPE where `ID`=%s" cursor.execute(sql, wood_id) result = cursor.fetchone() finally: connection.close() cursor.close() return result
def edit(product_id, title, description, price): connection = db_connection.get_connection() try: with connection.cursor() as cursor: sql = "UPDATE PRODUCT SET `title` = %s,`description` = %s,`price` = %s where `Product_id`=%s" prod = (title, description, price, product_id) cursor.execute(sql, prod) connection.commit() except Exception as e: print(e) finally: connection.close() cursor.close()
def login(email, password): connection = db_connection.get_connection() try: with connection.cursor() as cursor: sql = "SELECT * from USER where `Email`=%s AND `Password`=%s" login_details = (email, password) cursor.execute(sql, login_details) result = cursor.fetchone() finally: connection.close() cursor.close() return result
def add_modal(modal_mask, image_mask): connection = db_connection.get_connection() try: with connection.cursor() as cursor: sql = "INSERT INTO MODAL VALUES(DEFAULT,%s,%s)" mask = (modal_mask, image_mask) cursor.execute(sql, mask) connection.commit() return cursor.lastrowid except Exception as e: print(e) finally: connection.close() cursor.close()
def signup(lastname, firstname, email, password): connection = db_connection.get_connection() try: with connection.cursor() as cursor: sql = "INSERT INTO USER VALUES(DEFAULT,%s,%s,%s,%s,'Customer')" signup_details = (lastname, firstname, email, password) result = cursor.execute(sql, signup_details) user_id = cursor.lastrowid connection.commit() return result, user_id except Exception as e: print(e) finally: connection.close() cursor.close()
def get_all_orders(): """Gets all the active orders Returns: result: collection of all the active orders """ connection = db_connection.get_connection() try: with connection.cursor() as cursor: sql = "SELECT * from `CUSTOMER_ORDER` where `state`<>%s" cursor.execute(sql, 'In Cart') result = cursor.fetchall() finally: connection.close() cursor.close() return result
def get_order_details_by_id(order_id): """Get all the order deatils by order id Arguments: order_id: order id Returns: result: details of the order """ connection = db_connection.get_connection() try: with connection.cursor() as cursor: sql = "SELECT * from `CUSTOMER_ORDER` where `ID`=%s" cursor.execute(sql, order_id) result = cursor.fetchone() finally: connection.close() cursor.close() return result
def load_cart(user_name): """Load products in the cart Arguments: user_name: name of the user Returns: result: list of products in the cart """ connection = db_connection.get_connection() try: with connection.cursor() as cursor: sql = "SELECT * from CUSTOMER_ORDER where `email_id`=%s AND `state`=%s" cart_details = (user_name, 'In Cart') cursor.execute(sql, cart_details) result = cursor.fetchall() finally: connection.close() cursor.close() return result
def get_order_details_for_user(user_name): """Gets all the orders for a particular user Arguments: user_name: name of the user Returns: list: collection of all the orders for a user """ connection = db_connection.get_connection() try: with connection.cursor() as cursor: sql = "SELECT * from CUSTOMER_ORDER where `email_id`=%s AND `state`<>%s" cart_details = (user_name, 'In Cart') cursor.execute(sql, cart_details) result = cursor.fetchall() finally: connection.close() cursor.close() return result
def update_order_status_for_order(order_status, order_id): """Update the order status Arguments: order_id: order id order_status: new order status Returns: result: updated order with updated status """ connection = db_connection.get_connection() try: with connection.cursor() as cursor: sql = "UPDATE `CUSTOMER_ORDER` SET `state` = %s where `ID` = %s" result = cursor.execute(sql, (order_status, order_id)) connection.commit() finally: connection.close() cursor.close() return result
def remove_cart(product_id): """Remove the product from cart Arguments: product_id: product id Returns: result: 1 if the product was removed successfully else 0 """ connection = db_connection.get_connection() try: with connection.cursor() as cursor: sql = "DELETE from CUSTOMER_ORDER where `ID`=%s" result = cursor.execute(sql, product_id) connection.commit() except Exception as e: print(e) finally: connection.close() cursor.close() return result
def tearDown_for_signup(self, user_id): connection = db_connection.get_connection() with connection.cursor() as cursor: sql = "DELETE from USER where `ID`=%s" cursor.execute(sql, user_id) connection.commit()