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()
