def get(self, profileid, count, recommendationtype):
connection = bsql.get_connection("Floris09", "huwebshop")
cursor = bsql.get_cursor(connection)
""" This function represents the handler for GET requests coming in
through the API. It currently returns a random sample of products. """
prodids = []
if recommendationtype == "popular":
cat = profileid
cat = cat.replace("-", " ")
cat = cat.replace(" en ", " & ")
print(cat)
data = bsql.select_data(
cursor,
f"SELECT * FROM andere_kochten_ook WHERE LOWER(category) = LOWER('{cat}');"
)
for row in data:
print(row)
prodids = [row[1], row[2], row[3], row[4]]
print(prodids)
if recommendationtype == "similar":
prod_id = profileid
data = bsql.select_data(
cursor,
f"SELECT * FROM soort_gelijke_producten WHERE productid = '{prod_id}';"
)
for row in data:
print(row)
prodids = [row[1], row[2], row[3], row[4]]
print(prod_id)
if recommendationtype == "behaviour":
data = bsql.select_data(
cursor,
f"SELECT * FROM passend_bij_uw_gedrag WHERE profileid = '{profileid}';"
)
for row in data:
print(row)
prodids = [row[1], row[2], row[3], row[4]]
if recommendationtype == "combination":
print(recommendationtype)
prod_id = profileid
data = bsql.select_data(
cursor,
f"SELECT * FROM combineert_goed_met WHERE productid = '{prod_id}'"
)
for row in data:
print(row)
prodids = [row[1], row[2], row[3], row[4]]
if recommendationtype == "personal":
data = bsql.select_data(
cursor,
f"SELECT * FROM public.persoonlijk_aangeboden WHERE profileid = '{profileid}';"
)
for row in data:
print(row)
prodids = [row[1], row[2], row[3], row[4]]
return prodids, 200
randcursor = database.products.aggregate([{
'$sample': {
'size': count
}
}])
prodids = list(map(lambda x: x['_id'], list(randcursor)))
return prodids, 200
import csv
import thebestsql as bsql
conn = bsql.get_connection("Floris09", "huwebshop")
cur = bsql.get_cursor(conn)
def most_bought():
orders_cats = {}
orders = {}
query = '''
SELECT o.productid, c.category, o.count
FROM orders AS o, categories AS c, products AS p
WHERE o.productid = p.ID
AND p.categoriesid = c.ID
AND o.sessionsid in (
SELECT id
FROM sessions
WHERE session_end BETWEEN (
select session_end
FROM sessions
ORDER BY session_end DESC LIMIT 1) - INTERVAL '30 DAY'
AND
(select session_end
FROM sessions
ORDER BY session_end DESC LIMIT 1))'''
data = bsql.select_data(cur, query)
for row in data:
cat = row[1]
product_id = row[0]
product_count = row[2]
import csv
import thebestsql as bsql
conn = bsql.get_connection("3621", "postgres")
cur = bsql.get_cursor(conn)
def combineert_goed_met():
orders_cats = {}
orders = {}
query_1 = '''
select sessionsid, productid from orders
'''
data_1 = bsql.select_data(cur, query_1)
# query_2 = '''
# select s.id, count(p.id)
# from products as p, orders as o, sessions as s
# where p.id = o.productid
# and o.sessionsid = s.id
# group by s.id
# having
# count(p.id) > 1;
# '''
# data_2 = bsql.select_data(cur, query_2)
counter = 0
list = []
for row in data_1:
counter += 1
if counter % 100 == 0:
print(counter)
ses_id = row[0]