## PRODUCTS DICTIONARY # for eShop # ////////////////////////////////////////////////////////////////////////////// ## LIBRARIES # ////////////////////////////////////////////////////////////////////////////// # import pandas as pd # pandas for excel reading import mysql.connector as mysql # mysql connector import re # regex import json # json import os.path # ... import sys from urllib.request import urlopen ## LOCAL FUNCTIONS # ////////////////////////////////////////////////////////////////////////////// # do-me-INTeger # --- def domeInt(x) : if isinstance(x, str) : # if string return int(x.strip()) if isinstance(x, float) : # if float return round(x) return x # otherwise is int already # do-me-Float # --- def domeFloat(x) : if isinstance(x, str) : return float(x.strip()) else : return x + 0.00 # make sure that result is float ## Clean Text ... # -> removes some general/neutral words and symbols # -> ignores some in-line characters # -> also strips spare spaces # function is applied onto the full title/description # --- def cleanText(x) : ignoreList = '" ( ) [ ]'.split(' ') for r in ignoreList : x = x.replace(r, ' ') x = x.replace(' ', ' ') # remove spare spaces x = x.replace(' ', ' ') x = x.replace(' ', ' ') return x # isSignificant # decides if the term is significant to be indexed; # a term is significant if does not contain digit-chars # --- def isSignificant(x) : # fisrts exclude some notable exceptions (mostly brands) if x in ['7UP', '3ΑΛΦΑ'] : return True return not bool(re.match("\S*\d+\S*", x)) def kbLatinString( txt ) : maTable = txt.maketrans( "ςερτυθιοπασδφγηξκλζχψωβνμΕΡΤΥΘΙΟΠΑΣΔΦΓΗΞΚΛΖΧΨΩΒΝΜάέήίόύώϊϋΆΈΉΊΌΎΏΪΫQWERTYUIOPASDFGHJKLZXCVBNM", "sertyuiopasdfghjklzxcvbnmertyuiopasdfghjklzxcvbnmaehioyviyaehioyviyqwertyuiopasdfghjklzxcvbnm" ) txt = txt.replace('\'', '') txt = txt.replace('-', '') txt = txt.replace(' ', '') return txt.translate(maTable).lower() # set root-keyQ: w (if not exist) # update frequency: f # into list: l # NOTE: in this version, # comparison is based on the *keyboard* format ## --- def rootKey ( w, f, l ) : keyExists = False kbW = kbLatinString(w) for it in l : if it['kb'] == kbW : keyExists = True it['f'] += f if w not in it['alt'] : it['alt'].append(w) if keyExists == False : l.append({ 'w' : w, 'f' : f, 'alt' : [ w ], 'kb' : kbW, 'c' : [] }) # connect keys: a , b # of product with id: i # with frequency: f # into list: l ## --- def connectKeys( a, b, i, f, l ) : kbA = kbLatinString(a) kbB = kbLatinString(b) if kbA == kbB : return False ## exclude just-in-case for it in l : if it['kb'] == kbA : # found: a; # lets update the connection to: b bExists = False for jt in it['c'] : if jt['kb'] == kbLatinString(b) : bExists = True # update the connection's data jt['f'] += f jt['p'].append(i) if bExists == False : # create connection with word: b it['c'].append({ 'w': b, 'kb': kbLatinString(b), 'f': f, 'p': [ i ] }) ## let mysql to return valid strings ## (otherwise it returns strings with missed characters) # credit: https://stackoverflow.com/a/68784172 # analytical credit: https://stackoverflow.com/questions/27566078/ def get_data_from_db(cursor, sql): output = [] cursor.execute(sql) row = cursor.fetchone() while row is not None: row_to_return = row.decode('utf-8') if isinstance(row, bytearray) else row output.append(row_to_return) row = cursor.fetchone() return output ## LOCAL CONSTANTS # ////////////////////////////////////////////////////////////////////////////// _COL = { # -- main info 'freq' : 0, # frequency (based on recent orders) 'pid' : 1, # product id 'brand' : 2, # brand 'barcd' : 3, # barcode 'sklcd' : 4, 'eyscd' : 5, 'descr' : 6, # product description 'sap2' : 7 # SAP category level-2 id } ## PREPADE (build) exception objects # ////////////////////////////////////////////////////////////////////////////// # --- list of words to exclude from keywords # applied in a per-word base (after spliting description to words) removeList = [] removeOriginals = 'μας με σε για του της των από στο στον από & r s ft l τ e g h k m n o p s x'.split(' ') for it in removeOriginals : removeList.append(kbLatinString(it)) """ # --- list of linked-words linkedWords = [] linkedWordOriginals = [ 'Χωρίς-Γλουτένη', 'Χωρίς-Ζάχαρη', 'Χωρίς-Αλάτι', 'Χωρίς-Λακτόζη', 'Χωρίς-Συντηρητικά', 'Χωρίς-Αλκοόλ', 'Υψηλής-Παστερίωσης', 'Ολικής-Άλεσης', 'Χαρτί-Υγείας', 'Χαρτί-Κουζίνας', 'Μπάρες-Δημητριακών', 'Φυσικός-Χυμός' 'Μπαρμα-Στάθης', 'Coca-Cola' 'Aς-Μαγειρέψουμε', 'ΚΡΙΣ-ΚΡΙΣ', 'ΚΡΙ-ΚΡΙ', 'ΕΛ-ΓΚΡΕΚΟ', 'FREE-STEP', 'EL-SABOR', 'LE-PETIT-MARSEILLAIS', 'ΧΡΥΣΑ-ΑΥΓΑ', 'DOUWE-EGBERTS', 'ΕΝ-ΕΛΛΑΔΙ', 'SPIN-SPAN', 'CRETA-FARMS' ] for it in linkedWordOriginals : linkedWords.append(kbLatinString(it)) synonyms = [] synonymOriginals = [ 'μπίρα, μπύρα, μπίρες, μπύρες', 'αυγά, αβγά, αυγό, αβγό', 'σίκαλης, σικάλεως', 'ξηρά, ξερά', 'ρολό, ρολλό' 'coca-cola, cocacola, coke', 'χαρτί-υγείας, ρολό-υγείας, χαρτί-τουαλέτας', 'χαρτί-κουζίνας, ρολό-κουζίνας', 'μπίρα, μπίρες', 'οινος, κρασι', 'ΚΑΤΣΕΛΗΣ, ΚΑΤΣΕΛΗ', 'DR-OETKER, OETKER' ] for it in synonymOriginals : synonyms.append(kbLatinString(it)) """ ## Read data # ////////////////////////////////////////////////////////////////////////////// ##### DEPRICATED ##### read data straight from the database ## ## # enter your ## HOST = "mariadb" # server IP address/domain name ## DATABASE = "emarket_laravel" # database name ## USER = "emarket_laravel" ## PASSWORD = "" ## ## # connect to MySQL server ## _dbc = mysql.connect( ## host=HOST, ## database=DATABASE, ## user=USER, ## password=PASSWORD, ## use_unicode=True, ## charset='utf8' ## ) ## print("Connected to:", _dbc.get_server_info()) ## ## # execute SQL to get all data you need ## crs = _dbc.cursor() ## query = ''' ## SELECT p.product_title, p.SKU , p.FriendlyUrl as `seoUrl`, ## c.FullFriendlyUrl as `path` ## FROM products p ## LEFT JOIN category_product cp ON cp.product_id = p.id ## LEFT JOIN categories c ON c.id = cp.category_id ## WHERE p.isActive = 1 AND p.Published = 1 AND p.IsCurrentlyActive = 1 ## AND c.isActive AND c.IsCurrentlyActive = 1; ## ''' ## results_ = get_data_from_db(crs, query) ## ## --- ## ## # enter your ## HOST = "127.0.0.1" # server IP address/domain name ## DATABASE = "emarket_laravel_dev" # database name ## USER = "emarket_laravel" ## PASSWORD = "SzvRYl4Y0XU9JXVc" ## DB_SOCKET='/cloudsql/pythia-251711:europe-west4:pythia-db-eu' ## ## # connect to MySQL server ## _dbc = mysql.connect( ## host=HOST, ## database=DATABASE, ## user=USER, ## password=PASSWORD, ## use_unicode=True, ## charset='utf8' ## ) ## print("Connected to:", _dbc.get_server_info()) ## ## ## # execute SQL to get all data you need ## crs = _dbc.cursor() ## query = ''' ## SELECT count(pl.eys_code) as FREQuency, ## pl.product_id as product_id, ## pb.brand_name, ## pl.barcode, pl.skl_code, pl.eys_code, ## IF( pd.description IS NOT NULL , pd.description , pl.product_description) AS product_description ## FROM product_list as pl ## LEFT JOIN delivery_orders_products AS dop ON dop.product_id = pl.eys_code ## LEFT JOIN delivery_orders AS do ON dop.order_id = do.id ## LEFT JOIN product_list AS replacement ON dop.replacement_for = replacement.eys_code ## LEFT JOIN product_details AS pd ON pl.eys_code = pd.eys_code ## INNER JOIN product_brands pb ON pl.brand_id = pb.id ## WHERE pl.active = 1 AND pl.sap_code IS NOT NULL ## GROUP BY pl.product_id ## ORDER BY FREQuency DESC ## ''' ## results_ = get_data_from_db(crs, query) ##### NEW ## ----------------------------------------------------------------------------- ## get data from endpoint (url) url = "http://localhost/api/v1/productsSearch" json_url = urlopen(url) received = json.loads(json_url.read()) # print(data) # sys.exit() # --- Lists to fill keywords_ = [] # all data minilist_ = [] products_ = [] ## keywords format: ## [ ## { ## w : 'fresh', ## alt : [ 'Fresh', 'FRESH', 'fresh' ] ## kb : ## f : 150, ## c : [ ## { w : 'milk', f : 150 , p : [122, 254, 907] }, ## { w : 'juice', f : 50 , p : [254, 351] } ## ] ## }, ## {...}, ## ... ## ] ## --- index: ## w : word (str/utf-8) ## f : frequency (int) ## c : combos / connections (list of objects) ## p : list of product-ids found in specific words-combination (list of int) ## alt : list of alternative writtings (list of str/utf-8) ## kb: *keyboard* writting (str/latin-ascii) ## LOOP through the rows to pre-proccess all products ## --- ## for row in results_ : for rec in received['data'] : ### description = row[_COL['product_title']] # product description ### pid = domeInt( row[_COL['SKU']] ) # product-id ### fq = domeInt( 1 ) # frequency ### # url = '/'+ row[_COL['path']] +'/'+ row[_COL['seoUrl']] # product url ## process only products with images # --- if rec['img'] == 1 : description = rec['txt'] pid = ['id'] fq = 1 # setup product # --- products_.append({ 't' : description, 'i' : pid, 'f' : fq # 'u' : url }) # TODO: # identify brands # then ... description = cleanText(description) # clean description string before spliting keys = [] # list of product's key(word)s words = description.split() # split to words for w in words : if kbLatinString(w) not in removeList: # if not in removeList if isSignificant(w) : # and if significant keys.append(w) # keep it # print(pid, description, words, keys) # append words (and their combos) to the list for w in keys : rootKey( w, fq, keywords_ ) for w2 in keys : if w2 != w and isSignificant(w2) : connectKeys( w, w2, pid, fq, keywords_ ) ## SORT keywords # ////////////////////////////////////////////////////////////////////////////// # --- sort childs of each key (per frequency, desc) for it in keywords_ : it['c'].sort(key=lambda x: x['f'], reverse=True) # --- sort root keys keywords_.sort(key=lambda x: x['f'], reverse=True) # --- create mini list based on the sorted keywords_ for it in keywords_ : minilist_.append({ 'w' : it['w'], 'f' : it['f'], 'kb': it['kb'] }) ## OUTPUT final data to a json-format file # ////////////////////////////////////////////////////////////////////////////// with open("./keywords-v5.json", "w", encoding="utf-8") as outfile : data = json.dump(keywords_, outfile, sort_keys=False, indent=3, ensure_ascii=False) with open("./minilist-v5.json", "w", encoding="utf-8") as outfile : data = json.dump(minilist_, outfile, sort_keys=False, indent=3, ensure_ascii=False) with open("./products-v5.json", "w", encoding="utf-8") as outfile : data = json.dump(products_, outfile, sort_keys=False, indent=3, ensure_ascii=False)