def add_vendor_product(name, price, currency, sku_id, unknown=False):
"""
add vendor product information to DB(haystack/vendor_product)
:return: 'success'
"""
engine = mysql_engine()
db_session = sessionmaker(bind=engine)
session = db_session()
if not unknown:
is_exist = True if get_product_id(sku_id) else False
if not is_exist:
new_product = VendorProduct(product_name=name,
product_price=price,
currency=currency,
sku_id=sku_id)
session.add(new_product)
session.commit()
else:
new_product = VendorProduct(product_name='unknown',
product_price=0.00,
is_new=-1,
sku_id=sku_id)
session.add(new_product)
session.flush()
new_product_id = new_product.product_id
session.commit()
session.close()
return new_product_id
def get_sites(product_id):
engine = mysql_engine()
db_session = sessionmaker(bind=engine)
session = db_session()
ret = session.query(VendorProduct.product_name).filter(
VendorProduct.product_id == product_id).first()
if not ret:
return
product_name = ret.product_name
ret = session.query(ProductSite).filter(
ProductSite.product_id == product_id).all()
sites = []
for r in ret:
product_site_id = r.product_site_id
site_id = r.site_id
ret_site = session.query(
Site.full_url).filter(Site.site_id == site_id).first()
if not ret_site:
continue
url = ret_site.full_url
site_info = {
'site_id': site_id,
'product_site_id': product_site_id,
'url': url,
'product_name': product_name
}
sites.append(site_info)
session.close()
return sites
def check_site_by_id(site_id):
engine = mysql_engine()
db_session = sessionmaker(bind=engine)
session = db_session()
ret = session.query(Site.site_id).filter(Site.site_id == site_id).first()
session.close()
return ret is not None
def reset_site_status():
engine = mysql_engine()
db_session = sessionmaker(bind=engine)
session = db_session()
session.query(Site).update({Site.status: 0})
session.commit()
session.close()
def add_site(url, product_id):
"""
parse domain, pattern_url, full_url from url
:param url: url
:param product_id: vendor product id
:return: site_id
"""
engine = mysql_engine()
db_session = sessionmaker(bind=engine)
session = db_session()
# db/haystack/site
ret = session.query(Site).filter(Site.full_url == url).first()
if not ret:
pattern = re.compile(url_regex)
url_match = pattern.match(url)
url_domain = url_match.group(3)
new_site = Site(domain=url_domain, full_url=url)
session.add(new_site)
session.commit()
ret = session.query(Site.site_id).filter(Site.full_url == url).first()
site_id = ret.site_id
# db/haystack/product_site
new_product_site = ProductSite(product_id=product_id, site_id=site_id)
session.add(new_product_site)
session.commit()
session.close()
def crawl_data(url, site_id, max_retry_times):
"""
crawl and save url raw data in local
:param url: url
:param site_id: site_id as file name
:param max_retry_times: max retry times
:return: True if success, else False
"""
success_flag = False
if not check_site_by_id(site_id):
return success_flag
current_retry_count = 0
while current_retry_count < max_retry_times:
try:
req_headers = {
'User-Agent':
'Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.0; Trident/4.0)'
}
req = urllib.request.Request(url, headers=req_headers)
response = urllib.request.urlopen(req, timeout=60)
content = response.read()
# Save to file
data_file_path = config.URL_CRAWLED_DATA_DIR + str(
site_id) + '_new'
with open(data_file_path, 'wb') as f:
f.write(content)
# update last indexed time in DB(haystack/site)
engine = mysql_engine()
db_session = sessionmaker(bind=engine)
session = db_session()
session.query(Site).filter(Site.site_id == site_id).update({
Site.last_indexed:
datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
})
session.commit()
session.close()
success_flag = True
break
except urllib.error.HTTPError as e:
print(e.code, e.reason)
current_retry_count += 1
print('Retry:', current_retry_count, '/', max_retry_times)
continue
except urllib.error.URLError as e:
print(e.reason)
current_retry_count += 1
print('Retry:', current_retry_count, '/', max_retry_times)
continue
except ConnectionResetError as e:
print('ConnectionResetError')
time.sleep(random.uniform(0, 2))
current_retry_count += 1
print('Retry:', current_retry_count, '/', max_retry_times)
continue
except Exception as e:
print('Unexpected exception:', str(e))
break
return success_flag
def finish_product_searching(product_id):
engine = mysql_engine()
db_session = sessionmaker(bind=engine)
session = db_session()
session.query(Product).filter(Product.product_id == product_id).update(
{Product.is_searching: 0})
session.commit()
session.close()
def add_product(product_id):
engine = mysql_engine()
db_session = sessionmaker(bind=engine)
session = db_session()
new_product = Product(product_id=product_id)
session.add(new_product)
session.commit()
session.close()
def clear_db():
engine = mysql_engine()
db_session = sessionmaker(bind=engine)
session = db_session()
session.query(Site).delete()
session.commit()
session.query(VendorProduct).delete()
session.commit()
session.close()
def update_product_status(product_id):
engine = mysql_engine()
db_session = sessionmaker(bind=engine)
session = db_session()
session.query(VendorProduct).filter(
VendorProduct.product_id == product_id).update(
{VendorProduct.is_new: 0})
session.commit()
session.close()
def update_site_status(product_site_id):
engine = mysql_engine()
db_session = sessionmaker(bind=engine)
session = db_session()
ret = session.query(ProductSite.site_id).filter(
ProductSite.product_site_id == product_site_id).first()
session.query(Site).filter(Site.site_id == ret.site_id).update(
{Site.status: 1})
session.commit()
session.close()
def get_site_domain(site_id):
engine = mysql_engine()
db_session = sessionmaker(bind=engine)
session = db_session()
ret = session.query(Site.domain).filter(Site.site_id == site_id).first()
if not ret:
return None
domain = ret.domain
session.close()
return domain
def format_currency():
engine = mysql_engine()
db_session = sessionmaker(bind=engine)
session = db_session()
ret = session.query(ProductEvent)
for r in ret:
formatted_currency = currency_formatter(r.currency)
session.query(ProductEvent).filter(ProductEvent.event_id == r.event_id)\
.update({ProductEvent.currency: formatted_currency})
session.commit()
session.close()
def get_status(product_id):
engine = mysql_engine()
db_session = sessionmaker(bind=engine)
session = db_session()
ret1 = session.query(func.count(Site.site_id).label('count'))\
.select_from(Site, ProductSite).filter(ProductSite.site_id == Site.site_id)\
.filter(ProductSite.product_id == product_id).first()
ret2 = session.query(func.count(Site.site_id).label('count'))\
.select_from(Site, ProductSite).filter(ProductSite.site_id == Site.site_id) \
.filter(ProductSite.product_id == product_id).filter(Site.status == 1).first()
return ret1.count, ret2.count
def get_image_urls(product_id):
engine = mysql_engine()
db_session = sessionmaker(bind=engine)
session = db_session()
ret = session.query(ProductImageUrl).filter(
ProductImageUrl.product_id == product_id).all()
image_urls = []
for r in ret:
image_urls.append(r.image_url)
session.close()
return image_urls
def update_site(site_id, site_type, country):
engine = mysql_engine()
db_session = sessionmaker(bind=engine)
session = db_session()
session.query(Site).filter(Site.site_id == site_id).update({
Site.site_type:
site_type,
Site.country:
country
})
session.commit()
session.close()
def set_site_finished(site_id, debug_info):
engine = mysql_engine()
db_session = sessionmaker(bind=engine)
session = db_session()
session.query(Site).filter(Site.site_id == site_id).update({
Site.is_finished:
1,
Site.debug_info:
json.dumps(debug_info)
})
session.commit()
session.close()
def add_image_url(url, product_id):
engine = mysql_engine()
db_session = sessionmaker(bind=engine)
session = db_session()
# db/haystack/product_image_url
ret = session.query(ProductImageUrl.image_id).filter(
ProductImageUrl.image_url == url).first()
if not ret:
new_image_url = ProductImageUrl(image_url=url, product_id=product_id)
session.add(new_image_url)
session.commit()
session.close()
def get_product_id(sku_id, new_only=False):
engine = mysql_engine()
db_session = sessionmaker(bind=engine)
session = db_session()
if not new_only:
ret = session.query(VendorProduct).filter(
VendorProduct.sku_id == sku_id).first()
else:
ret = session.query(VendorProduct).filter(VendorProduct.sku_id == sku_id)\
.filter(VendorProduct.is_new == 1).first()
session.close()
if not ret:
return None
else:
return ret.product_id
def remove_from_favourites(product_id):
"""
:return: 'success' / 'fail'
"""
engine = mysql_engine()
db_session = sessionmaker(bind=engine)
session = db_session()
try:
session.query(VendorProduct).filter(
VendorProduct.product_id == product_id).update(
{VendorProduct.is_favourite: 0})
session.commit()
session.close()
status = "success"
except Exception as e:
print(e)
session.close()
status = "fail"
return status
def add_site(product_id, url):
engine = mysql_engine()
db_session = sessionmaker(bind=engine)
session = db_session()
ret = session.query(Site).filter(Site.full_url == url).filter(
Site.product_id == product_id).first()
if ret:
site_id = ret.site_id
else:
pattern = re.compile(url_regex)
url_match = pattern.match(url)
domain = url_match.group(3)
new_site = Site(domain=domain, full_url=url, product_id=product_id)
session.add(new_site)
session.flush()
site_id = new_site.site_id
session.commit()
session.close()
return site_id
def add_event(site_id,
price,
currency,
event_time,
status=None,
event_type=None):
engine = mysql_engine()
db_session = sessionmaker(bind=engine)
session = db_session()
price = float('%.2f' % float(price))
if len(str(currency)) > 20:
currency = ''
currency = currency_formatter(currency)
new_event = ProductEvent(site_id=site_id,
product_price=price,
product_currency=currency,
product_status=status,
event_time=event_time,
event_type=event_type)
session.add(new_event)
session.commit()
session.close()
def get_crawled_sites():
engine = mysql_engine()
db_session = sessionmaker(bind=engine)
session = db_session()
ret = session.query(Site).filter(Site.last_indexed.isnot(None))
crawled_sites = []
for r in ret:
temp = session.query(ProductSite.product_id).filter(
ProductSite.site_id == r.site_id).first()
temp = session.query(VendorProduct.is_new).filter(
VendorProduct.product_id == temp.product_id).first()
if temp.is_new == -1:
continue
else:
crawled_sites.append({
'site_id': r.site_id,
'domain': r.domain,
'full_url': r.full_url,
'last_indexed': r.last_indexed
})
session.close()
return crawled_sites
def init_db():
# Connect to mysql
engine = create_engine('mysql+pymysql://%s:%s@%s:%s'
% (mysql_conf['user'],
mysql_conf['passwd'],
mysql_conf['host'],
mysql_conf['port']))
database_name = mysql_conf['db']
# Check database
existing_databases = engine.execute("SHOW DATABASES;")
existing_databases = [d[0] for d in existing_databases]
# Create database if not exists
if database_name not in existing_databases:
sql = 'CREATE DATABASE IF NOT EXISTS %s' % database_name
engine.execute(sql)
print('Database created')
else:
print('Database already exist')
# Create schema
db_engine = mysql_engine()
Base.metadata.create_all(db_engine)
def api_get_vendor_product(product_id):
with open(os.path.join(config.RULE_DIR, 'currency_symbols.json')) as f:
currency_mapper = json.load(f)
if not currency_mapper:
print('[Error]: missing currency_symbols.json')
return
engine = mysql_engine()
db_session = sessionmaker(bind=engine)
session = db_session()
ret_products = session.query(VendorProduct, ProductSite)\
.filter(VendorProduct.product_id == product_id)\
.filter(ProductSite.product_id == VendorProduct.product_id)\
.order_by(ProductSite.product_id).all()
vendor_product = None
if not ret_products:
return vendor_product
i = 0
while i < len(ret_products):
c_product_id = ret_products[i].VendorProduct.product_id
c_product_name = ret_products[i].VendorProduct.product_name
c_vendor_price = ret_products[i].VendorProduct.product_price
c_currency = ret_products[i].VendorProduct.currency
c_is_favourite = ret_products[i].VendorProduct.is_favourite
c_sku_id = ret_products[i].VendorProduct.sku_id
price_list = []
event_list = []
while i < len(ret_products) and ret_products[
i].VendorProduct.product_id == c_product_id:
c_product_site_id = ret_products[i].ProductSite.product_site_id
ret_events = session.query(ProductEvent, ProductSite, Site)\
.filter(ProductEvent.product_site_id == ProductSite.product_site_id)\
.filter(ProductSite.site_id == Site.site_id)\
.filter(ProductEvent.product_site_id == c_product_site_id).all()
for e in ret_events:
if e.ProductEvent.currency not in currency_mapper.values():
continue
temp = {
'price': e.ProductEvent.product_price,
'currency': e.ProductEvent.currency
}
price_list.append(temp)
event_list.append(e)
i += 1
event_list.sort(key=lambda obj: obj.ProductEvent.event_time,
reverse=True)
events = []
for e in event_list:
event = {
'event_id': e.ProductEvent.event_id,
'product_site_id': e.ProductEvent.product_site_id,
'event_type': e.ProductEvent.event_type,
'event_time': e.ProductEvent.event_time,
'price_change': e.ProductEvent.price_change,
'product_price': e.ProductEvent.product_price,
'currency': e.ProductEvent.currency,
'domain': e.Site.domain,
'full_url': e.Site.full_url
}
events.append(event)
sites_count, finished_sites_count = get_status(c_product_id)
vendor_product = {
'product_id': c_product_id,
'product_name': c_product_name,
'currency': c_currency,
'vendor_price': c_vendor_price,
'sku_id': c_sku_id,
'is_favourite': c_is_favourite,
'events': events,
'sites_count': sites_count,
'finished_sites_count': finished_sites_count
}
session.close()
return vendor_product
def get_events():
"""
get events
:return: events ordered by time
[{'event_id', 'product_site_id', 'event_time', 'product_price', 'product_price_compare',
'currency', 'event_type', 'domain', 'full_url', 'product_id', 'product_name'}, ...]
"""
with open(os.path.join(config.RULE_DIR, 'currency_symbols.json')) as f:
currency_mapper = json.load(f)
if not currency_mapper:
print('[Error]: missing currency_symbols.json')
return
engine = mysql_engine()
db_session = sessionmaker(bind=engine)
session = db_session()
ret_events = session.query(ProductEvent, ProductSite).filter(
ProductEvent.product_site_id == ProductSite.product_site_id).order_by(
ProductEvent.event_time.desc()).all()
events = []
if not ret_events:
return events
for e in ret_events:
if e.ProductEvent.currency not in currency_mapper.values():
continue
c_site_id = e.ProductSite.site_id
c_product_id = e.ProductSite.product_id
c_site = session.query(Site).filter(Site.site_id == c_site_id).first()
c_vendor_product = session.query(VendorProduct).filter(
VendorProduct.product_id == c_product_id).first()
if c_vendor_product.is_new == -1:
continue
temp_price_list = [{
'price': e.ProductEvent.product_price,
'currency': e.ProductEvent.currency
}]
exchanged_price = exchange_price_list(temp_price_list,
c_vendor_product.currency)
exchanged_price = exchanged_price[0]
product_price_compare = check_price(c_vendor_product.product_price,
exchanged_price)
if product_price_compare == 'abnormal':
continue
event = {
'event_id': e.ProductEvent.event_id,
'product_site_id': e.ProductEvent.product_site_id,
'event_time': e.ProductEvent.event_time,
'product_price': e.ProductEvent.product_price,
'product_price_compare': product_price_compare,
'currency': e.ProductEvent.currency,
'event_type': e.ProductEvent.event_type,
'price_change': e.ProductEvent.price_change,
'domain': c_site.domain,
'full_url': c_site.full_url,
'product_id': c_product_id,
'product_name': c_vendor_product.product_name
}
events.append(event)
session.close()
return events
def get_vendor_product(product_id=-1):
"""
get vendor products
:param product_id: get product according to product_id. if default -1, get all products
:return: vendor products information
[{'product_id', 'product_name', 'currency', 'vendor_price',
'max_price', 'min_price', 'avg_price', 'is_favourite',
'events': {
'event_id', 'product_site_id', 'event_type', 'event_time',
'product_price', 'product_price_compare', 'currency', 'domain', 'full_url'
}
}]
"""
with open(os.path.join(config.RULE_DIR, 'currency_symbols.json')) as f:
currency_mapper = json.load(f)
if not currency_mapper:
print('[Error]: missing currency_symbols.json')
return
engine = mysql_engine()
db_session = sessionmaker(bind=engine)
session = db_session()
if product_id == -1:
ret_products = session.query(VendorProduct, ProductSite).filter(
ProductSite.product_id == VendorProduct.product_id).order_by(
ProductSite.product_id).all()
else:
ret_products = session.query(
VendorProduct,
ProductSite).filter(VendorProduct.product_id == product_id).filter(
ProductSite.product_id == VendorProduct.product_id).order_by(
ProductSite.product_id).all()
vendor_products = []
if not ret_products:
return vendor_products
i = 0
while i < len(ret_products):
if ret_products[i].VendorProduct.is_new == -1:
i += 1
continue
c_product_id = ret_products[i].VendorProduct.product_id
c_product_name = ret_products[i].VendorProduct.product_name
c_vendor_price = ret_products[i].VendorProduct.product_price
c_currency = ret_products[i].VendorProduct.currency
c_is_favourite = ret_products[i].VendorProduct.is_favourite
c_sku_id = ret_products[i].VendorProduct.sku_id
price_list = []
event_list = []
while (i < len(ret_products)
and ret_products[i].VendorProduct.product_id == c_product_id):
c_product_site_id = ret_products[i].ProductSite.product_site_id
ret_events = session.query(ProductEvent, ProductSite, Site).filter(
ProductEvent.product_site_id == ProductSite.product_site_id
).filter(ProductSite.site_id == Site.site_id).filter(
ProductEvent.product_site_id == c_product_site_id).all()
for e in ret_events:
if e.ProductEvent.currency not in currency_mapper.values():
continue
temp_price_list = [{
'price': e.ProductEvent.product_price,
'currency': e.ProductEvent.currency
}]
exchanged_price = exchange_price_list(temp_price_list,
c_currency)
exchanged_price = exchanged_price[0]
product_price_compare = check_price(c_vendor_price,
exchanged_price)
if product_price_compare == 'abnormal':
continue
temp = {
'price': e.ProductEvent.product_price,
'currency': e.ProductEvent.currency
}
price_list.append(temp)
event_list.append(e)
i += 1
if len(price_list) > 0:
exchanged_price_list = exchange_price_list(price_list, c_currency)
max_price = min_price = exchanged_price_list[0]
sum_price = 0
for p in exchanged_price_list:
if p > max_price:
max_price = p
if p < min_price:
min_price = p
sum_price += p
avg_price = round(sum_price / len(price_list), 2)
else:
max_price = min_price = avg_price = 0
event_list.sort(key=lambda obj: obj.ProductEvent.event_time,
reverse=True)
events = []
for e in event_list:
temp_price_list = [{
'price': e.ProductEvent.product_price,
'currency': e.ProductEvent.currency
}]
exchanged_price = exchange_price_list(temp_price_list, c_currency)
exchanged_price = exchanged_price[0]
product_price_compare = check_price(c_vendor_price,
exchanged_price)
event = {
'event_id': e.ProductEvent.event_id,
'product_site_id': e.ProductEvent.product_site_id,
'event_type': e.ProductEvent.event_type,
'event_time': e.ProductEvent.event_time,
'price_change': e.ProductEvent.price_change,
'product_price': e.ProductEvent.product_price,
'exchanged_price': exchanged_price,
'product_price_compare': product_price_compare,
'currency': e.ProductEvent.currency,
'domain': e.Site.domain,
'full_url': e.Site.full_url
}
events.append(event)
avg_price_compare = check_price(c_vendor_price, avg_price)
sites_count, finished_sites_count = get_status(c_product_id)
vendor_product = {
'product_id': c_product_id,
'product_name': c_product_name,
'currency': c_currency,
'vendor_price': c_vendor_price,
'sku_id': c_sku_id,
'max_price': max_price,
'min_price': min_price,
'avg_price': avg_price,
'avg_price_compare': avg_price_compare,
'is_favourite': c_is_favourite,
'events': events,
'sites_count': sites_count,
'finished_sites_count': finished_sites_count
}
vendor_products.append(vendor_product)
session.close()
return vendor_products
def update_events(event):
"""
add or update in DB(haystack/product_event)
:param event: dict, {'product_site_id', 'event_time', 'product_price', 'currency'}
"""
engine = mysql_engine()
db_session = sessionmaker(bind=engine)
session = db_session()
product_price = float('%.2f' % float(event.get('product_price')))
currency = event.get('currency')
if len(str(currency)) > 20:
currency = ''
currency = currency_formatter(currency)
product_site_id = event.get('product_site_id')
event_time = event.get('event_time')
exist_events = session.query(ProductEvent).filter(
ProductEvent.product_site_id == product_site_id).order_by(
ProductEvent.event_time.asc()).all()
need_to_save = True
event_type = 'update'
price_change = 1
if not exist_events:
event_type = 'add'
else:
for i in range(len(exist_events)):
if exist_events[i].product_price == product_price:
price_change = 0
if exist_events[i].event_time.strftime(
'%Y-%m-%d %H:%M:%S') == event_time:
need_to_save = False
break
else:
if event_time < exist_events[i].event_time.strftime(
'%Y-%m-%d %H:%M:%S') and i == 0:
event_type = 'add'
session.query(ProductEvent).filter(
ProductEvent.event_id ==
exist_events[i].event_id).update({
ProductEvent.event_type:
'update',
ProductEvent.price_change:
0
})
session.commit()
break
else:
price_change = 1
if exist_events[i].event_time.strftime(
'%Y-%m-%d %H:%M:%S') == event_time:
need_to_save = False
session.query(ProductEvent).filter(
ProductEvent.event_id ==
exist_events[i].event_id).update(
{ProductEvent.product_price: product_price})
if i > 0:
previous_price = session.query(ProductEvent.product_price)\
.filter(ProductEvent.event_id == exist_events[i - 1].event_id)
session.query(ProductEvent).filter(ProductEvent.event_id == exist_events[i - 1].event_id)\
.update({ProductEvent.price_change: (1 if product_price != previous_price else 0)})
if i < len(exist_events) - 1:
next_price = session.query(ProductEvent.product_price)\
.filter(ProductEvent.event_id == exist_events[i + 1].event_id)
session.query(ProductEvent).filter(ProductEvent.event_id == exist_events[i + 1].event_id)\
.update({ProductEvent.price_change: (1 if product_price != next_price else 0)})
session.commit()
break
else:
if event_time < exist_events[i].event_time.strftime(
'%Y-%m-%d %H:%M:%S') and i == 0:
event_type = 'add'
session.query(ProductEvent).filter(ProductEvent.event_id == exist_events[i].event_id)\
.update({ProductEvent.event_type: 'update'})
session.commit()
break
if need_to_save:
# Save to DB
new_product_event = ProductEvent(product_site_id=product_site_id,
event_time=event_time,
event_type=event_type,
product_price=product_price,
currency=currency,
price_change=price_change)
session.add(new_product_event)
session.commit()
session.close()
def get_product_site(product_site_id, event_id=-1):
"""
get product sites
:param product_site_id: product_site_id
:param event_id: get event according to event_id. if default -1, get all events
"""
with open(os.path.join(config.RULE_DIR, 'currency_symbols.json')) as f:
currency_mapper = json.load(f)
if not currency_mapper:
print('[Error]: missing currency_symbols.json')
return
engine = mysql_engine()
db_session = sessionmaker(bind=engine)
session = db_session()
ret = session.query(
ProductSite, Site,
VendorProduct).filter(ProductSite.site_id == Site.site_id).filter(
ProductSite.product_site_id == ProductEvent.product_site_id
).filter(ProductSite.product_id == VendorProduct.product_id).filter(
ProductSite.product_site_id == product_site_id).first()
c_domain = ret.Site.domain
c_full_url = ret.Site.full_url
c_country = ret.Site.country
c_site_type = ret.Site.site_type
c_last_indexed = ret.Site.last_indexed
c_product_id = ret.VendorProduct.product_id
c_product_name = ret.VendorProduct.product_name
c_vendor_price = ret.VendorProduct.product_price
c_currency = ret.VendorProduct.currency
ret_events = session.query(ProductSite, ProductEvent).filter(
ProductEvent.product_site_id == ProductSite.product_site_id).filter(
ProductSite.product_site_id == product_site_id).order_by(
ProductEvent.event_time.desc()).all()
events = []
c_event_time = None
c_product_status = None
get_current_price = False
c_price = -1
c_price_compare = 'normal'
for ret_event in ret_events:
if ret_event.ProductEvent.currency not in currency_mapper.values():
continue
temp_price_list = [{
'price': ret_event.ProductEvent.product_price,
'currency': ret_event.ProductEvent.currency
}]
exchanged_price = exchange_price_list(temp_price_list, c_currency)
exchanged_price = exchanged_price[0]
product_price_compare = check_price(c_vendor_price, exchanged_price)
if product_price_compare == 'abnormal':
continue
event = {
'event_id': ret_event.ProductEvent.event_id,
'event_type': ret_event.ProductEvent.event_type,
'event_time': ret_event.ProductEvent.event_time,
'product_price': exchanged_price,
'product_price_compare': product_price_compare,
'price_change': ret_event.ProductEvent.price_change,
}
events.append(event)
if str(event_id) == str(-1) and not get_current_price:
c_price = exchanged_price
c_price_compare = product_price_compare
c_event_time = ret_event.ProductEvent.event_time
c_product_status = ret_event.ProductEvent.product_status
get_current_price = True
if str(event_id) != str(-1) and not get_current_price:
if str(ret_event.ProductEvent.event_id) == str(event_id):
c_price = exchanged_price
c_price_compare = product_price_compare
c_event_time = ret_event.ProductEvent.event_time
c_product_status = ret_event.ProductEvent.product_status
get_current_price = True
product_site_info = {
'product_site_id': product_site_id,
'domain': c_domain,
'full_url': c_full_url,
'country': c_country,
'site_type': c_site_type,
'last_indexed': c_last_indexed,
'product_id': c_product_id,
'product_name': c_product_name,
'current_price': c_price,
'current_price_compare': c_price_compare,
'current_event_time': c_event_time,
'current_product_status': c_product_status,
'currency': c_currency,
'events': events
}
session.close()
return product_site_info
def beautify_event_time():
engine = mysql_engine()
db_session = sessionmaker(bind=engine)
session = db_session()
all_events = session.query(ProductEvent).all()
for event in all_events:
ret = session.query(ProductEventBak).filter(
ProductEventBak.event_id == event.event_id).first()
if not ret:
new_event_bak = ProductEventBak(
event_id=event.event_id,
product_site_id=event.product_site_id,
event_time=event.event_time,
event_type=event.event_type,
product_name=event.product_name,
product_price=event.product_price,
currency=event.currency,
product_status=event.product_status,
price_change=event.price_change)
session.add(new_event_bak)
session.commit()
ret = session.query(VendorProduct.product_id).all()
for r in ret:
product_id = r.product_id
all_events = session.query(ProductSite, ProductEvent).filter(
ProductEvent.product_site_id == ProductSite.product_site_id
).filter(ProductSite.product_id == product_id).all()
if not all_events:
continue
all_events_copy = all_events
all_events_copy.sort(key=lambda obj: obj.ProductEvent.event_time,
reverse=True)
latest_event_time = all_events_copy[0].ProductEvent.event_time
date_list = generate_random_date(str(latest_event_time),
len(all_events))
random.shuffle(date_list)
new_data = list(zip(all_events, date_list))
new_data.sort(key=lambda obj: obj[-1])
exist_product_site_id = []
for data in new_data:
if data[0].ProductEvent.product_site_id not in exist_product_site_id:
session.query(ProductEvent).filter(
ProductEvent.event_id ==
data[0].ProductEvent.event_id).update({
ProductEvent.event_time:
data[-1],
ProductEvent.event_type:
'add'
})
session.commit()
exist_product_site_id.append(
data[0].ProductEvent.product_site_id)
else:
session.query(ProductEvent).filter(
ProductEvent.event_id ==
data[0].ProductEvent.event_id).update({
ProductEvent.event_time:
data[-1],
ProductEvent.event_type:
'update'
})
session.commit()
session.close()
