def pipe_power_index():
cursor = database_connection()
for each in POWER_CONFIG:
if each == 'Alpha':
print(f"Pulling {POWER_CONFIG[each]} from {each} stats")
powerArray = generate_index_history(setCheck=each,
setId=1,
boolCheck=POWER_CONFIG[each])
if len(powerArray) > 0:
print(f"Piping nested arrays")
insert_index(cursor=cursor, mtgArray=powerArray)
elif each == 'Beta':
print(f"Pulling {POWER_CONFIG[each]} from {each} stats")
powerArray = generate_index_history(setCheck=each,
setId=2,
boolCheck=POWER_CONFIG[each])
if len(powerArray) > 0:
print(f"Piping nested arrays")
insert_index(cursor=cursor, mtgArray=powerArray)
elif each == 'Unlimited':
print(f"Pulling {POWER_CONFIG[each]} from {each} stats")
powerArray = generate_index_history(setCheck=each,
setId=3,
boolCheck=POWER_CONFIG[each])
if len(powerArray) > 0:
print(f"Piping nested arrays")
insert_index(cursor=cursor, mtgArray=powerArray)
def pipe_duals_index():
cursor = database_connection()
for each in DUALS_CONFIG:
if each == 'Alpha':
print(f"Forming {DUALS_CONFIG[each]} index from {each} stats")
dualsArray = generate_index_history(setCheck=each,
setId=4,
boolCheck=DUALS_CONFIG[each])
if len(dualsArray) > 0:
print(f"Piping nested arrays")
insert_index(cursor=cursor, mtgArray=dualsArray)
elif each == 'Beta':
print(f"Forming {DUALS_CONFIG[each]} index from {each} stats")
dualsArray = generate_index_history(setCheck=each,
setId=5,
boolCheck=DUALS_CONFIG[each])
if len(dualsArray) > 0:
print(f"Piping nested arrays")
insert_index(cursor=cursor, mtgArray=dualsArray)
elif each == 'Unlimited':
print(f"Forming {DUALS_CONFIG[each]} index from {each} stats")
dualsArray = generate_index_history(setCheck=each,
setId=6,
boolCheck=DUALS_CONFIG[each])
if len(dualsArray) > 0:
print(f"Piping nested arrays")
insert_index(cursor=cursor, mtgArray=dualsArray)
elif each == 'Revised':
print(f"Forming {DUALS_CONFIG[each]} index from {each} stats")
dualsArray = generate_index_history(setCheck=each,
setId=7,
boolCheck=DUALS_CONFIG[each])
if len(dualsArray) > 0:
print(f"Piping nested arrays")
insert_index(cursor=cursor, mtgArray=dualsArray)
def __init__(self, api_key, keyword):
self.api_key, self.keyword = api_key, keyword
# defin which site we wish to connect to and feed in our api-key
self.api = Connection(siteid='EBAY-US',
appid=self.api_key,
config_file=None)
# create a live db cursor
self.cursor = database_connection()
# establish lists for appending data to
self.active_product_ids = []
self.active_product_nick = []
self.active_product_titles = []
self.active_product_prices = []
self.active_product_cat_names = []
self.active_product_cat_ids = []
self.active_product_img_thumb = []
self.active_product_img_url = []
self.active_product_lst_type = []
self.active_product_con = []
self.active_product_loc = []
self.active_product_start = []
self.active_product_end = []
self.active_product_watch_count = []
self.active_product_depth = []
self.depthCountStorage = []
# outline our search body paramaters
self.search_body_pages = {
'keywords':
keyword,
'itemFilter': [
# US only sellers -- can also limit by feedback score, business type, top-rated status, charity, etc.
{
'name': 'MinPrice',
'value': '1',
'paramName': 'Currency',
'paramValue': 'USD'
},
{
'name': 'MaxPrice',
'value': '99999999',
'paramName': 'Currency',
'paramValue': 'USD'
},
# pre-filter to only actionable items (non-bids, etc.)
{
'name': 'ListingType',
'value':
['FixedPrice', 'StoreInventory', 'AuctionWithBIN']
},
],
'paginationInput': {
'entriesPerPage': '100',
# always 1, as we want to pull the maximum number of pages given a maximum of 100 results per page
'pageNumber': '1'
},
# can filter this to multiple different options as well (Best Offer, Most Watched, etc.)
'sortOrder':
'PricePlusShippingLowest'
}
def pipe_power_stats():
cursor = database_connection()
for each in POWER_CONFIG:
print(f"Pulling {POWER_CONFIG[each]} from {each}")
powerArray = generate_stat_history(setCheck=each, historyRange=91, boolCheck=POWER_CONFIG[each])
if len(powerArray) > 0:
print(f"Piping nested arrays")
insert_stats(cursor=cursor, mtgArray=powerArray)
def pipe_duals_stats():
# generate `cursor` (used to execute db queries)
cursor = database_connection()
# iterate over `DUALS_CONFIG` and pipe each nested array.
for each in DUALS_CONFIG:
print(f"Pulling {DUALS_CONFIG[each]} from {each}")
dualsArray = generate_stat_history(setCheck=each, historyRange=91, boolCheck=DUALS_CONFIG[each])
if len(dualsArray) > 0:
print(f"Piping nested arrays")
insert_stats(cursor=cursor, mtgArray=dualsArray)
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()
# words = ['Revised Tundra MTG']
for value in words:
print(f'Pruning {value}....')
prune_completed(value, cursor)
print('-------------------------------------')
print('Succesfully pruned completed_products')
print('-------------------------------------')
if __name__ == '__main__':
inputCheck = input(
'Beginning once-a-day batch calc script -- are you sure you want to proceed?: '
)
if inputCheck in ('Y', 'y'):
print('I understand. Beggining once-a-day batch script.')
prune_db(cursor=database_connection())
print()
# begin piping stats
pipe_power_stats()
print()
pipe_duals_stats()
print()
# begin piping index
pipe_power_index()
print()
pipe_duals_index()
print()
print('Batch process completed. Data has been successfully inserted.')
elif inputCheck in ('N', 'n'):
print('Exiting batch process.')
