Digital Drip

Amazon.com is one of my favorite online retailers. Their prices are [usually] great, they have incredibly fast shipping (free 2-day with Amazon Prime membership), and have an enormous inventory of merchandise. Amazon has a nice feature when you leave things in your cart, in that they will tell you when you return whether your saved cart items have increased or decreased in price. While an awesome feature, the caveat is that you have to check their site multiple times a day to increase your chances of seeing some of the really good deals.

Solution? Amazon.com’s API. Amazon provides an API to their database called Amazon Web Services for free (registration required). This opens up a huge opportunity for some great tools. Take, for instance, an automatic price updater, which automatically tells you when something you’ve had your eye on is having a sale.

The program I wrote to do this runs in three parts. Using a public wishlist where I add my items to track (instead of my shopping cart), the first script loads all my wishlist items and find anything that was added, adding them to a mysql database.

#!/usr/bin/perl

use Net::Amazon;
use Net::Amazon::Request::Wishlist;
use DBI;
use Product;


sub createRecord{
  my $rec = shift;
  print "creating record for: " . $rec->getAsin() . "\n";

  my $db='amazon';
  my $host="localhost";
  my $uid="USERNAME";
  my $passwd="PASSWORD";
  my $conn="dbi:mysql:$db:$host";

  my $asin = $rec->getAsin();
  my $name = $rec->getName();
  my $desc = "";

  # quotes will ruin our query, replace with underscore
  $name =~ s/'//g;

  my $dbh = DBI->connect($conn,$userid,$passwd);
  my $query = "insert into products(asin,name,description) values('$asin','$name','$desc')";

  my $sth = $dbh->prepare($query);
  $sth->execute();

}


my $ua = Net::Amazon->new(token=>'YOUR_AMAZON_TOKEN');
my $req = Net::Amazon::Request::Wishlist->new(wishlist=>'YOUR_WISHLIST_ID');
my @products;
my @wishlist;

my $db='amazon';
my $host="localhost";
my $uid="USERNAME";
my $passwd="YOUR_PASSWORD";
my $conn="dbi:mysql:$db:$host";

my $dbh = DBI->connect($conn,$userid,$passwd);
my $query = "select asin,name,description from products";

my $sth = $dbh->prepare($query);
$sth->execute();
$sth->bind_columns(\$asin, \$name, \$description);

while($sth->fetch()){
  #print "found: $asin | $name | $description\n";
  push(@products, Product->new($asin, $name, $description));
}

my $resp = $ua->request($req);
foreach my $item ($resp->properties){
 push(@wishlist, Product->new($item->ASIN(), $item->ProductName(),));
}

#foreach(@products){
#  print "Found a product: " .  $_->getAsin() . "\n";
#}

foreach my $wish (@wishlist){
  #print "Found a wishlist: " . $_->getAsin() . "\n";
  my $flag = 0;
  foreach my $prod (@products){
    if($wish->getAsin() eq $prod->getAsin()){
      $flag = 1;
    }
  }
  if($flag == 0){
    createRecord($wish);
  }
}

exit 0;

The second script can then run, which looks up all the items in the mysql database and finds their updated prices. If the price of an item has changed, it adds a new record to a table.

#!/usr/bin/perl

use Net::Amazon;
use Net::Amazon::Request::Wishlist;
use Net::Amazon::Request::ASIN;
use DBI;
use Product;
use Price;


## Create a record in the "prices" table
sub createRecord{
  my $rec = shift;
  #print "creating record for: " . $rec->getAsin() . "\n";

  my $db='amazon';
  my $host="localhost";
  my $uid="USERNAME";
  my $passwd="PASSWORD";
  my $conn="dbi:mysql:$db:$host";

  my $asin = $rec->getAsin();
  my $price = $rec->getPrice();

  my $dbh = DBI->connect($conn,$userid,$passwd);
  my $query = "insert into prices(asin,price) values('$asin','$price')";

  my $sth = $dbh->prepare($query);
  $sth->execute();

}

## Get a Product details from amazon
sub getAmazonPrice{
  my $ua = Net::Amazon->new(token=>'YOUR_AMAZON_TOKEN');
  my $asin = shift;

  my $req = Net::Amazon::Request::ASIN->new(asin=>$asin);
  my $resp = $ua->request($req);
  foreach my $item ($resp->properties){
    my $tPrice = $item->OurPrice();
    $tPrice =~ s/\$//g;
    return $tPrice;
  }
}

my @products;
my @amazonPrices;

my $db='amazon';
my $host="localhost";
my $uid="USERNAME";
my $passwd="PASSWORD";
my $conn="dbi:mysql:$db:$host";

