def prepare_display1(): """ Refresh Display1, then run routine.RoutineDisplay1 on the asins in Display1 to update their MWS data, then refresh Display1 again. This will usually filter out asins that only originally made it into Display1 due to some MWS data fluke. """ a = datetime.datetime.now() transfer_wm_datums() calc_column('salesrank') calc_column('net') con = con_postgres() call_sql(con, 'REFRESH MATERIALIZED VIEW public."Display1"', [], "executeNoReturn") con.close() e = RoutineDisplay1() e.routine() calc_column('net') con = con_postgres() call_sql(con, 'REFRESH MATERIALIZED VIEW public."Display1"', [], "executeNoReturn") con.close() print('Total time elapsed: {}'.format( str(datetime.datetime.now() - a).split('.')[0]))
def displaydata_checkskus(): """ Checks each asin in purchasedSh to see if a sku already exists for it in io.SKUs. If so, overwrite the sku in purchasedSh with the old sku from SQL. """ purchasedSh = xw.Book.caller().sheets('Purchased') fullRange = purchasedSh.range('A1').current_region headersRow = fullRange.rows(1) datumsRange = fullRange.resize(fullRange.rows.count - 1).offset(1, 0) skusCol = datumsRange.columns(headersRow.value.index('sku') + 1) asins = [ datumsRange.columns(headersRow.value.index('asin') + 1)(i).value for i in range(1, datumsRange.rows.count + 1) ] con = con_postgres() sqlTxt = '''SELECT sku FROM "SKUs" WHERE asin = %s''' for i in range(0, len(asins)): skus = tuple( j[0] for j in call_sql(con, sqlTxt, [asins[i]], 'executeReturn')) if len(skus) > 0: # Use the shortest matching SKU, i.e. the one that doesn't have a used identifier (ULN, UVG, etc.) skusCol(i + 1).formula = min(skus, key=len) else: continue if con: con.close()
def displaydata_fillinvloader(): """ DEPRECATED Removes entries from invLoaderSh that are neither first-timers nor problem children (hazmat, restricted, etc) The only entries remaining in invLoaderSh should be first-timers and problem children """ invLoaderSh = xw.Book.caller().sheets('Inv Loader') fullRange = invLoaderSh.range('A1').current_region headersRow = fullRange.rows(1) datumsRange = fullRange.resize(fullRange.rows.count - 1).offset(1, 0) asinsCol = datumsRange.columns(headersRow.value.index('product-id') + 1) con = con_postgres() sqlTxt = '''SELECT asin FROM "SKUs" WHERE asin NOT IN ( SELECT asin FROM "Purchased" WHERE lower(notes) LIKE '%%hazmat%%' OR lower(notes) LIKE '%%restricted%%' )''' asinsToAxe = [hjk[0] for hjk in call_sql(con, sqlTxt, [], 'executeReturn')] if con: con.close()
def displaydata_tosql_enroute(): enrouteSh = xw.Book.caller().sheets('Enroute') fullRange = enrouteSh.range('A1').current_region _headersRow = fullRange.rows(1) datumsRange = fullRange.resize(fullRange.rows.count - 1).offset(1, 0) # Build datums from the colsNames columns in enrouteSh # The order matters, needs to match the SQL statement colsNames = ['received', 'cancelled', 'lost', 'notes', 'purchase_id'] theData = [] for i in range(1, datumsRange.rows.count + 1): theData.append([ datumsRange.columns(_headersRow.value.index(_g) + 1)(i).value for _g in colsNames ]) con = con_postgres() sqlTxt = '''UPDATE io."Purchased" SET received = %s, cancelled = %s, lost = %s, notes = %s WHERE purchase_id = %s''' call_sql(con, sqlTxt, theData, "executeBatch") if con: con.close()
def json_to_sql(self, theJson): # Takes a raw Product Lookup JSON string and writes it to SQL jsonItems = theJson['items'] theData = [] preConflictTags = ("itemId", "name", "salePrice", "upc", "modelNumber", "brandName", "stock", "availableOnline", "freeShippingOver35Dollars", "clearance") postConflictTags = ("name", "salePrice", "upc", "modelNumber", "brandName", "stock", "availableOnline", "freeShippingOver35Dollars", "clearance") ts = datetime_floor(1.0 / 60) for i in jsonItems: theData.append((ts, ) + tuple(i[tag] if tag in i else None for tag in preConflictTags) + (ts, ) + tuple(i[tag] if tag in i else None for tag in postConflictTags)) if theData: sqlTxt = '''INSERT INTO "Prod_Wm" (fetched, wm_id, name, price, upc, model, brand, in_stock, avail_online, free_ship, clearance) VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) ON CONFLICT ("wm_id") DO UPDATE SET fetched = %s, name = %s, price = %s, upc = %s, model = %s, brand = %s, in_stock = %s, avail_online = %s, free_ship = %s, clearance = %s''' theData.sort(key=itemgetter(1)) con = con_postgres() call_sql(con, sqlTxt, theData, "executeBatch") con.close()
def write_to_sql(self, theData): """ Writes Search API product data to SQL Parameters: theData """ if theData: sqlTxt = '''INSERT INTO "Prod_Wm" (fetched, wm_id, name, price, upc, model, brand, in_stock, avail_online, free_ship, clearance) VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) ON CONFLICT ("wm_id") DO UPDATE SET fetched = %s, name = %s, price = %s, upc = %s, model = %s, brand = %s, in_stock = %s, avail_online = %s, free_ship = %s, clearance = %s''' theData.sort(key=itemgetter(1)) con = con_postgres() call_sql(con, sqlTxt, theData, "executeBatch") if self.subCat: # Need to update with path theData2 = [[self.subCat, g[1]] for g in theData ] # Get wm_id along with the subCat theData2.sort(key=itemgetter(1)) call_sql(con, 'UPDATE "Prod_Wm" SET path = %s WHERE wm_id = %s', theData2, "executeBatch") con.close()
def displaydata_tosql_already(): alreadySh = xw.Book.caller().sheets('Already') fullRange = alreadySh.range('A1').current_region _headersRow = fullRange.rows(1) datumsRange = fullRange.resize(fullRange.rows.count - 1).offset(1, 0) # Build theData from the colsNames columns in alreadySh # The order matters, needs to match the SQL statement colsNames = ('fragile', 'polybag', 'expire', 'ingredients', 'discontinue', 'edit', 'non_joliet', 'sku') theData = [] for i in range(1, datumsRange.rows.count + 1): theData.append([ datumsRange.columns(_headersRow.value.index(_g) + 1)(i).value for _g in colsNames ]) con = con_postgres() sqlTxt = '''UPDATE io."SKUs" SET fragile = %s, polybag = %s, expire = %s, ingredients = %s, discontinue = %s, edit = %s, non_joliet = %s WHERE sku = %s''' call_sql(con, sqlTxt, theData, 'executeBatch') if con: con.close()
def displaydata_toxl_enroute(): con = con_postgres() call_sql(con, 'REFRESH MATERIALIZED VIEW io."Enroute"', [], 'executeNoReturn') con.close() sql_to_xl('io.Enroute', wkbk=xw.Book.caller(), sh='Enroute')
def displaydata_toxl_already(): con = con_postgres() call_sql(con, 'REFRESH MATERIALIZED VIEW public."Already"', [], 'executeNoReturn') con.close() sql_to_xl('Already', wkbk=xw.Book.caller(), sh='Already')
def displaydata_toxl_instock(): con = con_postgres() call_sql(con, 'REFRESH MATERIALIZED VIEW io."In_Stock"', [], "executeNoReturn") con.close() sql_to_xl('io.In_Stock', wkbk=xw.Book.caller(), sh='In Stock')
def restrictedbrands_tosql(): xl_to_sql('io.Restr_Brands', xw.Book.caller(), sh='Brands', upsert=True) con = con_postgres() sqlTxt = '''UPDATE io."Restr_Brands" SET checked_date = CURRENT_DATE WHERE checked_date is Null''' call_sql(con, sqlTxt, [], 'executeNoReturn') if con: con.close()
def delete_bad_upcs(): """ Delete items with non-numeric UPCs from Prod_Wm """ con = con_postgres() sqlTxt = '''DELETE FROM "Prod_Wm" WHERE ISNUMERIC(upc) is FALSE''' call_sql(con, sqlTxt, [], "executeNoReturn") if con: con.close()
def displaydata_tosql_instock(): isStockSh = xw.Book.caller().sheets('In Stock') fullRange = isStockSh.range('A1').current_region _headersRow = fullRange.rows(1) datumsRange = fullRange.resize(fullRange.rows.count - 1).offset(1, 0) # Build theData from the colsNames columns in instockSh # The order matters, needs to match the SQL statement colsNames1 = [ 'labeled', 'packing', 'lost', 'pack_date', 'notes', 'purchase_id' ] theData1 = [] colsNames2 = ['non_joliet', 'sku'] # The order matters, needs to match the SQL statement theData2 = [] for i in range(1, datumsRange.rows.count + 1): theData1.append([ datumsRange.columns(_headersRow.value.index(_g) + 1)(i).value for _g in colsNames1 ]) theData2.append([ datumsRange.columns(_headersRow.value.index(_g) + 1)(i).value for _g in colsNames2 ]) # Go through and turn any blank strings ('') into None. Postgres can't handle blank strings in non-text columns. # Could bo done inside the list compehension with a conditional, but that would be ugly...but also faster. for i, theRow in enumerate(theData1): for j, theColumn in enumerate(theRow): if theColumn == '': theData1[i][j] = None con = con_postgres() sqlTxt = '''UPDATE io."Purchased" SET labeled = %s, packed = COALESCE(packed, 0) + COALESCE(%s, 0), lost = %s, pack_date = %s, notes = %s WHERE purchase_id = %s''' call_sql(con, sqlTxt, theData1, "executeBatch") sqlTxt = '''UPDATE io."SKUs" SET non_joliet = %s WHERE sku = %s''' call_sql(con, sqlTxt, theData2, 'executeBatch') if con: con.close()
def fill_q(self, op, q): """ Updates the queue for each MWS function from SQL """ if 'args' not in self.qDefs[op]: self.qDefs[op]['args'] = [] con = con_postgres() # Combine with existing queue, ensuring no duplicates blurp = deque(union_no_dups(list(q), call_sql(con, self.qDefs[op]['qry'], self.qDefs[op]['args'], "executeReturn"))) con.close() return blurp
def update_wm_query_log(num, ts=None): """ Upserts timestamps and num_queries in wm.WmQueryLog Parameters: num, ts """ if not ts: ts = datetime_floor(5.0) # Update WmQueryLog. If queries have already been logged for the current timestamp, add to that total. sqlTxt = '''INSERT INTO wm."WmQueryLog" (timestamp, num_queries) VALUES(%s, %s) ON CONFLICT (timestamp) DO UPDATE SET num_queries = "WmQueryLog".num_queries + %s''' con = con_postgres() theData = [ts, num, num] call_sql(con, sqlTxt, theData, "executeNoReturn")
def get_all(self, triggs=None): """ Goes through all the subcats and retrieves all the products (up to 1000) for each one. Writes to SQL """ con = con_postgres() overCallLimit = False for i in range(0, len(self.subcatsList)): # Loops through all the subcats # Checks WmQueryLog to see if too many calls to the Walmart API have been made in the last 24 hours sqlTxt = '''SELECT COALESCE(SUM(num_queries), 0) FROM "WmQueryLog" WHERE EXTRACT(EPOCH FROM (localtimestamp - "timestamp")/86400) < 1''' # Counts # of queries in last 24 hours hng = call_sql(con, sqlTxt, [], 'executeReturn')[0][0] if hng >= self.maxDailyCalls: print("Over daily call limit for the Walmart API! ({})".format( hng)) if triggs: for _, value in triggs['send'].items(): value.send( 'Over Walmart API daily call limit of {}'.format( self.maxDailyCalls)) value.close() overCallLimit = True break # Retrieve all the products for the subcat from the Walmart API SearchSubcat(self.subcatsList[i]["full_id"]).get_all_for_subcat() if con: con.close() if not overCallLimit: print( 'Exhausted all queriable subcategories with the Walmart API!') if triggs: for _, value in triggs['send'].items(): value.send( 'Over Walmart API daily call limit of {}'.format( self.maxDailyCalls)) value.close()
def update_wm_data_timestamps(): print('update_wm_data_timestamps - starting...') con = con_postgres() sqlTxt = '''SELECT b.asin, a.fetched FROM "Prod_Wm" as a INNER JOIN "Products_WmAz" as b ON a.wm_id = b.wm_id WHERE a.fetched IS NOT Null''' datums = call_sql(con, sqlTxt, [], 'executeReturn') if datums: record_timestamps(datums, 'wm_data') if con: con.close() print('update_wm_data_timestamps - finished')
def test_get_arrays(): enrouteSh = xw.Book.caller().sheets('Enroute') fullRange = enrouteSh.range("A1").current_region _headersRow = fullRange.rows(1) datumsRange = fullRange.resize(fullRange.rows.count - 1).offset(1, 0) expireCol = datumsRange.columns(_headersRow.value.index('expire') + 1) purchaseIdCol = datumsRange.columns( _headersRow.value.index('purchase_id') + 1) qwe = get_arrays_list(enrouteSh, expireCol, (datetime.datetime, int)) theData = [[qwe[i], purchaseIdCol[i].value] for i, _ in enumerate(qwe)] sqlTxt = '''UPDATE io."Purchased" SET expire = %s WHERE purchase_id = %s''' con = con_postgres() call_sql(con, sqlTxt, theData, 'executeBatch') con.close()
def taxo_to_mem(self): """ Reads WmTaxo_Static from SQL and puts it into a list of lists """ con = con_postgres() sqlTxt = '''SELECT full_id, last_searched--, include FROM "WmTaxo_Updated" WHERE active IS TRUE AND (include = 1 OR include IS Null) AND (success NOT IN ('4003', 'totalResults_value_is_0') OR last_searched IS Null OR EXTRACT(EPOCH FROM (localtimestamp - last_searched)/86400) > 30) ORDER BY last_searched ASC''' self.subcatsList = call_sql(con, sqlTxt, [], 'executeReturn', dictCur=True) if con: con.close()
def intertwine_taxos(self): """ Share some columns between WmTaxo_Static and WmTaxo_Updated """ con = con_postgres() # Copy "include" column from WmTaxo_Static to WmTaxo_Updated # sqlTxt = '''UPDATE "WmTaxo_Updated" t2 # SET include = t1.include # FROM "WmTaxo_Static" t1 # WHERE t2.full_id = t1.full_id # AND t2.include IS DISTINCT FROM t1.include''' # call_sql(con, sqlTxt, [], "executeNoReturn") # Copy new subcats over to WmTaxo_Static from WmTaxo_Updated. On conflict, update "active" column sqlTxt = '''INSERT INTO "WmTaxo_Static" (full_id, dept_id, dept_name, cat_id, cat_name, subCat_id, subCat_name, active) SELECT full_id, dept_id, dept_name, cat_id, cat_name, subCat_id, subCat_name, active FROM "WmTaxo_Updated" ON CONFLICT (full_id) DO UPDATE SET active = excluded.active''' call_sql(con, sqlTxt, [], "executeNoReturn")
def test_insert_lists(): enrouteSh = xw.Book.caller().sheets('Enroute') fullRange = enrouteSh.range('A1').current_region _headersRow = fullRange.rows(1) datumsRange = fullRange.resize(fullRange.rows.count - 1).offset(1, 0) purchaseIdCol = datumsRange.columns( _headersRow.value.index('purchase_id') + 1) # SELECTs from multiple parameters in list form (purchaseIdCol.value) # The return is ordered the same as the parameter list was ordered # https://stackoverflow.com/a/35456954/5253431 sqlTxt = '''SELECT expire FROM "Purchased" JOIN UNNEST('{{{}}}'::text[]) WITH ORDINALITY t(purchase_id, ord) USING (purchase_id) ORDER BY t.ord'''.format(','.join(purchaseIdCol.value)) con = con_postgres() expire = tuple( q[0] for q in call_sql(con, sqlTxt, purchaseIdCol.value, 'executeReturn')) con.close() insert_lists(expire, xw.Book.caller(), 'notes', 'Enroute')
def mark_wm_dups(): """ Mark Prod_Wm.dup as True for items that have non-unique UPCs """ con = con_postgres() # Set all dups rows to False, then overwrite the ones that are upc duplicates with True sqlTxt = '''LOCK TABLE "Prod_Wm" IN SHARE ROW EXCLUSIVE MODE; UPDATE "Prod_Wm" AS a SET dup = False; UPDATE "Prod_Wm" AS a SET dup = True FROM ( SELECT upc FROM "Prod_Wm" WHERE upc IS NOT Null GROUP BY upc HAVING count(*) > 1 ) AS subqry WHERE a.upc = subqry.upc''' call_sql(con, sqlTxt, [], "executeNoReturn") if con: con.close()
def sql_to_xl(tbl, wkbk=None, sh='Sheet1', where=None): """ Writes a postgres table into Excel <where> allows a SQL WHERE clause to be added to the SELECT statement """ if wkbk: sht = wkbk.sheets(sh) else: sht = xw.Book.caller().sheets(sh) fullRange = sht.range('A1').current_region headersRow = fullRange.rows(1) try: datumsRange = fullRange.resize(fullRange.rows.count - 1).offset(1, 0) except AssertionError: # No data currently in the sheet datumsRange = headersRow.offset(1, 0) datumsRange.clear_contents() # Format the table name to have the appropriate quotes depending on if it includes a schema or not yerp = tbl.split('.') if len(yerp) == 2: theTbl = ".".join([yerp[0], '"{}"'.format(yerp[1])]) metaTblName = "'{}'".format(yerp[1]) else: theTbl = '"{}"'.format(tbl) metaTblName = "'{}'".format(tbl) # Get the column names from SQL. # This method is used instead of information_schema because it works for materialized views. sqlTxt = '''SELECT a.attname FROM pg_attribute a JOIN pg_class t ON a.attrelid = t.oid WHERE a.attnum > 0 AND NOT a.attisdropped AND t.relname = {}'''.format( metaTblName) con = con_postgres() sqlTblNames = tuple(i[0] for i in call_sql(con, sqlTxt, [], 'executeReturn')) # Get column names from Excel, using the headers that have comments. # If the excel header exists in the SQL table, append the header as-is # If the excel header doesn't exist in the SQL table, still append it, but precluded with "Null AS". colNames = [] colIndexes = [] for header in headersRow: try: _ = header.api.Comment.Text() except: print("Not using {}".format(header.value)) else: if header.value in sqlTblNames: colNames.append(header.value) else: colNames.append('Null AS {}'.format(header.value)) colIndexes.append(header.column) colNamesStr = ", ".join(colNames) sqlTxt = '''SELECT {} FROM {}'''.format(colNamesStr, theTbl) if where: sqlTxt += ' {}'.format(where) print(sqlTxt) # Double tuple comprehension: creates a tuple of tuples with the column index and the data for that column. # The data part of each 2nd level tuple is itself a tuple, and transposed so the data pastes in as columns instead # of rows. datumsByCol = tuple(( colIndexes[i], tuple((j[i], ) for j in call_sql(con, sqlTxt, [], 'executeReturn')), ) for i in range(len(colIndexes))) if con: con.close() for col in datumsByCol: datumsRange(1, col[0]).value = col[1]
def xl_to_sql(tbl, caller, sh='Sheet1', upsert=False): """ Writes the data from the Excel sheet <sh> in workbook <caller> into the SQL table <tbl>. If <tbl> already exists, the rows will be inserted/upserted. Otherwise, a new table will be created. If <upsert> is True, the rows will be upserted. Otherwise, they'll be inserted. Only columns with comments in the headers will be written to SQL. If a new table is being created, its columns' types will be based on the text of the comments. The first column of the Excel sheet must be the primary key of the SQL table. """ con = con_postgres() # Format the table name to have the appropriate quotes depending on if it includes a schema or not yerp = tbl.split(".") if len(yerp) == 2: tbl = '.'.join([yerp[0], '"{}"'.format(yerp[1])]) else: tbl = '"{}"'.format(tbl) sht = caller.sheets(sh) a = sht.range('A1').current_region numItems = a.rows.count - 1 # Get column names and types from Excel colNames, colTypes, datums = ([] for _ in range(3)) colNum = 1 for i in a.resize(1): try: colTypes.append(i.api.Comment.Text()) except: print('Not using {}'.format(i.value)) else: colNames.append(i.value) datums.append(a.resize(numItems, 1).offset(1, colNum - 1).value) finally: colNum += 1 # If datums isn't 2d (only 1 item was appended) then make it 2d anyways if not isinstance(datums[0], list): datums = [[vvv] for vvv in datums] # Transpose datums. https://stackoverflow.com/questions/6473679/transpose-list-of-lists datums = list(map(list, itertools.zip_longest(*datums))) # Re-add non-primary key columns to datums for upsert (These columns need to be included twice) # ['Books', 62428868] becomes ['Books', 62428868, 62428868] for each inner list in datums, which is a list of lists if upsert: for item in datums: item.extend(item[1:len(item)]) # Write out the <column_name>, <column_type> part of the CREATE TABLE statement if colTypes: if colTypes[0] not in ['', '_', 'Tim:_']: nameType = [] for i in range(0, len(colNames)): if i == 0: # First column is automatically assigned as PRIMARY KEY nameType.append('{} {} NOT NULL PRIMARY KEY'.format( colNames[i], colTypes[i])) else: nameType.append('{} {}'.format(colNames[i], colTypes[i])) nameTypeTxt = ', '.join(nameType) # Create a table with the correct column names and types sqlTxt = '''CREATE TABLE IF NOT EXISTS {} ({});'''.format( tbl, nameTypeTxt) call_sql(con, sqlTxt, [], 'executeNoReturn') names = ", ".join(colNames) formatters = ", ".join(['%s'] * len(colNames)) if upsert: afterSetList = [] for i in range( 1, len(colNames)): # Skip first colName, which is the primary key afterSetList.append('{} = %s'.format(colNames[i])) afterSet = ", ".join(afterSetList) # Insert the data into the table sqlTxt = '''INSERT INTO {} ({}) VALUES({}) ON CONFLICT ("{}") DO UPDATE SET {}'''.format(tbl, names, formatters, colNames[0], afterSet) else: # Insert the data into the table sqlTxt = '''INSERT INTO {} ({}) VALUES({})'''.format(tbl, names, formatters) print(sqlTxt) call_sql(con, sqlTxt, datums, 'executeBatch') if con: con.close()
def get_all_for_subcat(self): """ Gets all items' data for a subcategory and writes it to SQL """ numSearches = 0 while True: thisSearch = SearchJSON(startIndex=(numSearches * 25) + 1, subCat=self.subCat) thisSearch.api_search() totalRslts, errFlag, errVal = thisSearch.prep_data() if totalRslts and not self.totalResults: self.totalResults = totalRslts self.process_errors(errFlag, errVal) if self.internetConnection: numSearches += 1 if errFlag or not self.totalResults: break else: searchData = thisSearch.parse_data() thisSearch.write_to_sql(searchData) searchData = None if numSearches * 25 >= self.totalResults or numSearches * 25 >= 1000: break con = con_postgres() if not self.status["errors"]: # No errors were returned by the API # All successful: success. All failed: failed. Some of each: partial. Something else: <error>. if self.status["failures"] > 0: if self.status["successes"] > 0: success = "partial" else: success = "failed" elif self.status["successes"] > 0: success = "success" else: success = "none" else: success = ','.join(map(str, self.status["errors"]) ) # Combine all the error codes into a string update_wm_query_log(num=numSearches) # Update WmTaxo_Updated sqlTxt = '''UPDATE "WmTaxo_Updated" SET success = %s WHERE full_id = %s''' theData = [success, self.subCat] call_sql(con, sqlTxt, theData, "executeNoReturn") if self.totalResults > -1: sqlTxt = '''UPDATE "WmTaxo_Updated" SET last_searched = %s, num_items = %s WHERE full_id = %s''' theData = [ datetime_floor(1.0 / 60), self.totalResults, self.subCat ] call_sql(con, sqlTxt, theData, "executeNoReturn") if con: con.close()
def append_taxo(self): """ Write taxonomy to SQL. The UUID is used to tell which subcats are no longer active, since they won't have the most recent update_uuid. """ root = ET.ElementTree(ET.fromstring( self.taxoxml.encode('utf-8'))).getroot() updateuuid = psycopg2.extras.UUID_adapter(uuid.uuid4()) theData = [] con = con_postgres() for dept in root.findall("category"): for cats in dept.findall("children"): for cat in cats.findall("category"): for subCats in cat.findall("children"): for subCat in subCats.findall("category"): full_id = subCat.find("id").text dept_id = int(dept.find("id").text.split('_')[0]) dept_name = dept.find("name").text cat_id = int(cat.find("id").text.split('_')[1]) cat_name = cat.find("name").text subCat_id = int( subCat.find("id").text.split('_')[2]) subCat_name = subCat.find("name").text theData.append([ full_id, dept_id, dept_name, cat_id, cat_name, subCat_id, subCat_name, updateuuid, datetime.date.today(), # ON CONFLICT DO UPDATE values start here dept_name, cat_name, subCat_name, updateuuid ]) sqlTxt = '''INSERT INTO "WmTaxo_Updated" (full_id, dept_id, dept_name, cat_id, cat_name, subcat_id, subcat_name, update_uuid, birthdate) VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s) ON CONFLICT (full_id) DO UPDATE SET dept_name = %s, cat_name = %s, subcat_name = %s, update_uuid = %s''' call_sql(con, sqlTxt, theData, "executeBatch") # Set "active" column in WmTaxo_Updated to True when that row's uuid matches the new one sqlTxt = '''UPDATE "WmTaxo_Updated" SET active = True WHERE update_uuid = %s; UPDATE "WmTaxo_Updated" SET active = False WHERE update_uuid != %s;''' call_sql(con, sqlTxt, [updateuuid, updateuuid], "executeNoReturn") if con: con.close()
def wm_db_query(items): """ Retrieve data for the Walmart products from SQL If <items> is a list, this function will assume it's a list of wm_id's If <items> is a single integer, this function will match that many items from the Walmart table Returns a list of dictionaries with the keys {wm_id, name, price, upc, in_stock, free_ship} """ con = con_postgres() wmDicts = [] go = False if items: if isinstance(items, int): go = True isList = False elif isinstance(items, list) or isinstance(items, tuple): if isinstance(items[0], int): go = True isList = True if go: # SELECT requests using this returns a list of dicts, with columns names as the keys dict_cur = con.cursor(cursor_factory=psycopg2.extras.RealDictCursor) sqlTxt = '''SELECT wm_id, name, price, upc, model, brand, in_stock, free_ship FROM "Prod_Wm" ''' # Sort out <items> depending on what type it is if isList: # Write the wm_id's out into the WHERE clause of a SQL SELECT statement sqlSnippet = 'WHERE' for wm_id in items: sqlSnippet = sqlSnippet + " (wm_id = '" + str(wm_id) + "') OR" sqlSnippet = sqlSnippet[:-3] # Remove the last 3 characters: ' or' else: # Write the SQL bit to get the n most pertinent items sqlSnippet = 'ORDER BY fetched ASC FETCH FIRST %s ROWS ONLY' % items dict_cur.execute(sqlTxt + sqlSnippet) wmDicts = dict_cur.fetchall() # List of dicts # Some error-logging if not wmDicts or not items: print("----------") if not wmDicts: # No matches for the input wm_ids were found print( "Error: walmartclasses.wm_db_query didn't find any matches for its wm_ids, which were: {}" .format(items)) if not items: # No input wm_ids were given print( "Error: walmartclasses.wm_db_query wasn't given any wm_ids in its parameters" ) print("----------") elif isinstance(items, list): if len(wmDicts) != len( items ): # Discrepency between the number of wm_ids input and output print("----------") wmDictsIds = [a["wm_id"] for a in wmDicts] wmDictsOnly = list( set(wmDictsIds) - set(items)) # wm_ids that are in in wmDicts, but not items itemsOnly = list( set(items) - set(wmDictsIds)) # wm_ids that are in items, but not wmDicts print( "Error: walmartclasses.wm_db_query either returned extra wm_ids, or didn't find a match for each " "wm_id fed to it.") print("Extra wm_ids returned: {}".format(wmDictsOnly)) print("wm_ids that were fed but didn't find a match: {}".format( itemsOnly)) print("----------") for j in range( 0, len(wmDicts) ): # Convert some text values to boolean, and add some entries if wmDicts[j]['in_stock'].lower() == 'available': wmDicts[j]['in_stock'] = 'true' else: wmDicts[j]['in_stock'] = 'false' if con: con.close() return wmDicts
def displaydata_expandrows_old(): """ Scans the active sheet for asins or wm_ids that were added manually, i.e. have no other data in their rows. Fills in the what empty columns it can from Products_WmAz, and additionally from io.Manual if the active sheet is manualSh. """ # shtName = xw.Range('A1').sheet.name fullRange = xw.Range('A1').current_region headersRow = fullRange.rows(1) datumsRange = fullRange.resize(fullRange.rows.count - 1).offset(1, 0) asinsCol = datumsRange.columns(headersRow.value.index('asin') + 1) wmidCol = datumsRange.columns(headersRow.value.index('wm_id') + 1) con = con_postgres() # Get column names from io.Manual sqlTxt = """SELECT column_name FROM information_schema.columns WHERE table_name = 'Manual'""" availCols1 = [y[0] for y in call_sql(con, sqlTxt, [], 'executeReturn') ] # List of column names in io.Manual cols1 = [y for y in availCols1 if y in headersRow.value] formatters1 = ", ".join(cols1) # Get column names from Products_WmAz sqlTxt = """SELECT column_name FROM information_schema.columns WHERE table_name = 'Products_WmAz'""" availCols2 = [y[0] for y in call_sql(con, sqlTxt, [], 'executeReturn') ] # List of column names in Products_WmAz cols2 = [y for y in availCols2 if y in headersRow.value and y not in cols1] formatters2 = ", ".join(cols2) sqlTxt0 = '''SELECT asin FROM io."Purchased" WHERE wm_id = %s''' sqlTxt2 = '''SELECT {} FROM io."Manual" WHERE asin = %s'''.format(formatters1) sqlTxt1 = '''SELECT {} FROM "Products_WmAz" WHERE asin = %s'''.format(formatters2) for i in range(1, datumsRange.rows.count + 1): # This row has at most 2 filled-in columns (assumed to be asin or wm_id) if sum(xcv is not None for xcv in datumsRange.rows(i).value) <= 2: # If wm_id is filled in, but not asin, get the asin from io.Purchased if wmidCol(i).value and not asinsCol(i).value: asinsCol(i).formula = call_sql(con, sqlTxt0, [wmidCol(i).value], 'executeReturn')[0] # Now, get the rest of the columns from Products_WmAz (and possibly io.Manual) using the asin try: # Fetch data for this asin from Products_WmAz aa = call_sql(con, sqlTxt1, [asinsCol(i).value], 'executeReturn', dictCur=True) a = aa[0] # Fetch data for this asin from io.Manual except IndexError: # No asins need to be expanded a = None try: b = call_sql(con, sqlTxt2, [asinsCol(i).value], 'executeReturn', dictCur=True)[0] except IndexError: # No asins need to be expanded b = None # Put the values from SQL into purchasedSh for j in headersRow: header = j.value if a: if header in a.keys(): # and shtName == 'Manual': if a[header]: datumsRange(i, j.column).formula = str(a[header]) if b: if header in b.keys(): if b[header]: datumsRange(i, j.column).formula = str(b[header]) if con: con.close()
def displaydata_expandrows(): """ Scans the active sheet (will only actually activate on purchasedSh) for asins or wm_ids that were added manually, i.e. have no other data in their rows. Fills in the what empty columns it can from Products_WmAz, and additionally from io.SKUs to get thecolumn <my_name>. """ # shtName = xw.Range('A1').sheet.name fullRange = xw.Range('A1').current_region headersRow = fullRange.rows(1) datumsRange = fullRange.resize(fullRange.rows.count - 1).offset(1, 0) asinCol = datumsRange.columns(headersRow.value.index('asin') + 1) wmidCol = datumsRange.columns(headersRow.value.index('wm_id') + 1) skuCol = datumsRange.columns(headersRow.value.index('sku') + 1) con = con_postgres() sqlTxt = '''SELECT * FROM "{}" WHERE {} = %s''' for i in range(1, datumsRange.rows.count + 1): # This row has at most 2 filled-in columns (assumed to be asin or wm_id) if sum(xcv is not None for xcv in datumsRange.rows(i).value) <= 2: # If wm_id is filled in, but not asin, get the asin from Products_WmAz if wmidCol(i).value and not asinCol(i).value: asinCol(i).formula = call_sql( con, 'SELECT asin FROM "Products_WmAz" WHERE wm_id = %s', [wmidCol(i).value], 'executeReturn')[0] # If sku is filled in, but not asin, get the asin from io.SKUs elif skuCol(i).value and not asinCol(i).value: asinCol(i).formula = call_sql( con, 'SELECT asin FROM io."SKUs" WHERE sku = %s', [skuCol(i).value], 'executeReturn')[0] # Now, get the rest of the columns from Products_WmAz using the asin a, b = (None for _ in range(2)) try: a = call_sql(con, sqlTxt.format('Products_WmAz', 'asin'), [asinCol(i).value], 'executeReturn', dictCur=True)[0] except IndexError: # No asins need to be expanded pass try: b = call_sql(con, sqlTxt.format('SKUs', 'sku'), [skuCol(i).value], 'executeReturn', dictCur=True)[0] except IndexError: # No asins need to be expanded pass # Put the values from SQL into purchasedSh headerVals = headersRow.value for j, header in enumerate(headerVals): if a: if header in a.keys(): if a[header]: datumsRange(i, j + 1).formula = str(a[header]) elif header == 'name': # If io.SKUs has a value for my_name, use that. Else use az_name (again) if b: if b['my_name']: datumsRange(i, j + 1).formula = str(b['my_name']) elif a['az_name']: datumsRange(i, j + 1).formula = str(a['az_name']) elif a['az_name']: datumsRange(i, j + 1).formula = str(a['az_name']) if con: con.close()