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