my $dbh = DBI->connect($conn,$userid,$passwd);
my $query  = "select asin,price from prices where id in (select max(id) from prices group by asin)";
my $query2 = "select distinct asin from products";

my @prodAsins;
my $sth2 = $dbh->prepare($query2);
my $prodAsin;
$sth2->execute();
$sth2->bind_columns(\$prodAsin);
while($sth2->fetch()){
  push(@prodAsins, $prodAsin);
}

my $sth = $dbh->prepare($query);
$sth->execute();
$sth->bind_columns(\$asin, \$price);

while($sth->fetch()){
  push(@products, Price->new($asin, $price));
}

foreach(@prodAsins){
    push(@amazonPrices, Price->new($_, getAmazonPrice($_)));
}

foreach my $amazonAsin (@amazonPrices){
  my $flag = 2;
  foreach my $prod (@products){
    if($amazonAsin->getAsin() eq $prod->getAsin()){
      if($amazonAsin->getPrice() != $prod->getPrice()){
        $flag = 1;
      }else{
        $flag = 0;
      }
    }
  }
  if($flag > 0){
    print "Creating record for " . $amazonAsin->getAsin() . " | price is now " . $amazonAsin->getPrice() . "\n";
    createRecord($amazonAsin);
  }
}

exit 0;

The third piece is a php script which shows in a rudamentary way what the current status of your items are. Green highlighted items are currently at the lowest price you’ve seen, red are at the highest, and white have always been the same.

<html>
<head>
  <title>A Little Bit of PHP</title>
  <style type="text/css">
    td {font-size: 8pt; font-family: Arial;}
  </style>
</head>

<?php

mysql_connect('localhost', 'USERNAME', 'PASSWORD');
@mysql_select_db('amazon') or die("Unable to select database");


#$query = "call gen_report();";
$query = 'select * from products p left join ( select p.asin, p.price as min_price, max(p.creation_date) as min_date from  (select asin,min(price) as price from prices group by asin) as x inner join prices as p on x.asin = p.asin and x.price = p.price group by asin ) as A on p.asin = A.asin left join ( select p.asin, p.price as max_price, max(p.creation_date) as max_date from   (select asin,max(price) as price from prices group by asin) as x inner join prices as p on x.asin = p.asin and x.price = p.price group by asin) as B on A.asin = B.asin left join ( select p.asin, p.price as curr_price, p.creation_date as curr_date from prices p where id in (select max(id) from prices group by asin) ) as C on B.asin = C.asin';

$result = mysql_query($query);

mysql_close();

$num = mysql_numrows($result);
?>


<body>
<table border="1">
<tr>
  <th>ASIN</th>
  <th>NAME</th>
  <th>LOWEST</th>
  <th>DATE</th>
  <th>HIGHEST</th>
  <th>DATE</th>
  <th>CURRENT</th>
  <th>DATE</th>
</tr>
<?php

$i = 0;
while($i < $num){
  $min_price = mysql_result($result, $i, "min_price");
  $max_price = mysql_result($result, $i, "max_price");
  $curr_price = mysql_result($result, $i, "curr_price");

  if(($min_price == $curr_price) && ($min_price != $max_price)){
    print '<tr style="background-color: lightgreen;">';
  }else if(($max_price == $curr_price) && ($min_price != $max_price)){
    print '<tr style="background-color: pink;">';
  }else if(($max_price > $curr_price) && ($min_price < $curr_price)){
    print '<tr style="background-color: #eeee44;">';
  }else{
    print "<tr>";
  }
    	print "<td>" . mysql_result($result, $i, "asin") . "</td>";
    	print "<td>" . mysql_result($result, $i, "name") . "</td>";
    	print "<td>" . $min_price . "</td>";
    	print "<td style=\"background-color: #eeeeee;\">" . mysql_result($result, $i, "min_date") . "</td>";
    	print "<td>" . $max_price . "</td>";
    	print "<td style=\"background-color: #eeeeee;\">" . mysql_result($result, $i, "max_date") . "</td>";
    	print "<td>" . $curr_price . "</td>";
    	print "<td style=\"background-color: #eeeeee;\">" . mysql_result($result, $i, "curr_date") . "</td>";
    	print "</tr>\n";
      $i++;
    }

    mysql_free_result($result); //we're done using the results, so set it free
  ?>
</table>

</body>
</html>

To set the scripts up on a schedule, just add them to your cron. The below example looks for new items in your wishlist every hour on the hour, and new prices five minutes later.

00 * * * * /home/jon/amazon/updateProductsFromWishlist.pl
05 * * * * /home/jon/amazon/updatePrices.pl

I’ve uploaded all the source code. I plan to add more features as time permits, and depending on Amazon’s license I hope to open a full site where user’s can sign up for automated alerts.