def get_categories(): cursor = conn.cursor() cursor.execute("""select * from category""") records = cursor.fetchall() res = [] for r in sorted(records, key=lambda x: len(x[0])): res.append({"id": r[0], "name": r[1]}) return res
def remove_product(id): cursor = conn.cursor() cursor.execute( """delete from list_brand_product where prod_id = %(prod_id)s""", {"prod_id": id}) cursor.execute("""delete from product where prod_id = %(prod_id)s""", {"prod_id": id}) conn.commit()
def add_category(data): cursor = conn.cursor() cursor.callproc('add_category', [ len(data["parent"].split(".")) + 1, data["name"], data["parent"], ]) conn.commit()
def update_category(data): cursor = conn.cursor() cursor.execute( """update category set cat_name = %(cat_name)s where cat_num = %(cat_num)s""", { "cat_name": data["name"], "cat_num": data["id"] }) conn.commit()
def update_shop_info(data): cursor = conn.cursor() cursor.execute( """update shop set name = %(name)s, phone = %(phone)s, address = %(address)s""", { "name": data["name"], "phone": data["phone"], "address": data["address"] }) conn.commit()
def get_node_categories(): cursor = conn.cursor() cursor.execute( """select category.cat_num cat_num, category.cat_name from category where cat_num not in (select category.cat_num from product, category where product.cat_num = category.cat_num)""" ) records = cursor.fetchall() res = [] for r in sorted(records, key=lambda x: len(x[0])): res.append({"id": r[0], "name": r[1]}) return res
def update_brand(data): cursor = conn.cursor() cursor.execute( """update brand set br_name = %(br_name)s, br_description = %(desc)s, img_url = %(img)s where br_id = %(br_id)s""", { "br_id": data["id"], "br_name": data["name"], "desc": data["description"], "img": data["img_url"] }) conn.commit()
def get_brand(id): cursor = conn.cursor() cursor.execute("""select * from brand where br_id = %(id)s""", {"id": id}) records = cursor.fetchall() res = { "id": records[0][0], "name": records[0][1], "description": records[0][2], "img_url": records[0][3] } return res
def remove_category(id): cursor = conn.cursor() cursor.execute("""delete from category where cat_num = %(cat_num)s;""", {"cat_num": id}) cursor.execute( """delete from list_brand_product where prod_id IN (select prod_id from product where cat_num = %(cat_num)s);""", {"cat_num": id}) cursor.execute("""delete from product where cat_num = %(cat_num)s;""", {"cat_num": id}) conn.commit()
def get_category_path(cat_num): cursor = conn.cursor() res = "" while cat_num != "": cursor.execute( """select cat_name from category where cat_num = %(cat_num)s""", {"cat_num": cat_num}) records = cursor.fetchall() res = records[0][0] + "/" + res cat_num = cat_num[:cat_num.rfind(".")] return res[:-1]
def get_leaf_categories(): cursor = conn.cursor() cursor.execute( """select cat_num, cat_name from category where cat_num not in (select distinct f.cat_num from category f, category f1 where f1.cat_num like f.cat_num || '.' || '%');""" ) records = cursor.fetchall() res = [] for r in sorted(records, key=lambda x: len(x[0])): res.append({"id": r[0], "name": r[1]}) return res
def get_category_info(cat_num): cursor = conn.cursor() cursor.execute( """select avg(price) from product where cat_num = %(cat_num)s""", {"cat_num": cat_num}) records = cursor.fetchall() if records[0][0]: res = {"avg_price": round(float(records[0][0]), 2)} else: res = {"avg_price": 0} return res
def get_shop_info(): cursor = conn.cursor() cursor.execute("""select * from shop""") records = cursor.fetchall() res = {"name": "None", "phone": "None", "address": "None"} if records: res = { "name": records[0][0], "phone": records[0][1], "address": records[0][2] } return res
def add_brand(data): cursor = conn.cursor() cursor.execute("""select max(br_id) from brand""") br_id = cursor.fetchall()[0][0] + 1 cursor.execute( """insert into brand (br_id, br_name, br_description, img_url) values (%(br_id)s, %(br_name)s, %(desc)s, %(img)s)""", { "br_id": br_id, "br_name": data["name"], "desc": data["description"], "img": data["img_url"] }) conn.commit()
def get_brands(): cursor = conn.cursor() cursor.execute("""select * from brand""") records = cursor.fetchall() res = [] for r in records: res.append({ "id": r[0], "name": r[1], "description": r[2], "img_url": r[3] }) return res
def get_filter_values(cat_num): cursor = conn.cursor() cursor.execute( """select min(price), max(price), min(warranty_period), max(warranty_period) from product where cat_num = %(cat_num)s""", {"cat_num": cat_num}) records = cursor.fetchall() res = {"minPrice": 0, "maxPrice": 0, "minWarranty": 0, "maxWarranty": 0} if records: res = { "minPrice": records[0][0], "maxPrice": records[0][1], "minWarranty": records[0][2], "maxWarranty": records[0][3] } return res
def get_high_priced_products(params): cursor = conn.cursor() cursor.execute( """select * from product where cat_num = %(category)s order by price desc limit %(n)s;""", params) records = cursor.fetchall() res = [] for r in records: res.append({ "id": r[0], "name": r[1], "price": r[2], "installment_plan": r[3], "warranty_period": r[4], "img_url": r[5], "cat_num": r[5] }) return res
def get_products_by_brand(br_id): cursor = conn.cursor() cursor.execute( """select * from product where prod_id in (select prod_id from list_brand_product where br_id = %(br_id)s) """, {"br_id": br_id}) records = cursor.fetchall() res = [] for r in records: res.append({ "id": r[0], "name": r[1], "price": r[2], "installment_plan": r[3], "warranty_period": r[4], "img_url": r[5], "description": r[6], "cat_num": r[7] }) return res
def get_products(cat_num): cursor = conn.cursor() cursor.execute( """select * from product where cat_num = %(cat_num)s order by price desc""", {"cat_num": cat_num}) records = cursor.fetchall() res = [] for r in records: res.append({ "id": r[0], "name": r[1], "price": r[2], "installment_plan": r[3], "warranty_period": r[4], "img_url": r[5], "description": r[6], "cat_num": r[7] }) return res
def get_filtered_products(params): cursor = conn.cursor() cursor.execute( """select * from product where cat_num = %(category)s and price >= %(minPrice)s and price <= %(maxPrice)s and warranty_period >= %(minWarranty)s and warranty_period <= %(maxWarranty)s and installment_plan = %(installment)s order by price desc""", params) records = cursor.fetchall() res = [] for r in records: res.append({ "id": r[0], "name": r[1], "price": r[2], "installment_plan": r[3], "warranty_period": r[4], "img_url": r[5], "cat_num": r[5] }) return res
def get_all_products(): cursor = conn.cursor() cursor.execute( """select p.prod_id, p.prod_name, p.price, p.installment_plan, p.warranty_period, p.img_url, p.description, p.cat_num, b.br_id from product p, list_brand_product l, brand b where p.prod_id = l.prod_id and l.br_id = b.br_id;""") records = cursor.fetchall() res = [] for r in records: res.append({ "id": r[0], "name": r[1], "price": r[2], "installment_plan": r[3], "warranty_period": r[4], "img_url": r[5], "description": r[6], "cat_num": r[7], "br_id": r[8] }) return res
def get_product_info(id): cursor = conn.cursor() cursor.execute( """select br_name from brand where br_id in (select br_id from list_brand_product where prod_id = %(id)s) """, {"id": id}) records = cursor.fetchall() brand_name = records[0][0] cursor.execute("""select * from product where prod_id = %(id)s""", {"id": id}) records = cursor.fetchall() res = { "id": records[0][0], "name": records[0][1], "price": records[0][2], "installment_plan": records[0][3], "warranty_period": records[0][4], "img_url": records[0][5], "description": records[0][6], "cat_num": records[0][7], "brand": brand_name } return res
def add_product(data): cursor = conn.cursor() cursor.execute("""select max(prod_id) from product""") prod_id = cursor.fetchall()[0][0] + 1 cursor.execute( """insert into product (prod_id, prod_name, price, installment_plan, warranty_period, cat_num, img_url, description) values (%(id)s, %(name)s, %(price)s, %(plan)s, %(period)s, %(num)s, %(image)s, %(desc)s)""", { "id": prod_id, "name": data["name"], "price": data["price"], "plan": data["installment_plan"], "period": data["warranty_period"], "num": data["category"], "image": data["img_url"], "desc": data["description"], }) cursor.execute( """insert into list_brand_product (br_id, prod_id) values(%(br_id)s, %(prod_id)s);""", { "br_id": data["brand"], "prod_id": prod_id }) conn.commit()
def update_product(data): cursor = conn.cursor() cursor.execute( """update product set prod_name = %(name)s, price = %(price)s, installment_plan = %(plan)s, warranty_period = %(period)s, img_url = %(image)s, description = %(desc)s, cat_num = %(num)s where prod_id = %(id)s""", { "id": data["id"], "name": data["name"], "price": data["price"], "plan": data["installment_plan"], "period": data["warranty_period"], "num": data["category"], "image": data["img_url"], "desc": data["description"] }) cursor.execute("""delete from list_brand_product where prod_id = %(id)s""", {"id": data["id"]}) cursor.execute( """insert into list_brand_product(br_id, prod_id) values(%(br_id)s, %(prod_id)s)""", { "br_id": data["brand"], "prod_id": data["id"] }) conn.commit()
def remove_brand(id): cursor = conn.cursor() cursor.execute("""delete from brand where br_id = %(br_id)s""", {"br_id": id}) conn.commit()
def get_categories(): cursor = conn.cursor() cursor.execute("""select * from category""") records = cursor.fetchall() return sorted(records, key=lambda x: len(x[0]))