amazon_watches_v1.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "ca2543ed",
   "metadata": {},
   "source": [
    "# Scrapping and Postgres Dump Pipeline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "7795f96e",
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests\n",
    "from bs4 import BeautifulSoup\n",
    "import pandas as pd\n",
    "import psycopg2\n",
    "from psycopg2 import sql\n",
    "import os\n",
    "import json\n",
    "\n",
    "\n",
    "BASE_DIR = os.path.abspath(os.path.dirname(\"__file__\"))\n",
    "DATA_DIR = os.path.join(BASE_DIR, \"data\")\n",
    "\n",
    "CREDS_PATH = os.path.join(DATA_DIR, 'creds.json')\n",
    "\n",
    "with open(CREDS_PATH) as f:\n",
    "    creds = json.load(f)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5b5288af",
   "metadata": {},
   "source": [
    "### Connect Database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "a985a436",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "# Function to connect to the PostgreSQL database\n",
    "def connect_db():\n",
    "    conn = psycopg2.connect(\n",
    "        dbname=creds['database'],\n",
    "        user=creds['user'],\n",
    "        password=creds['password'],\n",
    "        host=creds['host'],\n",
    "        port=creds['port']\n",
    "    )\n",
    "    return conn\n",
    "\n",
    "# Function to create table if it does not exist\n",
    "def create_table_if_not_exists(conn):\n",
    "    with conn.cursor() as cursor:\n",
    "        create_table_query = \"\"\"\n",
    "        CREATE TABLE IF NOT EXISTS amazon_watches_v1 (\n",
    "            id SERIAL PRIMARY KEY,\n",
    "            title TEXT,\n",
    "            price TEXT,\n",
    "            overall_rating TEXT,\n",
    "            total_reviews TEXT,\n",
    "            availability TEXT,\n",
    "            model TEXT,\n",
    "            material TEXT,\n",
    "            item_length TEXT,\n",
    "            length TEXT,\n",
    "            clasp TEXT,\n",
    "            model_number TEXT,\n",
    "            reviewer_name_1 TEXT,\n",
    "            review_text_1 TEXT,\n",
    "            review_rating_1 TEXT,\n",
    "            review_date_1 TEXT,\n",
    "            reviewer_name_2 TEXT,\n",
    "            review_text_2 TEXT,\n",
    "            review_rating_2 TEXT,\n",
    "            review_date_2 TEXT,\n",
    "            reviewer_name_3 TEXT,\n",
    "            review_text_3 TEXT,\n",
    "            review_rating_3 TEXT,\n",
    "            review_date_3 TEXT\n",
    "        );\n",
    "        \"\"\"\n",
    "        cursor.execute(create_table_query)\n",
    "        conn.commit()\n",
    "\n",
    "# Function to insert data into the database\n",
    "def insert_data(conn, data):\n",
    "    with conn.cursor() as cursor:\n",
    "        insert_query = sql.SQL(\"\"\"\n",
    "            INSERT INTO amazon_watches_v1 (title, price, overall_rating, total_reviews, availability,\n",
    "                                          model, material, item_length, length, clasp, model_number,\n",
    "                                          reviewer_name_1, review_text_1, review_rating_1, review_date_1,\n",
    "                                          reviewer_name_2, review_text_2, review_rating_2, review_date_2,\n",
    "                                          reviewer_name_3, review_text_3, review_rating_3, review_date_3)\n",
    "            VALUES (%s, %s, %s, %s, %s,\n",
    "                    %s, %s, %s, %s, %s, %s,\n",
    "                    %s, %s, %s, %s,\n",
    "                    %s, %s, %s, %s,\n",
    "                    %s, %s, %s, %s)\n",
    "        \"\"\")\n",
    "        cursor.execute(insert_query, (\n",
    "            data.get(\"title\"),\n",
    "            data.get(\"price\"),\n",
    "            data.get(\"overall_rating\"),\n",
    "            data.get(\"total_reviews\"),\n",
    "            data.get(\"availability\"),\n",
    "            data.get(\"Model\"),\n",
    "            data.get(\"Material\"),\n",
    "            data.get(\"Item Length\"),\n",
    "            data.get(\"Length\"),\n",
    "            data.get(\"Clasp\"),\n",
    "            data.get(\"Model number\"),\n",
    "            data.get(\"reviewer_name_1\"),\n",
    "            data.get(\"review_text_1\"),\n",
    "            data.get(\"review_rating_1\"),\n",
    "            data.get(\"review_date_1\"),\n",
    "            data.get(\"reviewer_name_2\"),\n",
    "            data.get(\"review_text_2\"),\n",
    "            data.get(\"review_rating_2\"),\n",
    "            data.get(\"review_date_2\"),\n",
    "            data.get(\"reviewer_name_3\"),\n",
    "            data.get(\"review_text_3\"),\n",
    "            data.get(\"review_rating_3\"),\n",
    "            data.get(\"review_date_3\"),\n",
    "        ))\n",
    "        conn.commit()\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b326531d",
   "metadata": {},
   "source": [
    "### Scraping Functions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "c9771b10",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Function to extract Product Title\n",
    "def get_title(soup):\n",
    "\n",
    "    try:\n",
    "        # Outer Tag Object\n",
    "        title = soup.find(\"span\", attrs={\"id\":'productTitle'})\n",
    "        \n",
    "        # Inner NavigatableString Object\n",
    "        title_value = title.text\n",
    "\n",
    "        # Title as a string value\n",
    "        title_string = title_value.strip()\n",
    "\n",
    "    except AttributeError:\n",
    "        title_string = \"\"\n",
    "\n",
    "    return title_string\n",
    "\n",
    "# Function to extract Product Price\n",
    "def get_price(soup):\n",
    "    try:\n",
    "        # Find the price data in the HTML\n",
    "        price_data = soup.find(\"div\", attrs={'class':'a-section aok-hidden twister-plus-buying-options-price-data'}).string.strip()\n",
    "        \n",
    "        # Parse the JSON string to a Python dictionary\n",
    "        price_dict = json.loads(price_data)\n",
    "        \n",
    "        # Access the \"priceAmount\" field\n",
    "        price_amount = price_dict[\"desktop_buybox_group_1\"][0][\"priceAmount\"]\n",
    "    \n",
    "    except (AttributeError, json.JSONDecodeError, KeyError):\n",
    "        # Handle cases where the price or data is not found or malformed\n",
    "        price_amount = \"\"\n",
    "\n",
    "    return price_amount\n",
    "\n",
    "# Function to extract Product Rating\n",
    "def get_rating(soup):\n",
    "\n",
    "    try:\n",
    "        rating = soup.find(\"i\", attrs={'class':'a-icon a-icon-star a-star-4-5'}).string.strip()\n",
    "    \n",
    "    except AttributeError:\n",
    "        try:\n",
    "            rating = soup.find(\"span\", attrs={'class':'a-icon-alt'}).string.strip()\n",
    "        except:\n",
    "            rating = \"\"\t\n",
    "\n",
    "    return rating\n",
    "\n",
    "# Function to extract Number of User Reviews\n",
    "def get_review_count(soup):\n",
    "    try:\n",
    "        review_count = soup.find(\"span\", attrs={'id':'acrCustomerReviewText'}).string.strip()\n",
    "\n",
    "    except AttributeError:\n",
    "        review_count = \"\"\t\n",
    "\n",
    "    return review_count\n",
    "\n",
    "# Function to extract Availability Status\n",
    "def get_availability(soup):\n",
    "    try:\n",
    "        available = soup.find(\"div\", attrs={'id':'availability'})\n",
    "        available = available.find(\"span\").string.strip()\n",
    "\n",
    "    except AttributeError:\n",
    "        available = \"Not Available\"\t\n",
    "\n",
    "    return available\n",
    "\n",
    "\n",
    "# Your new function for scraping technical specifications\n",
    "def get_technical_specs(soup):\n",
    "    specs = {}\n",
    "    try:\n",
    "        table = soup.find(\"table\", {\"id\": \"technicalSpecifications_section_1\"})\n",
    "        if table:\n",
    "            for row in table.find_all(\"tr\"):\n",
    "                th = row.find(\"th\").text.strip()  # Extracting header name\n",
    "                td = row.find(\"td\").text.strip()  # Extracting corresponding value\n",
    "                specs[th] = td  # Adding to the dictionary\n",
    "    except AttributeError:\n",
    "        pass\n",
    "    return specs\n",
    "\n",
    "\n",
    "# Function to get consistent lengths for review data\n",
    "def get_reviews(soup):\n",
    "    names, reviews, ratings, dates = [], [], [], []\n",
    "    try:\n",
    "        review_list = soup.find(\"div\", {\"id\": \"cm-cr-dp-review-list\"})\n",
    "        review_divs = review_list.find_all(\"div\", attrs={\"data-hook\": \"review\"}, limit=3)\n",
    "        for review_div in review_divs:\n",
    "            name = review_div.find(\"span\", attrs={\"class\": \"a-profile-name\"}).text.strip()\n",
    "            review = review_div.find(\"div\", attrs={\"data-hook\": \"review-collapsed\"}).text.strip()\n",
    "            rating = review_div.find(\"i\", attrs={\"data-hook\": \"review-star-rating\"}).text.strip()\n",
    "            date = review_div.find(\"span\", attrs={\"data-hook\": \"review-date\"}).text.strip()\n",
    "            names.append(name)\n",
    "            reviews.append(review)\n",
    "            ratings.append(rating)\n",
    "            dates.append(date)\n",
    "    except AttributeError:\n",
    "        pass\n",
    "    \n",
    "    # Ensure three entries for names, reviews, ratings, and dates\n",
    "    for _ in range(3 - len(reviews)):\n",
    "        names.append(\"\")\n",
    "        reviews.append(\"\")\n",
    "        ratings.append(\"\")\n",
    "        dates.append(\"\")\n",
    "    \n",
    "    return names, reviews, ratings, dates\n",
    "\n",
    "\n",
    "# Combining data with technical specifications\n",
    "def get_all_data(soup):\n",
    "    data = {\n",
    "        \"title\": get_title(soup),\n",
    "        \"price\": get_price(soup),\n",
    "        \"overall_rating\": get_rating(soup),\n",
    "        \"total_reviews\": get_review_count(soup),\n",
    "        \"availability\": get_availability(soup),\n",
    "    }\n",
    "    \n",
    "    # Get the technical specifications and merge them with the existing data\n",
    "    specs = get_technical_specs(soup)\n",
    "    data.update(specs)\n",
    "\n",
    "    names, reviews, ratings, dates = get_reviews(soup)\n",
    "    data[\"reviewer_name_1\"] = names[0]\n",
    "    data[\"review_text_1\"] = reviews[0]\n",
    "    data[\"review_rating_1\"] = ratings[0]\n",
    "    data[\"review_date_1\"] = dates[0]\n",
    "\n",
    "    data[\"reviewer_name_2\"] = names[1]\n",
    "    data[\"review_text_2\"] = reviews[1]\n",
    "    data[\"review_rating_2\"] = ratings[1]\n",
    "    data[\"review_date_2\"] = dates[1]\n",
    "\n",
    "    data[\"reviewer_name_3\"] = names[2]\n",
    "    data[\"review_text_3\"] = reviews[2]\n",
    "    data[\"review_rating_3\"] = ratings[2]\n",
    "    data[\"review_date_3\"] = dates[2]\n",
    "\n",
    "    return data\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "805ad4fc",
   "metadata": {},
   "source": [
    "## Main Function"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "7968d318",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "connected\n"
     ]
    }
   ],
   "source": [
    "if __name__ == '__main__':\n",
    "    HEADERS = ({'User-Agent':'', 'Accept-Language': 'en-US, en;q=0.5'})\n",
    "    URL = \"https://www.amazon.com/s?i=specialty-aps&bbn=16225019011&rh=n%3A7141123011%2Cn%3A16225019011%2Cn%3A6358539011&ref=nav_em__nav_desktop_sa_intl_watches_0_2_13_4\"\n",
    "\n",
    "    # Connect to the database\n",
    "    conn = connect_db()\n",
    "\n",
    "    print(\"connected\")\n",
    "\n",
    "    try:\n",
    "        # Create table if it doesn't exist\n",
    "        create_table_if_not_exists(conn)\n",
    "\n",
    "        webpage = requests.get(URL, headers=HEADERS)\n",
    "        soup = BeautifulSoup(webpage.content, \"html.parser\")\n",
    "        links = soup.find_all(\"a\", attrs={'class': 'a-link-normal s-no-outline'})\n",
    "        links_list = [link.get('href') for link in links]\n",
    "\n",
    "        data_list = []\n",
    "\n",
    "        for link in links_list[:10]:\n",
    "            new_webpage = requests.get(\"https://www.amazon.com\" + link, headers=HEADERS)\n",
    "            new_soup = BeautifulSoup(new_webpage.content, \"html.parser\")\n",
    "            product_data = get_all_data(new_soup)\n",
    "            data_list.append(product_data)\n",
    "\n",
    "            df = pd.DataFrame(data_list)\n",
    "            df.to_csv(\"amazon_watch_data_with_specs_5.csv\", index=False)\n",
    "\n",
    "            # Insert each product's data into the database\n",
    "            insert_data(conn, product_data)\n",
    "\n",
    "    finally:\n",
    "        conn.close()  # Close the database connection\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "656177fa",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "ml_ops",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.20"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}