#!/usr/bin/python3
from urllib.request import urlopen, Request
import urllib
from bs4 import BeautifulSoup
import time
import pymysql.cursors



def lookup_amazon_price(prod_code):
 url = 'http://www.amazon.com/dp/'+prod_code
 req = urllib.request.Request(
     url,
     data=None,
     headers={
        'User-Agent': 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_3) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/35.0.1916.47 Safari/537.36'
     }
 )

 f = urllib.request.urlopen(req)
 html = f.read().decode('utf-8')
 bsObj = BeautifulSoup ( html , "lxml")
 t = bsObj.find("span",{'id':'productTitle'})
 p = bsObj.find("span",{'id':'priceblock_ourprice'})
 title = t.get_text()
 title = " ".join(title.split())
 price = p.get_text()
 all_item_info = [title,price]
 return all_item_info

filename = 'tocheck.txt'
with open(filename) as f:
 lines = [line.rstrip('\n') for line in open(filename)]

num_prod = len(lines)

# Connect to the database
connection = pymysql.connect(host='localhost',
                             user='username',
                             password='password',
                             db='database',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)

try:
 with connection.cursor() as cursor:
  # Create a new record
  print('\nAmazon Price Logger:\nby Someone\n\nThere are '+str(num_prod)+' products to check:\n\nBeginning Now\n\n')
  for prod_code in lines:
   all_item_info = lookup_amazon_price(prod_code)
   sql = 'INSERT INTO products (id_num, title) VALUES (%s, %s) ON DUPLICATE KEY UPDATE added=added'
   cursor.execute(sql, (prod_code,all_item_info[0]))
   connection.commit()
   sql = 'INSERT INTO prices (id_num, price) VALUES (%s, %s) ON DUPLICATE KEY UPDATE time=time'
   cursor.execute(sql, (prod_code,all_item_info[1]))
   connection.commit()
   num_prod -= 1
   print('Product: '+all_item_info[0]+'\nPrice: '+all_item_info[1]+'\nProducts Remaining: '+str(num_prod)+'\n\n')
   time.sleep(5)

finally:
    connection.close()