def get_product_data(prod_id): product_list = "" try: with connection.cursor(pymysql.cursors.DictCursor) as cursor: sql = """SELECT Products.Product_name, Products.Product_id, Products.Image, Products.Description, Products.Age, Products.Product_condition, Users.Username, Users.User_email, Users.Address FROM Products INNER JOIN Users ON Users.Product_id = Products.Product_id WHERE Products.Product_id = {} and Availability = 1 ;""".format( prod_id) cursor.execute(sql) for items in cursor: print(items, "itemsssss") product_list = items return product_list finally: # connection.close() print("Sure")
def get_last_user(): try: with connection.cursor(pymysql.cursors.DictCursor) as cursor: sql = """SELECT User_id FROM Users ORDER BY User_id DESC LIMIT 1""" cursor.execute(sql) for item in cursor: user_id = item["User_id"] finally: return user_id
def get_last_product(): try: with connection.cursor(pymysql.cursors.DictCursor) as cursor: sql = """SELECT Product_id FROM Products ORDER BY Product_id DESC LIMIT 1""" cursor.execute(sql) for item in cursor: product_id = item["Product_id"] finally: return product_id
def update_availability(prod_id): try: with connection.cursor() as cursor: sql = """ UPDATE Products SET Availability = 0 WHERE Product_id = {} """.format(prod_id) cursor.execute(sql) connection.commit() finally: return True
def add_post(User_id, prod_id): try: with connection.cursor() as cursor: sql = """ INSERT INTO Posts (User_id, Product_id) VALUES ('{}', '{}');""".format(User_id, prod_id) cursor.execute(sql) connection.commit() finally: # connection.close() return "yes"
def add_user(username, user_email, address, prod_id): try: with connection.cursor() as cursor: sql = """ INSERT INTO Users (Username, User_email , address, User_type , Product_id) VALUES ('{}' ,'{}' , '{}', "Donor", '{}');""".format( username, user_email, address, prod_id) cursor.execute(sql) connection.commit() finally: return True
def save_customer_info(username, user_email, prod_id): try: with connection.cursor() as cursor: sql = """ INSERT INTO Users (Username, User_email, User_type , Product_id) VALUES ('{}' , '{}', "Customer", '{}');""".format( username, user_email, prod_id) cursor.execute(sql) connection.commit() finally: return True
def add_product(name, cat_id, condition, age, descrption, img): try: with connection.cursor() as cursor: sql = """ INSERT INTO Products (Product_name, Image, Description, Age, Product_condition, Category_id, Availability) VALUES ('{}','{}','{}','{}','{}',{},true );""".format( name.capitalize(), img, descrption, age, condition, cat_id) cursor.execute(sql) connection.commit() finally: # connection.close() return "OK"
def get_category_data(category_id): try: with connection.cursor(pymysql.cursors.DictCursor) as cursor: sql = """SELECT Products.Product_name, Products.Product_id, Products.Image, Products.Description, Products.Age, Products.Product_condition, Users.Username, Users.Address, Posts.Date FROM Products INNER JOIN Users ON Users.Product_id = Products.Product_id INNER JOIN Posts ON Posts.Product_id = Products.Product_id WHERE Products.Category_id = {} and Availability = 1 and Users.User_type = "Donor";""".format( category_id) cursor.execute(sql) return cursor finally: print("yes")
def get_recent_product(): try: with connection.cursor(pymysql.cursors.DictCursor) as cursor: sql = """SELECT Products.Product_name, Products.Product_id, Products.Image, Products.Description, Products.Age, Products.Product_condition, Products.Category_id, Users.Username, Users.Address, Posts.Date FROM Products INNER JOIN Users ON Users.Product_id = Products.Product_id INNER JOIN Posts ON Posts.Product_id = Products.Product_id WHERE Availability = 1 and Users.User_type = "Donor" ORDER BY Product_id DESC LIMIT 6;""" cursor.execute(sql) return cursor finally: print("yes")