def __init__(self):
self.dbhost = rj.get_db_host()
self.dbuser = rj.get_db_user()
self.dbpwd = rj.get_db_pwd()
self.dbport = rj.get_db_port()
self.dbname = rj.get_db_name()
self.db = MySQLDatabase(self.dbuser, self.dbpwd, self.dbname, self.dbhost, self.dbport)
self.Session = self.db.mysql_session()
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 main():
"""
Imports an XLS file of Victor exported results.
The Experiment ID is determined by the time-stamp of the first measurement.
"""
options = MakeOpts().parse_args()
if options.debug:
db = CreateDummyDB()
else:
db = MySQLDatabase(host=options.host,
user='******',
port=3306,
passwd='a1a1a1',
db='tecan')
if not os.path.exists(options.xls_file):
print "File not found: " + options.xls_file
sys.exit(-1)
print "Importing from file: " + options.xls_file
fp = open(options.xls_file, 'r')
exp_id = VictorParser.ImportFileToDB(fp, db, options.exp_id)
if options.debug:
print "Done, go check out the results at %s" % db.filename
else:
print "Done, go check out the results at http://eladpc1/RoboSite/Exp/%s/0" % exp_id
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 main():
dbuser = rj.get_db_user(); dbpwd = rj.get_db_pwd()
dbhost = rj.get_db_host(); dbname = rj.get_db_name()
dbport = rj.get_db_port(); dbtables = rj.get_db_tables()
sender = rj.get_sender(); smtphost = rj.get_smtp_host()
recipient = rj.get_recipient()
db = MySQLDatabase(dbuser, dbpwd, dbname, dbhost, dbport)
Session = db.mysql_session()
files_list = StorageFilesList().create_selected_list()
for file_path in files_list:
cksgz_stg = md5Checksum(file_path).calculate_checksum()
cks_stg = md5Checksum(file_path).get_checksum_gz()
head, fname_gz = os.path.split(file_path)
for tbl in dbtables:
db_element = db_query(tbl, Session, fname_gz)
if db_element is not None:
cks_db = db_element[1]
cksgz_db = db_element[2]
if cks_db is not None and cksgz_db is not None:
if cksgz_stg == cksgz_db and cks_stg == cks_db:
try:
print(file_path)
#os.remove(file_path)
except Exception as e:
msg = "File removal exception --"
log.error("{0}{1}".format(msg,e))
else:
message = 'Severe alert - storage and DB file checksums DO NOT MATCH'
SendEmail(message,recipient,smtphost,sender).send_email()
else:
message = 'Severe alert - checksum not calculated'
SendEmail(message,recipient,smtphost,sender).send_email()
else:
pass
db.close_session()
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")
class SelectEventString(object):
def __init__(self):
self.dbhost = rj.get_db_host()
self.dbuser = rj.get_db_user()
self.dbpwd = rj.get_db_pwd()
self.dbport = rj.get_db_port()
self.dbname = rj.get_db_name()
self.db = MySQLDatabase(self.dbuser, self.dbpwd, self.dbname, self.dbhost, self.dbport)
self.Session = self.db.mysql_session()
def _check_event_string(self):
'''Check the event string matches roughly YYYYMMDDTHHMMSS format'''
try:
event_folders = glob(rsync_path + '/*')
event_strings = [os.path.basename(i)[0:15] for i in event_folders]
output_list = []
for i in event_strings:
if i[0].isdigit() and i[8] == 'T':
output_list.append(i)
else:
pass
return output_list
except Exception as e:
msg = "String event selection excep - CheckEventString._check_event_string --"
log.error("{0}{1}".format(msg,e))
def _filter_event(self, event_string):
'''Check if an event string is found in database'''
try:
rows = Queries(self.Session, DataFile, event_string).match_event()
if not rows:
return event_string
except Exception as e:
msg = "Query on database excep - SelectEventString._filter_event --"
log.error("{0}{1}".format(msg,e))
def get_selected_events_list(self):
'''Create list of event strings with YYYYMMDDTHHMMSS format and not found in database'''
try:
checked_list = self._check_event_string()
selected_list = []
for i in checked_list:
if self._filter_event(i) is not None:
selected_list.append(self._filter_event(i))
return selected_list
except Exception as e:
msg = "Creation of events already in db excep - SelectEventString.get_selected_events_list --"
log.error("{0}{1}".format(msg,e))
def main():
options = MakeOpts().parse_args()
if options.debug:
db = CreateDummyDB()
else:
db = MySQLDatabase(host=options.host,
user='******',
port=3306,
passwd='a1a1a1',
db='tecan')
if options.xml_dir:
if not os.path.exists(options.xml_dir):
print "Directory not found: " + options.xml_dir
sys.exit(-1)
xml_fname = GetLatestFile(options.xml_dir)
else:
xml_fname = options.xml_filename
if not os.path.exists(xml_fname):
print "File not found: " + xml_fname
sys.exit(-1)
print "Importing from file: " + xml_fname
header_dom, script_dom, plate_values = tecan.ParseReaderFile(xml_fname)
exp_id = GetExperimentID(options, db, header_dom, script_dom)
print "Experiment ID: " + exp_id
plate_id = options.iteration % options.num_plates
print "Plate ID: %d" % plate_id
MES = {plate_id: plate_values}
tecan.WriteToDatabase(MES, db, exp_id)
db.Commit()
print "Done!"
sys.exit(0)
def save_to_db(self, df):
db = MySQLDatabase()
db.write(df)
from database import MySQLDatabase
logging.basicConfig(
filename='logs/{}_eol_sets.log'.format(datetime.now().strftime('%Y%m%d')),
filemode='a',
format='%(asctime)s:%(levelname)s:%(funcName)s:%(message)s',
level=logging.INFO)
def _get_set_numbers_from_string(string):
set_numbers = re.findall(r'([0-9]{7}|[0-9]{4,5})', string)
set_numbers = list(dict.fromkeys(set_numbers))
return set_numbers
db = MySQLDatabase()
ps = ProductScanner()
set_number = 0
eol_url = 'https://www.stonewars.de/news/lego-end-of-life-eol-2020/'
soup = ps._get_soup(eol_url, headers={})
eol_sets_raw = [
_get_set_numbers_from_string(_.text)
for _ in soup.find_all('td', {'class': 'column-2'})
]
eol_sets = [item for sublist in eol_sets_raw for item in sublist]
for set_number in eol_sets:
logging.info(
import requests
import logging
from datetime import datetime
from database import MySQLDatabase
from queries import Queries
from config import _config
logging.basicConfig(
filename='logs/{}_update_sets.log'.format(
datetime.now().strftime('%Y%m%d')),
filemode='a',
format='%(asctime)s:%(levelname)s:%(funcName)s:%(message)s',
level=logging.INFO)
db = MySQLDatabase()
q = Queries()
for year in range((datetime.now().year) - 1, (datetime.now().year) + 2):
y = str(year)
for page in range(1, 10):
logging.info("Requesting year {} page {} ... ".format(year, page))
p = str(page)
data = {
'apiKey':
_config['brickset']['apikey'],
'userHash':
_config['brickset']['userhash'],
'params':
"{ 'year':'" + y +
"', 'orderBy':'Pieces', 'pageSize':200, 'pageNumber':" + p + " }"
from config import _config
from queries import Queries
from database import MySQLDatabase
from send_mail import send_mail
q = Queries()
db = MySQLDatabase()
for row in q.get_subscriptions():
entries = q.get_current_prices_for_set(row['set_number'])
low_prices = [r for r in entries if r['price'] < row['price_treshold']]
if low_prices:
for d in low_prices:
mail_body = "Set: https://svenborer.ch/lego-priisvrgliich/set/{} {}\nThema: {}/{}\nStei/Minifigure: {}/{}\nPriis: {} CHF\nUVP: {} CHF\nTreshold: {} CHF\nRabatt: {}%\nAhbieter: {}\nURL: {}"
body = mail_body.format(d['set_number'], d['name'], d['theme'],
d['subtheme'], d['pieces'], d['minifigs'],
d['price'], d['ch_price'],
row['price_treshold'],
round(d['save_in_percentage_lp'],
1), d['provider'], d['url'])
subject = '[L-PVG-P-S] {}|{}'.format(d['set_number'], d['name'])
to = row['email']
send_mail(to, subject, body)
payload = {
'table_name': 'tbl_subscriptions',
'data': {
'notified': 1
},
'condition': {
'id': row['id']
}
def connect():
return MySQLDatabase(host='hldbv02',
user='******',
passwd='a1a1a1',
db='tecan')
"""
from config import _config
from queries import Queries
from database import MySQLDatabase
from send_mail import send_mail
q = Queries()
db = MySQLDatabase()
all_sets = q.get_current_prices_for_set()
subscription_history = q.get_subscriptions_theme_history()
for row in q.get_subscriptions_theme():
low_prices = [
_ for _ in all_sets
if _['theme'] == row['theme'] and _['save_in_percentage_lp']
and _['save_in_percentage_lp'] >= row['save_treshold']
]
low_prices_sorted = reversed(
sorted(low_prices, key=lambda k: k['save_in_percentage_lp']))
if low_prices_sorted:
for d in low_prices_sorted:
sent = [
h for h in subscription_history
if row['id'] == h['subscriptions_theme_id']
and d['url'] == h['url'] and d['price'] >= h['price']
]
if not sent:
mail_body = "Set: https://svenborer.ch/lego-priisvrgliich/set/{} {}\nThema: {}/{}\nStei/Minifigure: {}/{}\nPriis: {} CHF\nUVP: {} CHF\nRabatt: {}%\nAhbieter: {}\nURL: {}"
body = mail_body.format(d['set_number'], d['name'], d['theme'],
d['subtheme'], d['pieces'],
d['minifigs'], d['price'],
def __init__(self):
self.db = MySQLDatabase()
from read_json import ReadJson
from multiprocessing import Pool
from utilities import LoggingClass
from database import MySQLDatabase
log = LoggingClass('', True).get_logger()
rj = ReadJson()
dbuser = rj.get_db_user()
dbpwd = rj.get_db_pwd()
dbname = rj.get_db_name()
dbhost = rj.get_db_host()
dbport = rj.get_db_port()
thr_nr = rj.get_threads_number()
db = MySQLDatabase(dbuser, dbpwd, dbname, dbhost, dbport)
Session = db.mysql_session()
def _query_filename(filename):
try:
rows = Queries(Session, DataFile, filename).match_filename()
if not rows:
return filename
except Exception as e:
msg = "Query on database excep - _query_filename --"
log.error("{0}{1}".format(msg, e))
finally:
db.close_session()
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
def deploy_to_database(self):
db = MySQLDatabase()
for product in self.products:
logging.debug('[DEPLOY] {}'.format(product))
db._insert_query(product)
db.close()
def read_from_db(self):
global db
db = MySQLDatabase()
df = db.get('bookie')
return df
