In [4]:
import requests
import re
import datetime
import random
from time import sleep
import bs4
from bs4 import BeautifulSoup
import pandas as pd
from pandas import Series, DataFrame
import sqlalchemy
from sqlalchemy import create_engine,Table,Column,Integer,String,MetaData,ForeignKey,Date
from IPython.display import display
In [5]:
import sqlalchemy
from sqlalchemy import create_engine
In [6]:
pd.set_option('max_colwidth', 800)
In [7]:
%load_ext watermark
In [8]:
%watermark
In [129]:
engine=create_engine("mysql+pymysql://semantic:GbwSq1RzFa@104.199.201.206:13606/Tests?charset=utf8",echo=False, encoding='utf-8')
conn = engine.connect()
sql_command = "select count(*) FROM Tests.amzProd"
resultSet = pd.read_sql_query(sql=sql_command, con=conn, coerce_float=False)
display(resultSet)
conn.close()
In [15]:
engine=create_engine("mysql+pymysql://semantic:GbwSq1RzFa@104.199.201.206:13606/Tests?charset=utf8",echo=False, encoding='utf-8')
conn = engine.connect()
sql_command = "SELECT type,sum(totalRev) as totalRev FROM Tests.amzProd group by type order by totalRev desc"
resultSet = pd.read_sql_query(sql=sql_command, con=conn, coerce_float=False)
display(resultSet)
conn.close()
In [108]:
resultSet["totalRev"].sum()
Out[108]:
In [21]:
prodTypes=["central","canister","handheld","robotic","stick","upright","wetdry"]
for prodType in prodTypes:
engine=create_engine("mysql+pymysql://semantic:GbwSq1RzFa@104.199.201.206:13606/Tests?charset=utf8",echo=False, encoding='utf-8')
conn = engine.connect()
sql_command = "SELECT type,pname,brand,price,totalRev,avgStars FROM Tests.amzProd where type='"+ prodType +"' order by price desc limit 5"
resultSet = pd.read_sql_query(sql=sql_command, con=conn, coerce_float=False)
display(resultSet)
conn.close()
In [20]:
prodTypes=["central","canister","handheld","robotic","stick","upright","wetdry"]
for prodType in prodTypes:
engine=create_engine("mysql+pymysql://semantic:GbwSq1RzFa@104.199.201.206:13606/Tests?charset=utf8",echo=False, encoding='utf-8')
conn = engine.connect()
sql_command = "SELECT type,pname,brand,price,totalRev,avgStars FROM Tests.amzProd where type='"+ prodType +"' and totalRev>500 order by avgStars desc limit 5"
resultSet = pd.read_sql_query(sql=sql_command, con=conn, coerce_float=False)
display(resultSet)
conn.close()
In [60]:
engine=create_engine("mysql+pymysql://semantic:GbwSq1RzFa@104.199.201.206:13606/Tests?charset=utf8",echo=False, encoding='utf-8')
conn = engine.connect()
sql_command = "SELECT brand,count(pname),avg(totalRev),avg(avgStars),avg(price) FROM Tests.amzProd \
group by brand having count(pname)> 50 order by avg(avgStars) desc"
resSet = pd.read_sql_query(sql=sql_command, con=conn, coerce_float=False)
display(resSet)
conn.close()
In [57]:
prodTypes=["central","canister","handheld","robotic","stick","upright","wetdry"]
for brand in resSet["brand"]:
engine=create_engine("mysql+pymysql://semantic:GbwSq1RzFa@104.199.201.206:13606/Tests?charset=utf8",echo=False, encoding='utf-8')
conn = engine.connect()
sql_command = "SELECT count(type),type,brand FROM Tests.amzProd \
where brand='"+brand+"' group by type"
resultSet = pd.read_sql_query(sql=sql_command, con=conn, coerce_float=False)
display(resultSet)
conn.close()