From c50d4c645cd3c04204106c4f9f026e5910afa3d5 Mon Sep 17 00:00:00 2001 From: Geo Halkiadakis Date: Fri, 17 Mar 2023 13:30:46 +0200 Subject: Code tree reorganized; older implemenatations act as a start point --- python/products-dict-v5.py | 428 +++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 428 insertions(+) create mode 100644 python/products-dict-v5.py (limited to 'python/products-dict-v5.py') diff --git a/python/products-dict-v5.py b/python/products-dict-v5.py new file mode 100644 index 0000000..329a212 --- /dev/null +++ b/python/products-dict-v5.py @@ -0,0 +1,428 @@ +## 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 + + + +## 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 +# ////////////////////////////////////////////////////////////////////////////// + +## # 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()) + +sys.exit() + +# 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) + + + +# --- 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_ : + + 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 + + # 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("results/keywords-v5.json", "w", encoding="utf-8") as outfile : + data = json.dump(keywords_, outfile, sort_keys=False, indent=3, ensure_ascii=False) + +with open("results/minilist-v5.json", "w", encoding="utf-8") as outfile : + data = json.dump(minilist_, outfile, sort_keys=False, indent=3, ensure_ascii=False) + +with open("results/products-v5.json", "w", encoding="utf-8") as outfile : + data = json.dump(products_, outfile, sort_keys=False, indent=3, ensure_ascii=False) \ No newline at end of file -- cgit v1.2.3