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-src-mysql-v2.py | 623 ++++++++++++++++++++++++++++++++++++++++ 1 file changed, 623 insertions(+) create mode 100644 python/products-src-mysql-v2.py (limited to 'python/products-src-mysql-v2.py') diff --git a/python/products-src-mysql-v2.py b/python/products-src-mysql-v2.py new file mode 100644 index 0000000..034da0d --- /dev/null +++ b/python/products-src-mysql-v2.py @@ -0,0 +1,623 @@ +## 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 datetime + +t0_ = datetime.datetime.now() + +## 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.replace(' ', ' ') # one lase (just in case) + + +## kbLatinString ... +# -> translate/re-wrrite string using latin-characters +# -> function is used used anywhere +def kbLatinString( txt ) : + maTable = txt.maketrans( + "ςερτυθιοπασδφγηξκλζχψωβνμΕΡΤΥΘΙΟΠΑΣΔΦΓΗΞΚΛΖΧΨΩΒΝΜάέήίόύώϊΐϋΆΈΉΊΌΎΏΪΫQWERTYUIOPASDFGHJKLZXCVBNM", + "sertyuiopasdfghjklzxcvbnmertyuiopasdfghjklzxcvbnmaehioyviiyaehioyviyqwertyuiopasdfghjklzxcvbnm" + ) + txt = txt.replace('\'', '') + return txt.translate(maTable).lower() + + +# letters-only translation to key-pressed characters (latin) +# this minimized version of kbLatinString is used in markLink() +# --- +def kbLatinLetter( txt ) : + maTable = txt.maketrans( + "ςερτυθιοπασδφγηξκλζχψωβνμΕΡΤΥΘΙΟΠΑΣΔΦΓΗΞΚΛΖΧΨΩΒΝΜάέήίόύώϊΐϋΆΈΉΊΌΎΏΪΫ", + "sertyuiopasdfghjklzxcvbnmertyuiopasdfghjklzxcvbnmaehioyviiyaehioyviy" + ) + return txt.translate(maTable).lower() + + +# isSignificant +# decides if the term is significant to be indexed; +# a term is significant if does not contain digit-chars [0-9], comma (,) or period (.) +# --- +significantExceptios = '7UP 3ΑΛΦΑ 17 3Π 7DAYS K2R'.split(' ') +def isSignificant(x) : + # fisrts exclude some notable exceptions (mostly brands) + if x in significantExceptios : + return True + + return not bool( re.match("\S*\d+\S*", x) ) + + +# check if word: w +# … has synonyms; return list of synonyms +# --- +def synonymKeys(w) : + w_kb = kbLatinString(w) + syns = [ w ] + found = False + # check if has synonyms + for group in synonyms : + possibles = group.split() + for wi in possibles : + if kbLatinString(wi) == w_kb : + syns = possibles + found = True + break + if found : + break + return syns + + +# set root-keyword: wl (if not exist) +# update frequency: f +# into list: l +# NOTE: +# * wl is a list of synonym-words +# ** comparison is based on the *keyboard* format +## --- +def rootKey ( wl, f, l ) : + keyExists = False + w_kb = kbLatinString(wl[0]) # cache kb format + + # check if exists in root keys already + # NOTE: you only need to check the 1st word of synonyms-list + for it in l : + if it['kb'] == w_kb : + keyExists = True + it['f'] += f + break + + # if not exists, append keyword + if keyExists == False : + l.append({ + 'w' : wl, + 'kb' : w_kb, + 'f' : f, + 'c' : [] + }) + + +# connect keys: a , b (each one is a list of synonmyms) +# of product with id: i +# with frequency: f +# into list: l +## --- +def connectKeys( a, b, i, f, l ) : + kbA = kbLatinString(a[0]) + kbB = kbLatinString(b[0]) + + if kbA == kbB : + return False ## exclude just-in-case + + for it in l : + if it['kb'] == kbA : + # found: a; + + # let's update connection to: b + bExists = False + for jt in it['c'] : + if jt['kb'] == kbB : + bExists = True + # update the connection's data + jt['f'] += f + jt['p'].append(i) + break + + if bExists == False : + # create connection with word: b + it['c'].append({ + 'w': b, + 'kb': kbB, + 'f': f, + 'p': [ i ] + }) + break + + +## 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 + + +## replaces +# do all replaces in place +# --- (preproccessing) +replaces = [] +replaceSource = [ + '3 ΑΛΦΑ ;3ΑΛΦΑ ', + 'HEAD & SHOULDERS ;HEAD&SHOULDERS ', + 'W.K Kellogg ; ', + 'ΦΙΛΕΤ ;Φιλέτο ', + 'ΕΝΕΛΛΑΔ ;Εν-Ελλάδι ', + 'ΓΑΛΟΠΟΥΛ ;Γαλοπούλα ', + '7 DAYS ;7DAYS ', + 'ΜΠΑΡΜΠΑ ΣΤΑΘΗ ;ΜΠΑΡΜΠΑ-ΣΤΑΘΗΣ ' +] +for it in replaceSource : + st = it.split(';') + replaces.append({ 'src': st[0], 'trg': st[1] }) + +def do_replaces(w) : + for it in replaces : + w = w.replace(it['src'], it['trg']) + return w + + +## main preproccess function for product descriptions +# --- +def preprocessEdit(w) : + w = do_replaces(w) + # ... do other things if needed + # then ... + return w + + +## mark a link to a text +# conecting them with a dash/minus character +# --- +def markLink(lws, text) : + text_kb = kbLatinLetter(text.replace(' ', '-')) + lws_kb = kbLatinLetter(lws) + try: + index_l = text_kb.lower().index(lws_kb.lower()) + except: + return text + else: + return text[:index_l] + lws + text[index_l + len(lws):] + + +### # --- list of normalized word combinations +### replaceWords = [ +### 'HEAD & SHOULDERS; HEAD&SOULDERS', +### 'ΟΛΙΚΗΣ 'ΑΛΕΣΗΣ; Ολικής Άλεσης', +### 'Χωρίς προσθήκη ζάχαρης; Χωρίς-Ζάχαρη', +### 'ΚΑΠΝ.CRETA-FARMS; ΚΑΠΝΙΣΤΗ CRETA-FARMS' +### ] + + +# --- list of linked-words +linkedWords = [ + 'Χωρίς-Γλουτένη', + 'Χωρίς-Ζάχαρη', + 'Χωρίς-Αλάτι', + 'Χωρίς-Λακτόζη', + 'Χωρίς-Συντηρητικά', + 'Χωρίς-Αλκοόλ', + 'Χωρίς-Kαφεϊνη', + 'Χωρίς-Γλυκάνισο', + 'Χωρίς-Ανθρακικό', + 'Υψηλής-Παστερίωσης', + 'Ολικής-Άλεσης', + 'Ολικής-Aλέσεως', + 'Χαρτί-Υγείας', + 'ρολό-υγείας', + 'χαρτί-τουαλέτας', + 'Χαρτί-Κουζίνας', + 'Μπάρες-Δημητριακών', + 'Μπαρμπα-Στάθης', + 'COCA-COLA' + 'Aς-Μαγειρέψουμε', + 'ΚΡΙΣ-ΚΡΙΣ', + 'ΚΡΙ-ΚΡΙ', + 'ΕΛ-ΓΚΡΕΚΟ', + 'FREE-STEP', + 'EL-SABOR', + 'LE-PETIT-MARSEILLAIS', + 'DOUWE-EGBERTS', + 'ΕΝ-ΕΛΛΑΔΙ', + 'SPIN-SPAN', + 'CRETA-FARMS', + 'CRETA-FARM', + 'NES-CAFE', + 'Ολες-τις-Χρήσεις', + 'Το-Μάννα', + 'Χωρίς-προσθήκη-ζάχαρης' +] + + + +# mark linked words (connect them with a dash) +# return new text after "all-links" are marked +# --- +def markLinkedWords(text) : + for lw in linkedWords : + text = markLink( lw, text ) + return text + + +## handle words that can never be the first word on a search +noRootKeywords = [] +noRoot = [ + 'χωρίς', + 'εισαγωγής', + 'δώρο', + 'γεύση', + 'γεύσεις', + 'φέτες', + 'Χωρίς-Γλουτένη', + 'Χωρίς-Ζάχαρη', + 'Χωρίς-Αλάτι', + 'Χωρίς-Λακτόζη', + 'Χωρίς-Συντηρητικά', + 'Χωρίς-Αλκοόλ', + 'Χωρίς-Kαφεϊνη', + 'Χωρίς-Γλυκάνισο', + 'Χωρίς-Ανθρακικό', + 'Υψηλής-Παστερίωσης', + 'Ολες-τις-Χρήσεις', + 'Ολικής-Άλεσης', + 'Ολικής-Aλέσεως', + 'Ολικής', + 'Γαϊδούρας', + 'Γαϊδάρου', + 'Ρούχων', + 'Πιάτων', + 'πλύσεις', + 'Πλυντηρίου', + 'Φύλλων', + 'Γάλακτος', + 'Χρήσης', + 'Τύπου', + 'Ολλανδίας', + 'Απορριμμάτων', + 'Medium', + 'Μαλλιά', + 'Μαλλιών', + 'Γενικής', + 'Plus', + 'Classic', + 'Έκπληξη', + 'Μάνης', + 'Ελάτου', + 'Άγριων', + 'Βοτάνων', + 'Λακωνίας', + 'ΠΑΡΑΓΓΕΛΙΩΝ' +] +for w in noRoot : + noRootKeywords.append(kbLatinString(w)) + + +## PREPARE (or build) exception objects +# ////////////////////////////////////////////////////////////////////////////// + + +# --- list of words to exclude from keywords +# NOTE: +# APPLIED in 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 synonyms +# in fact +synonyms = [ + 'μπίρα μπύρα μπίρες μπύρες', + 'αυγά αβγά αυγό', + 'σίκαλης σικάλεως', + 'ξηρά ξερά', + 'ρολό ρολλό', + 'coca-cola cocacola coke', + 'χαρτί-υγείας ρολό-υγείας χαρτί-τουαλέτας', + 'χαρτί-κουζίνας ρολό-κουζίνας', + 'οινος κρασι', + 'ΚΑΤΣΕΛΗΣ ΚΑΤΣΕΛΗ', + 'DR-OETKER OETKER', + 'DR.BECKMANN BECKMANN', + 'NES-CAFE NESCAFE', + 'Ολικής-Άλεσης Ολικής-Aλέσεως Ολικής', + 'τσίπουρο ρακή', + 'Βρώμη Βρώμης', + 'Φράουλα Φράουλες Φράουλας', + 'Μαλλιά Μαλλιών', + 'Κέικ, Cake', + 'CRETA-FARMS CRETA-FARM', + 'MARSEILLAIS LE-PETIT-MARSEILLAIS PETIT-MARSEILLAIS', + 'Γαϊδούρας Γαϊδάρου', + 'ΚΑΛΟΓΕΡΑΚΗΣ ΚΑΛΟΓΕΡΑΚΗ', + 'ΚΑΪΔΑΝΤΖΗΣ ΚΑΪΔΑΝΤΖΗ', + 'ΥΦΑΝΤΗΣ ΥΦΑΝΤΗ', + 'ΣΥΝΑΓΡΙΔΑ ΣΥΝΑΓΡΙΔΕΣ', + 'Ντομάτα Ντομάτας', + 'Ελαφρύ Ελαφρά Light', + 'Εγχώρια Εγχώριες Ελληνικό Ελληνική Ελληνικά', + 'τριμμένη τριμμένο', + 'Τόνος Τόνου', + 'Κριθαρένια κρίθινα', + 'Χωρίς-Kαφεϊνη Decaffeine', + 'Το-Μάννα Μάννα', + 'Κράνμπερι Κράνμπερις', + 'Κρήτης Κρητικό', + 'Πέννες Πένες', + 'Μακαρόνια Σπαγγέτι Σπαγγετίνι Σπαγγετόνι', + 'Καρτέλλα Καρτέλα Καρτέλλες' +] + + +## Read data +# ////////////////////////////////////////////////////////////////////////////// + + +## 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 + 'bpcs' : 7, # bpcs_code + 'img' : 8 # product's image file-name +} + +# enter your +HOST = "127.0.0.1" # server IP address/domain name +DATABASE = "dev_pythia_db" # database name +USER = "pythia_db_user_dev" +PASSWORD = "VnEP0eysjiXDHcfM" + +# 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, + pl.bpcs_code, + pd.image_path + 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 + LEFT JOIN product_brands pb ON pl.brand_id = pb.id + WHERE pl.active = 1 AND pl.sap_code IS NOT NULL AND pl.product_category_sap_4 NOT LIKE '72%' + GROUP BY pl.product_id + ORDER BY FREQuency DESC +''' +results_ = get_data_from_db(crs, query) + +t_read = datetime.datetime.now() + + +# --- Lists to fill +keywords_ = [] # all data; main exported object +minilist_ = [] +products_ = [] + +## keywords format: +## [ +## { +## w : [ word, word-synonym, ... ], +## kb : = kbLatinString(word) +## f : 150, +## c : [ +## { w: ['fish', 'fishes'], f: 150, p: [122, 254, 907] }, +## { w: ['juice'], f: 50, p: [254, 351] } +## ] +## }, +## ... +## ] +## +## --- index: +## w : words / list of synonyms (str/utf-8) +# kb : ascii-latin-keypoard format of first item of "w" list +## f : frequency (int) +## c : combos / connections (list of objects) +## p : list of product-ids found in specific words-combination (list of int) + + +records_counter = 0 +## LOOP through the rows to pre-proccess all products +## --- +for row in results_ : + records_counter += 1 + + description = row[_COL['descr']] # product description + ## depricate: pid = domeInt( row[_COL['pid']] ) # product-id + fq = 0 if None else domeInt( row[_COL['freq']] ) # frequency + barcode = 0 if None else domeInt( row[_COL['barcd']] ) + sklcode = 0 if None else domeInt( row[_COL['sklcd']] ) + eyscode = 0 if None else domeInt( row[_COL['eyscd']] ) + bpcs = 0 if None else domeInt( row[_COL['bpcs']] ) + img = row[_COL['img']] + + pid = eyscode # actual product id (pid) it the eys_code + + # edit descriptions + description = preprocessEdit(description) + + # setup product + # --- + products_.append({ + 'w' : description, + 'id' : eyscode, + 'f' : fq, + 'bc' : barcode, + 'sc' : sklcode, + 'bp' : bpcs, + 'i' : img + }) + + + description = cleanText(description) # clean description string before spliting + + description = markLinkedWords(description) # ... + + 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) + ## print(pid, keys) + + # append words (and their combos) to the list + for w in keys : + wl = synonymKeys(w) + + # update root word frequency (if w CAN be a root word) + if kbLatinLetter(w) not in noRootKeywords : + rootKey( wl, fq, keywords_ ) + + ## if no other keyword in description add a dummy one + # so preserve reference to the final product + if len(keys) == 1 : + connectKeys( wl, ['*'], pid, fq, keywords_ ) + + for w2 in keys : + if w2 != w : + w2syns = synonymKeys(w2) + connectKeys( wl, w2syns, pid, fq, keywords_ ) + + + +# --- remove 'kb' keywords +for ki in keywords_ : + del ki['kb'] # kb not needed (kb_translation in js is really fast) + + +## 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) + + + +t_main = datetime.datetime.now() +print('Proccessing ended; saving results in json format ...') + + +## OUTPUT final data to a json-format file +# ////////////////////////////////////////////////////////////////////////////// + +with open("results/keywords.json", "w", encoding="utf-8") as outfile : + data = json.dump(keywords_, outfile, sort_keys=False, ensure_ascii=False) + +with open("results/products.json", "w", encoding="utf-8") as outfile : + data = json.dump(products_, outfile, sort_keys=False, ensure_ascii=False, separators=(',', ':')) + + +t_end = datetime.datetime.now() + +print(records_counter, 'products proccessed') +print('execution time:', (t_end - t0_)) +print('read.n.parse sources:', (t_read - t0_)) +print('proccessing products:', (t_main - t_read)) + + + +## NOTE: +## prepare cloud-sql-proxy +## --- +## * install cloud-sql-proxy +## : sudo wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64 -O /usr/local/cloud_sql_proxy +## : sudo chmod +x /usr/local/cloud_sql_proxy +## +## * prepare/export/publish/copy credentials ... +## : sudo cp /some/path/to/cloudsqlproxy.json /usr/local +## +## * finaly run the database instance +## : /usr/local/cloud_sql_proxy -instances=pythia-251711:europe-west4:pythia-db-eu=tcp:3306 -credential_file=cloudsqlproxy.json +## +## after installation only the last command needs to run before connecting to the cloud-sql + + +# ΠΑΝΤΕΛΟΝΙ ΑΝΔ ΦΟΥΤ ΑΝ ΣΤΑ ΠΡΑΣ XXXL +# MAYBELLINECONCEALERAGEREWBLMEDIUM \ No newline at end of file -- cgit v1.2.3