def deploy_to_database(self): db = MySQLDatabase() for product in self.products: db._insert_query(product) for update in self.updates: db._update_query(update) db.close()
def _calc_tmp_provider_tbl(self): query = """ SELECT * FROM `tbl_provider_scans` WHERE DATEDIFF(NOW(), scan_date) < 5 ORDER BY `scan_date` ASC """ tmp_deals_l7d = self._select_query(query) deals = [] for tmp_deal in self._select_query( "SELECT * FROM `tbl_provider_scans` JOIN tmp_latest_scan_ids USING(scan_id)" ): deal = dict(tmp_deal) price_range = [ _['price'] for _ in tmp_deals_l7d if _['provider'] == deal['provider'] and _['set_number'] == deal['set_number'] ] max_count_price = max(set(price_range), key=price_range.count) price_change_l7d = int(tmp_deal['price'] - max_count_price) # print('{} has a price change of {} CHF.'.format(tmp_deal['set_number'], price_change_l7d)) deal.update({'price_change_l7d': price_change_l7d}) deals.append(deal) db = MySQLDatabase() self._execute_query("DELETE FROM tmp_deals_tmp") for deal in deals: payload = { 'table_name': 'tmp_deals_tmp', 'data': { 'set_number': deal['set_number'], 'title': deal['title'], 'url': deal['url'], 'price': deal['price'], 'currency': deal['currency'], 'provider': deal['provider'], 'availability': deal['availability'], 'scan_date': deal['scan_date'], 'scan_id': deal['scan_id'], 'price_change_l7d': deal['price_change_l7d'] } } db._insert_query(payload) self._execute_query("DELETE FROM tmp_deals") self._execute_query( "INSERT INTO tmp_deals SELECT * FROM tmp_deals_tmp")
def deploy_to_database(self): db = MySQLDatabase() for product in self.products: logging.debug('[DEPLOY] {}'.format(product)) db._insert_query(product) db.close()
set_number)) payload = { 'table_name': 'tbl_sets', 'data': { 'id': id, 'set_number': set_number, 'variant': variant, 'theme': theme, 'subtheme': subtheme, 'year': year, 'name': name, 'minifigs': minifigs, 'pieces': pieces, 'uk_price': uk_price, 'us_price': us_price, 'ca_price': ca_price, 'eu_price': eu_price, 'ch_price': ch_price, 'image_url': image_url, 'owned_by': owned_by, 'wanted_by': wanted_by, 'i_want': i_want, 'i_have': i_have } } db._insert_query(payload) else: logging.error(r) q._create_tmp_newest_bricklink_prices()
class Bricklink(): def __init__(self, set_number, condition='new'): self._url = 'http://www.bricklink.com/catalogPG.asp?S={}-1&ColorID=0'.format( set_number) self._headers = { 'User-Agent': 'Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/80.0.3987.132 Safari/537.36' } self.condition = condition self.prices = {} self.set_number = set_number self.scan_date = datetime.now() self.exchange_rates = {} self.toCurrency = 'CHF' self.result = {} self.db = MySQLDatabase() self.q = Queries() self._parse_prices() def _get_soup(self): while True: try: logging.debug("[BLP|{}] Requesting prices ...".format( self.set_number)) r = requests.get(self._url, headers=self._headers) soup = BeautifulSoup(r.content, "html.parser") return soup except Exception as e: logging.error("[BLP|{}] {} ...".format(self.set_number, e)) return False def _parse_prices(self): if not self.in_database: logging.debug("[BLP|{}] Not found in database ...".format( self.set_number)) if self.is_valid: logging.debug("[BLP|{}] Is valid, parsing prices ...".format( self.set_number)) try: price_table = self._soup.find('tr', { 'bgcolor': '#C0C0C0' }).find_all('td', {'valign': 'TOP'}) if len(price_table) == 4: self.prices['new'] = self._prices(price_table[0]) self.prices['used'] = self._prices(price_table[1]) self._deploy_to_database() self.result['status'] = 'success' self.result['source'] = 'bricklink' self.result['data'] = self.prices except Exception as e: logging.warning('[BLP|{}] {}'.format(self.set_number, e)) self.result['status'] = 'error' self.result['msg'] = e else: logging.debug("[BLP|{}] Found in database ...".format( self.set_number)) for c in ['new', 'used']: data = self.q.get_bricklink_price_for_set(self.set_number, c, age=7) p = [p for p in data] self.prices[c] = p[0] self.result['status'] = 'success' self.result['source'] = 'database' self.result['data'] = self.prices def _prices(self, table): prices = {} prices['times_sold'] = -1 prices['total_qty'] = -1 prices['min_price'] = -1 prices['avg_price'] = -1 prices['qty_avg_price'] = -1 prices['max_price'] = -1 try: prices['times_sold'] = table.find( 'td', text=re.compile('^Times Sold:.*')).find_next('td').text prices['total_qty'] = table.find( 'td', text=re.compile('^Total Qty:.*')).find_next('td').text prices['min_price'] = self._clean_and_convert_price( table.find( 'td', text=re.compile('^Min Price:.*')).find_next('td').text) prices['avg_price'] = self._clean_and_convert_price( table.find( 'td', text=re.compile('^Avg Price:.*')).find_next('td').text) prices['qty_avg_price'] = self._clean_and_convert_price( table.find( 'td', text=re.compile('^Qty Avg Price:.*')).find_next('td').text) prices['max_price'] = self._clean_and_convert_price( table.find( 'td', text=re.compile('^Max Price:.*')).find_next('td').text) except Exception as e: logging.debug("[BLP|PARSE_PRICE_TABLES|{}] {}".format( self.set_number, e)) return prices return prices def _deploy_to_database(self): for c in ['new', 'used']: # set_number, product_condition, times_sold, total_qty, min_price, avg_price, qty_avg_price, max_price, scan_date payload = { 'table_name': 'tbl_bricklink_prices', 'data': { 'set_number': self.set_number, 'product_condition': c, 'times_sold': self.prices[c]['times_sold'], 'total_qty': self.prices[c]['total_qty'], 'min_price': self.prices[c]['min_price'], 'avg_price': self.prices[c]['avg_price'], 'qty_avg_price': self.prices[c]['qty_avg_price'], 'max_price': self.prices[c]['max_price'], 'scan_date': self.scan_date } } self.db._insert_query(payload) def _clean_and_convert_price(self, price): currency = ''.join(re.findall(r'[A-Z]{3}', price)) exchangeRate = self._get_currency_exchange_rate(baseCurrency=currency) price = float(''.join(re.findall(r'[0-9,.]', price)).replace(',', '')) price = round(price * exchangeRate, 2) return price # TWD not yet supported def _get_currency_exchange_rate(self, baseCurrency): if baseCurrency == 'TWD': logging.debug( "[BLP|_GET_CURRENCY_EXCHANGE_RATE] Returning hardcoded TWD rate 0.031 ..." ) return 0.031 if baseCurrency in self.exchange_rates: return self.exchange_rates[baseCurrency] else: url = "https://api.exchangeratesapi.io/latest?base={}" url = url.format(baseCurrency) r = requests.get(url, headers=self._headers) r_json = r.json() rate = r_json['rates'][self.toCurrency] self.exchange_rates[baseCurrency] = rate logging.debug( "[BLP|_GET_CURRENCY_EXCHANGE_RATE] {} has rate {} ...".format( baseCurrency, self.exchange_rates[baseCurrency])) return rate @property def in_database(self): for c in ['new', 'used']: data = self.q.get_bricklink_price_for_set(self.set_number, c, age=3) data = [d for d in data] if not data: return False return True @property def is_valid(self): self._soup = self._get_soup() body = self._soup.find('body').text if 'No Item(s) were found. Please try again!' in body: self.result['status'] = 'error' self.result['msg'] = 'NoItemsFound' logging.debug("[BLP|{}] {} ...".format(self.set_number, self.result['msg'])) return False if 'Quota Exceeded' in body: self.result['status'] = 'error' self.result['msg'] = 'QuotaExceeded' logging.debug("[BLP|{}] {} ...".format(self.set_number, self.result['msg'])) return False if 'HTTP Error 404' in body: self.result['status'] = 'error' self.result['msg'] = 'HTTP404' logging.debug("[BLP|{}] {} ...".format(self.set_number, self.result['msg'])) return False if 'System Unavailable' in body: self.result['status'] = 'error' self.result['msg'] = 'SystemUnavailable' logging.debug("[BLP|{}] {} ...".format(self.set_number, self.result['msg'])) return False if 'Qty Avg Price' not in body and 'Times Sold' not in body: self.result['status'] = 'error' self.result['msg'] = 'NoPriceChartAvailable' logging.debug("[BLP|{}] {} ...".format(self.set_number, self.result['msg'])) return False logging.debug("[BLP|{}] Is a valid product ...".format( self.set_number)) return True