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.')