def get_products_search_sql(values): #get unique brands from mysql, use python to see if contains substring. how to do directly in sql? query = "select distinct(brand) from products" data = engine.execute(query).fetchall() data = [list(row) for j, row in enumerate(data)] unique_brands = [j.upper() for i in data for j in i] results = [] for i in unique_brands: if any(word.upper() in i for word in values): results.append(i) if len(results) != 0: #check to prevent crash if bad search #generating query... query2 = "select * from products where (" count = 0 for i in results: count += 1 query2 += "brand" query2 += " = " query2 += f'"{i}"' if count < len(results): query2 += " or " query2 += ")" data2 = engine.execute(query2).fetchall() data2 = [dict(row) for j, row in enumerate(data2)] else: data2 = [] return data2
def get_products_ids_sql(ids): #generating query query = "select * from products where (" counter = 0 for i in ids: counter += 1 query += "id" query += " = " query += str(i) if counter < len(ids): query += " or " query += ")" data51 = engine.execute(query).fetchall() #converting to legacy utilities output data51 = [dict(row) for j, row in enumerate(data51)] #generating multiplies dict for summing price of type {product_id: amount_ordered} like {1240:4, 12:3} would be 4 items of id 1240 and 3 items of id 12. multis = {} for index, item in enumerate(ids): multis[index] = item multis = dict(Counter(multis.values())) return data51, multis
def get_categories_sql(): gender_query = 'select distinct(gender) from products' # Queries the unique genders from DB gender_objects = engine.execute(gender_query).fetchall() gender_list = [i.values()[0] for i in gender_objects] product_query = [f"select distinct(type) from products where gender = '{g}'" for g in gender_list] product_objects = [engine.execute(i).fetchall() for i in product_query] result = [] for i, gender in enumerate(gender_list): gender_dict = {} gender_dict['title'] = gender gender_dict['children'] = [{'url': '', 'name': f'{category[0]}'} for category in product_objects[i]] result.append(gender_dict) return result
def get_subcategories_sql(gender, category): query = f"select distinct(subtype) from products where gender = '{gender}' and `type` = '{category}'" subtype_objects = engine.execute(query).fetchall() subtype_data = [i[0] for i in subtype_objects] result = [{'gender': gender, 'category': category, 'children': [{'url': '', 'name': subtype} for subtype in subtype_data]}] return result
def get_products_filtered_sql(categories = None): if categories is not None: #generating query query = "select * from products where (" count = 0 for i, j in zip(categories.keys(), categories.values()): count += 1 query += i query += " = " query += f'"{j}"' if count < len(categories.keys()): query += " and " query += ")" else: query = "select * from products" data = engine.execute(query).fetchall() #converting to legacy utilities output data = [dict(row) for j, row in enumerate(data)] return data
def write_order_sql(inf): query = f"select * from customers" data = engine.execute(query).fetchall() data = [dict(row) for j, row in enumerate(data)] first_name, last_name = inf["name"].split() kund = {"firstname": first_name, "lastname": last_name, "street": inf["address"], "city": inf["town"], "zipcode": int(inf["zipcode"])} kund_inf = [i for i in kund.values()] storage = [list(i.values()) for i in data] for index, item in enumerate(storage): storage[index] = item[1:] if kund_inf not in storage: query2 = f'insert into customers (firstname, lastname, street, city, zipcode) values ("{kund["firstname"]}", "{kund["lastname"]}", "{kund["street"]}", "{kund["city"]}", {kund["zipcode"]})' engine.execute(query2) cust_id = len(storage) + 1 else: cust_id = storage.index(kund_inf) + 1 bought = ast.literal_eval(inf["items"]) multis = {} for index, item in enumerate(bought): multis[index] = item multis = dict(Counter(multis.values())) ordernum = engine.execute("select max(orderid) from orders").fetchall() ordernum = ordernum[0][0] + 1 for i in multis.keys(): query9 = f'insert into orders (customer_id, orderid, id, amount) values ({cust_id}, {ordernum}, {i}, {multis[i]})' engine.execute(query9) #python3 -m http.server --cgi 8000
def get_20_most_popular_sql(): query = "select products.id, products.brand, products.type, products.subtype, products.color, products.gender, products.price, products.size from products join orders on products.id = orders.id order by orders.amount desc limit 20" data = engine.execute(query).fetchall() data = [dict(row) for j, row in enumerate(data)] return data