def insert_active_products(self, count):
"""(db cursor, array, count) -> ()
Takes in a database connection (cursor) and an array of data.
Proceeds to delete the old active values in the DB and then insert the contents of the passed in array."""
# only delete on the first iteration (otherwise you will be deleting all of the previous data during each insertion.)
if count == 1:
# begin delete (super raw for now)
self.cursor.execute("DELETE FROM active_products;")
for a, b, c, d, e, f, g, h, i, j, k, l, m, n, o in self.active_products:
try:
self.cursor.execute("""INSERT INTO active_products(active_product_nick, active_product_titles, active_product_ids, active_product_prices, active_product_cat_names, active_product_cat_ids, active_product_img_thumb, active_product_img_url, active_product_lst_type, active_product_con, active_product_loc, active_product_watch_count, active_product_end, active_product_start, active_product_depth)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)""", (a, b, c, d, e, f, g, h, i, j, k, l, m, n, o,)) # MAKE SURE to leave the trailing comma (d-->,<--), this will NOT work otherwise.
print("Unique value inserted...")
except Exception as e:
print("Duplicate value skipped...")
get_trace_and_log(e)
print()
print("Successfully piped database.")
else:
for a, b, c, d, e, f, g, h, i, j, k, l, m, n, o in self.active_products:
try:
self.cursor.execute("""INSERT INTO active_products(active_product_nick, active_product_titles, active_product_ids, active_product_prices, active_product_cat_names, active_product_cat_ids, active_product_img_thumb, active_product_img_url, active_product_lst_type, active_product_con, active_product_loc, active_product_watch_count, active_product_end, active_product_start, active_product_depth)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)""", (a, b, c, d, e, f, g, h, i, j, k, l, m, n, o,)) # MAKE SURE to leave the trailing comma (d-->,<--), this will NOT work otherwise.
print("Unique value inserted...")
except Exception as e:
print("Duplicate value skipped...")
get_trace_and_log(e)
print()
print("Successfully piped database.")
def get_pages(self):
"""() -> dict
Connects to the API,
Executes a query to find items by their category and takes in predefined parameters search_body_pages,
Returns the data in dictionary form,
Returns an integer with the total number of pages.
"""
try:
self.api.execute('findCompletedItems', self.search_body_pages)
self.data = self.api.response.dict()
self.pages = int(self.data['paginationOutput']['totalPages'])
return self.pages
except Exception as e:
get_trace_and_log(e)
def create_tables():
"""() -> executes function
Create series of tables in respective PostgreSQL database."""
commands = (
"""
CREATE TABLE active_products(
primary_ids SERIAL PRIMARY KEY,
active_product_nick VARCHAR(255) NOT NULL,
active_product_titles VARCHAR(255) NOT NULL,
active_product_ids BIGSERIAL NOT NULL UNIQUE,
active_product_prices FLOAT NOT NULL,
active_product_cat_names VARCHAR(255) NOT NULL,
active_product_cat_ids INT NOT NULL,
active_product_img_thumb VARCHAR(255) NOT NULL,
active_product_img_url VARCHAR(255) NOT NULL,
active_product_lst_type VARCHAR(255) NOT NULL,
active_product_watch_count INT NOT NULL,
active_product_con VARCHAR(255) NOT NULL,
active_product_loc VARCHAR(255) NOT NULL,
active_product_start VARCHAR(255) NOT NULL,
active_product_end VARCHAR(255) NOT NULL,
active_product_depth INT NOT NULL,
timestamp timestamp default current_timestamp)
""",
"""
CREATE TABLE completed_products(
primary_ids SERIAL PRIMARY KEY,
completed_product_nick VARCHAR(255) NOT NULL,
completed_product_titles VARCHAR(255) NOT NULL,
completed_product_ids BIGSERIAL NOT NULL UNIQUE,
completed_product_prices FLOAT NOT NULL,
completed_product_cat_names VARCHAR(255) NOT NULL,
completed_product_cat_ids INT NOT NULL,
completed_product_img_thumb VARCHAR(255) NOT NULL,
completed_product_img_url VARCHAR(255) NOT NULL,
completed_product_lst_type VARCHAR(255) NOT NULL,
completed_product_con VARCHAR(255) NOT NULL,
completed_product_loc VARCHAR(255) NOT NULL,
completed_product_start VARCHAR(255) NOT NULL,
completed_product_end VARCHAR(255) NOT NULL,
timestamp timestamp default current_timestamp,
completed_product_depth INT NOT NULL)
""",
"""
CREATE TABLE production_completed_products_stats(
primary_ids SERIAL PRIMARY KEY UNIQUE,
completed_product_nick VARCHAR(255),
completed_product_avg FLOAT,
completed_product_min FLOAT,
completed_product_max FLOAT,
completed_product_depth INT,
completed_product_avg_length FLOAT,
completed_product_sum FLOAT,
timestamp timestamp default current_timestamp)
""",
"""
CREATE TABLE production_completed_products_index(
primary_ids SERIAL PRIMARY KEY UNIQUE,
completed_product_set_name VARCHAR(255),
completed_product_set_id INT,
completed_product_index_avg FLOAT,
completed_product_index_min FLOAT ,
completed_product_index_max FLOAT,
completed_product_index_length_avg FLOAT ,
completed_product_index_count_sum INT,
completed_product_index_sum FLOAT,
timestamp timestamp default current_timestamp)
""",
"""
CREATE TABLE production_active_products_stats(
primary_ids SERIAL PRIMARY KEY UNIQUE,
active_product_nick VARCHAR(255),
active_product_avg FLOAT,
active_product_min FLOAT,
active_product_max FLOAT,
active_product_depth INT,
active_product_avg_length FLOAT,
active_product_sum FLOAT,
timestamp timestamp default current_timestamp)
""",
"""
CREATE TABLE production_active_products_index(
primary_ids SERIAL PRIMARY KEY UNIQUE,
active_product_set_name VARCHAR(255),
active_product_set_id INT,
active_product_index_avg FLOAT,
active_product_index_min FLOAT ,
active_product_index_max FLOAT,
active_product_index_length_avg FLOAT ,
active_product_index_count_sum INT,
active_product_index_sum FLOAT,
timestamp timestamp default current_timestamp)
""",
)
conn = None
try:
cur = database_connection()
try:
cur.execute(commands)
except:
for command in commands:
cur.execute(command)
cur.close()
conn.commit()
except (Exception, psycopg2.DatabaseError) as e:
get_trace_and_log(e)
finally:
if conn is not None:
conn.close()
def fetch_completed_data(self, pages):
"""() -> dict
Connects to the API,
Iterates over each page in the previously established range of 1 -> the total number of pages,
Establishes search_body_data parameters,
Executes a query to find items by their category and takes in predefined parameters search_body_data,
Returns the data in dictionary form,
Iterates over each item in the returned data dictionary and appends the various data points to their respective lists,
Prints the values.
"""
try:
search_body_data = {
'keywords': self.keyword,
'itemFilter': [
{'name': 'MinPrice', 'value': '5', 'paramName': 'Currency', 'paramValue': 'USD'},
{'name': 'MaxPrice', 'value': '99999999', 'paramName': 'Currency', 'paramValue': 'USD'},
# sold items only
{'name': 'SoldItemsOnly', 'value': 'true'},
],
'paginationInput':
{'entriesPerPage': '100',
'pageNumber': f'{page}'},
'sortOrder': 'PricePlusShippingLowest'}
self.api.execute('findCompletedItems', search_body_data)
self.data = self.api.response.dict()
time.sleep(1) # wait a second before continuing (be kind ^^)
except Exception as e:
get_trace_and_log(e)
outliers = [
re.compile(r"\bposter\b", re.I),
re.compile(r"\bproxy\b", re.I),
re.compile(r"\bmisprint\b", re.I),
re.compile(r"\bpuzzle\b", re.I),
re.compile(r"\bplaytest\b", re.I),
re.compile(r"\berror\b", re.I),
re.compile(r"\bpromo\b", re.I),
re.compile(r"\bproxy\b", re.I),
re.compile(r"\bframed\b", re.I),
re.compile(r"\breprint\b", re.I),
re.compile(r"\bbooster\b", re.I),
re.compile(r"\bpack\b", re.I),
re.compile(r"\bfactory sealed\b", re.I),
re.compile(r"\brp\b", re.I),
re.compile(r"\bheadlamp\b", re.I),
re.compile(r"\bheadlamps\b", re.I),
re.compile(r"\bcar\b", re.I),
re.compile(r"\btruck\b", re.I),
re.compile(r"\bheadlights\b", re.I),
re.compile(r"\brepack\b", re.I),
re.compile(r"\brepacks\b", re.I),
re.compile(r"\brubber\b", re.I),
re.compile(r"\bseat\b", re.I),
re.compile(r"\bbox\b", re.I),
re.compile(r'\bsticker\b', re.I),
re.compile(r'\bstickers\b', re.I),
re.compile(r'\b5 x\b', re.I), # used to ignore things like '5 x Mox's for sale..', which greatly skew the average.
re.compile(r'\b4 x\b', re.I),
re.compile(r'\b3 x\b', re.I),
re.compile(r'\b2 x\b', re.I),
re.compile(r'\b5x\b', re.I),
re.compile(r'\b4x\b', re.I),
re.compile(r'\b3x\b', re.I),
re.compile(r'\b2x\b', re.I),
re.compile(r'\bx5\b', re.I),
re.compile(r'\bx4\b', re.I),
re.compile(r'\bx3\b', re.I),
re.compile(r'\bx2\b', re.I),
re.compile(r'\bx-2\b', re.I),
re.compile(r'\bx-3\b', re.I),
re.compile(r'\bx-4\b', re.I),
re.compile(r'\bx-5\b', re.I),
re.compile(r'\bx 2\b', re.I),
re.compile(r'\bx 3\b', re.I),
re.compile(r'\bx 4\b', re.I),
re.compile(r'\bx 5\b', re.I),
re.compile(r'\bcustom\b', re.I),
re.compile(r'\bpractice\b', re.I),
re.compile(r'\btime spiral\b', re.I),
re.compile(r'\blions\b', re.I),
re.compile(r'\bstory\b', re.I),
re.compile(r'\bmullet\b', re.I),
re.compile(r'\bplayset\b', re.I),
re.compile(r'\bbb\b', re.I),
re.compile(r'\bblack border\b', re.I),
re.compile(r'\bartist proof\b', re.I),
re.compile(r'\bgerman\b', re.I),
re.compile(r'\bitalian\b', re.I),
re.compile(r'\bfrench\b', re.I),
re.compile(r'\blot\b', re.I),
re.compile(r'\bsealed\b', re.I),
re.compile(r'\bartist\b', re.I),
re.compile(r'\bproof\b', re.I),
re.compile(r'\bcollection\b', re.I),
re.compile(r'\bfbb\b', re.I),
# re.compile(r'\b2\b', re.I),
# re.compile(r'\b3\b', re.I),
# re.compile(r'\b4\b', re.I),
# re.compile(r'\b5\b', re.I),
# re.compile(r'\b6\b', re.I),
re.compile(r'\bcomplete set\b', re.I),
re.compile(r'\bplayset\b', re.I),
re.compile(r'\bplay-set\b', re.I),
re.compile(r'\bset\b', re.I),
re.compile(r'\b(Partial)\b', re.I),
re.compile(r'\bpartial\b', re.I),
re.compile(r'\binfect\b', re.I),
]
try:
# begin filtering magic :D
if word.split(' ')[0] not in {"Collector's", "International"}:
outliers.extend((
re.compile(r"\bce\b", re.I),
re.compile(r"\bie\b", re.I),
re.compile(r"\bcollector\b", re.I),
re.compile(r"\bcollectors\b", re.I),
re.compile(r"\bcollector's\b", re.I),
re.compile(r"\bcollector's edition\b", re.I),
re.compile(r"\binternational\b", re.I),
re.compile(r"\binternationals\b", re.I),
re.compile(r"\binternational edition\b", re.I),
re.compile(r"\bcollector''s\b", re.I),
re.compile(r'\bcollector"s\b', re.I),
))
else:
pass
# print(f'Searching keyword: {word}', end="")
print(f'Searching keyword: {word}')
print(f'Chugging through...{page}/{self.pages} page(s)...')
print()
depthCount = 0
for item in self.data['searchResult']['item']:
if not any(regex.findall(item['title']) for regex in set(outliers)): # sets provide more iterating efficiency than lists.
# end filter magic => begin appending values to respective arrays
try:
self.completed_product_img_thumb.append(item['galleryURL'])
except Exception as e:
self.completed_product_img_thumb.append('No picture')
self.completed_product_nick.append(word)
self.completed_product_titles.append(item['title'])
self.completed_product_ids.append(item['itemId'])
# completed_product_prices.append(item['sellingStatus']['currentPrice']['value'])
self.completed_product_prices.append(item['sellingStatus']['convertedCurrentPrice']['value']) # take the convertedCurrentPrice instead @ 10/10/2018
self.completed_product_cat_names.append(item['primaryCategory']['categoryName'])
self.completed_product_cat_ids.append(item['primaryCategory']['categoryId'])
self.completed_product_img_url.append(item['viewItemURL'])
self.completed_product_lst_type.append(item['listingInfo']['listingType'])
self.completed_product_con.append(item['condition']['conditionDisplayName'])
self.completed_product_loc.append(item['location'])
self.completed_product_start.append(item['listingInfo']['startTime'])
self.completed_product_end.append(item['listingInfo']['endTime'])
depthCount += 1
# if the page is 1 and the max number of pages is 1 then extend the depth to fill up the list,
# otherwise proceed forward
if self.pages == 1 and page == 1:
self.completed_product_depth.extend(depthCount for i in range(depthCount))
elif self.pages > 1 and page == 1:
self.depthCountStorage.append(depthCount)
else:
depthCountMulti = int(self.depthCountStorage[-1]) + depthCount
self.completed_product_depth.extend(depthCountMulti for i in range(depthCountMulti))
except KeyError as e:
get_trace_and_log(e)
