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")