def select_all_query_to_json(obj):
session = Session()
result = [
object_as_dict(response) for response in session.query(obj).all()
]
session.close()
return jsonify(result)
def select_query_to_json(obj, *args):
session = Session()
result = [{
column_name: column_value
for column_name, column_value in zip(args, response)
} for response in session.query(*[getattr(obj, arg)
for arg in args]).all()]
session.close()
return jsonify(result)
def select_product_by_id(product_id):
session = Session()
q = session.query(Product.name,
Company.name.label('company'),
Product.specifications,
ProductType.name.label('type')).\
filter(Company.id == Product.company_id).\
filter(ProductType.id == Product.type_id).\
filter(Product.id == product_id).all()
session.close()
return jsonify(query_to_dict(q))
def select_product_to_json(product_name):
session = Session()
product_name = ' '.join(product_name.split('-'))
q = session.query(Product.name,
Company.name.label('company'),
Product.specifications,
ProductType.name.label('type')) \
.filter(Company.id == Product.company_id) \
.filter(ProductType.id == Product.type_id) \
.filter(Product.name.like('{}%'.format(product_name))).all()
session.close()
return jsonify(query_to_dict(q))
def select_product_prices_by_id(product_id):
session = Session()
q = session.query(Product.name,
Company.name.label('company_name'),
Product.specifications,
Price.current_price,
Price.old_price,
Price.date).\
filter(Price.product_id == Product.id).\
filter(Company.id == Product.company_id).\
filter(Product.id == product_id).all()
session.close()
return jsonify(query_to_dict(q))
def select_product_prices_to_json(product_name):
session = Session()
product_name = ' '.join(product_name.split('-'))
q = session.query(Product.name,
Company.name.label('company_name'),
Product.specifications,
Price.current_price,
Price.old_price,
Price.date) \
.filter(Price.product_id == Product.id) \
.filter(Company.id == Product.company_id) \
.filter(Product.name.like('{}%'.format(product_name))).all()
session.close()
return jsonify(query_to_dict(q))
# Standard library imports
import numpy as np
import datetime as dt
# Third party imports
import matplotlib.dates as mdates
from matplotlib import pyplot as plt
# Local application imports
from dbmodels.base import engine
from dbmodels.base import Base
from dbmodels.base import Session
from dbmodels.product import Product
from dbmodels.price import Price
session = Session()
# select products.id, products.name, count(products.id) as nr from prices left join products on prices.product_id = products.id group by products.id having nr > 1;
# select companies.name, avg(prices.current_price) as total_price_current_day, prices.date from prices left join products on prices.product_id = products.id left join companies on products.company_id = companies.id group by companies.name, prices.date;
# select companies.name as company_name, avg(prices.current_price) as total_price_current_day, prices.date from prices left join products on prices.product_id = products.id left join companies on products.company_id = companies.id left join types on products.type_id = types.id where types.name = "Telefon" group by company_name, prices.date;
input_id = input("Enter id: ")
result = session.query(Product, Price).join(Price).filter(Product.id == input_id).all()
product_name = None
current_price = []
old_price = []
dates = []
for row in result:
product_name = row.Product.name
product_specifications = row.Product.specifications
current_price.append(row.Price.current_price)
def altex_etl_data(page_url):
"""Extract, Transform and Load data from altex.ro
Apply ETL to one category of products from altex site.
Phase 1: extract data form the category URL.
Phase 2: transform data to fit the database
Phase 3: load data to testdb database
Args:
param1 (str): The url of the category
Returns:
None
"""
for page in find_altex_sub_pages(page_url):
soup = bs(urlopen(page), 'html.parser')
product_list = soup.findAll(*product_tags[0])
for product in product_list:
# Phase 1 - Extract
name_container = product.find(*product_tags[1])
price_container = product.find(*product_tags[2])
current_price_container = price_container.find(*product_tags[3])
current_price_int = current_price_container.find(*product_tags[4])
current_price_dec = current_price_container.find(*product_tags[5])
old_price_container = price_container.find(*product_tags[6])
# Phase 2 - Transform
description = name_container.a['title'].split(',')
# The flag represent the extra number of words of the product
# type like 'Laptop Gaming', 'Televizor Smart Curbat'
# except the basic product types like 'Televizor' or 'Laptop'
flag_laptop = 0
flag_tv = 0
old_price = 0
# If laptop_flag is 1 means that the laptop is for gaming
# and all indices will be shifted with 1 position
if 'Gaming' in description[0].split() \
and description[0].split()[0] == 'Laptop':
flag_laptop = 1
if description[0].split()[0] == 'Televizor':
flag_tv += 1
if 'Smart' in description[0].split():
flag_tv += 1
if 'Curbat' in description[0].split():
flag_tv += 1
product_type = ' '.join(description[0].split()[0:1
+ flag_laptop
+ flag_tv])
product_comp = description[0].split()[1 + flag_laptop + flag_tv]
product_name = ' '.join(description[0].split()[2
+ flag_laptop
+ flag_tv:])
product_specifications = ', '.join(description[1:])
current_price = float(''.join(current_price_int.get_text() \
.split('.'))
+ '.'
+ current_price_dec.get_text()[1])
if old_price_container is not None:
old_price = float(''.join(old_price_container.get_text() \
.split('.')) \
.replace(',', '.'))
# Phase 3 - Load
session = Session()
if (product_type,) not in session.query(ProductType.name).all():
session.add(ProductType(product_type))
type_id = session.query(ProductType.id) \
.filter(ProductType.name == product_type) \
.first()[0]
if (product_comp,) not in session.query(Company.name).all():
session.add(Company(product_comp))
company_id = session.query(Company.id) \
.filter(Company.name == product_comp) \
.first()[0]
if (product_name, product_specifications) \
not in session.query(Product.name,
Product.specifications) \
.all():
session.add(Product(
product_name,
product_specifications,
type_id,
company_id))
product_id = session.query(Product.id) \
.filter(Product.name == product_name) \
.filter(Product.specifications
== product_specifications) \
.first()[0]
price_insert = Price(product_id, current_price, old_price)
session.add(price_insert)
session.commit()
session.close()