__ebay_commercial_scrapper__upload.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# This is a categorical scrapping project\n",
    "The goal for the project is to create a scrapper that could monitor the products informations in ebay.\n",
    "\n",
    "It will start from a given search and return all the project it had got.\n",
    "Then becomes a much intellegent scrapper that can cover the entire site and adjusting to changes.\n",
    "\n",
    "It will use xpath first, then it will switch to image recognition mode for futher scrapping."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "xpath_search_input_field = r'//*[(@id = \"gh-ac\")]'\n",
    "xpath_search_button = r'//*[(@id = \"gh-btn\")]'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "ebay_url = \"https://www.ebay.com\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from selenium import webdriver\n",
    "from selenium.webdriver.common.keys import Keys\n",
    "from selenium.webdriver.chrome.options import Options\n",
    "from selenium.webdriver.common.desired_capabilities import DesiredCapabilities\n",
    "import os\n",
    "import logging\n",
    "from parsel import Selector\n",
    "from pprint import *\n",
    "import time\n",
    "from peewee import *\n",
    "from playhouse.sqlite_ext import SqliteExtDatabase\n",
    "import datetime\n",
    "import re\n",
    "import copy\n",
    "import pandas as pd\n",
    "from IPython.display import display, HTML\n",
    "import psycopg2\n",
    "import json"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "given_serach_input = \"Iphone\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def web_driver_init():\n",
    "    driver = webdriver.Chrome()\n",
    "    driver.get(ebay_url)\n",
    "    return driver\n",
    "\n",
    "driver = web_driver_init()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def search_input_insertation(driver):\n",
    "    try:\n",
    "        driver.find_element_by_xpath(xpath_search_input_field).send_keys(given_serach_input)\n",
    "        driver.find_element_by_xpath(xpath_search_button).click()\n",
    "    except:\n",
    "        logging.error(\"Please switch to a better network condition and try again.\")\n",
    "\n",
    "search_input_insertation(driver)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "db = SqliteDatabase(\"ebay_search_spider_temp.db\")\n",
    "db.connect()\n",
    "\n",
    "class BaseModel(Model):\n",
    "    class Meta:\n",
    "        database = db\n",
    "        \n",
    "class itemOfSearchResult(BaseModel):\n",
    "    product_sponsored = BooleanField()\n",
    "    \n",
    "    product_title = CharField(unique=False)\n",
    "    product_link = CharField(unique=True)\n",
    "    product_tags = CharField(null=True)\n",
    "    product_status = CharField(null=True)\n",
    "    \n",
    "    product_price_from = CharField(null=True)\n",
    "    product_price_to = CharField(null=True)\n",
    "    product_price_off = CharField(null=True)\n",
    "    \n",
    "    product_shipping = CharField(null=True)\n",
    "    product_fast_returns = BooleanField()\n",
    "    product_estimated_delivery_time = CharField(null=True)\n",
    "    product_guaranteed_by = CharField(null=True)\n",
    "    \n",
    "    product_top_rated_plus = BooleanField()\n",
    "    product_fast_n_free = BooleanField()\n",
    "    product_buy_spry = BooleanField()\n",
    "    \n",
    "    product_watching_or_sold = CharField(null=True)\n",
    "    \n",
    "    product_inventory_information = CharField(null=True)\n",
    "    \n",
    "    product_image_url = TextField(null=True)\n",
    "    \n",
    "    scrapped_time = TimeField()\n",
    "    \n",
    "try:\n",
    "    db.drop_tables([itemOfSearchResult], safe=True)\n",
    "except:\n",
    "    pass\n",
    "db.create_tables([itemOfSearchResult], safe=True)\n",
    "db.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "xpath_product_list = \"\"\"//*[contains(concat( \" \", @class, \" \" ), concat( \" \", \"li\", \" \" ))]\"\"\"\n",
    "\n",
    "xpath_product_sponsored = \"\"\"//*[contains(concat( \" \", @class, \" \" ), concat( \" \", \"promoted-lv\", \" \" ))]//span\"\"\"\n",
    "\n",
    "xpath_product_title = \"\"\"string(//*[contains(concat( \" \", @class, \" \" ), concat( \" \", \"vip\", \" \" ))]//text())\"\"\"\n",
    "xpath_product_link = \"\"\"//*[contains(concat( \" \", @class, \" \" ), concat( \" \", \"vip\", \" \" ))]/@href\"\"\"\n",
    "xpath_product_tags = \"\"\"//div[@class=\"lvsubtitle\"][1]/text()\"\"\"\n",
    "xpath_product_status = \"\"\"//div[@class=\"lvsubtitle\"][2]/text()\"\"\"\n",
    "\n",
    "xpath_product_price_from = \"\"\"//*[contains(concat( \" \", @class, \" \" ), concat( \" \", \"prRange\", \" \" ))]/text()[1]\"\"\"\n",
    "xpath_product_price_to = \"\"\"//*[contains(concat( \" \", @class, \" \" ), concat( \" \", \"prRange\", \" \" ))]/text()[2]\"\"\"\n",
    "xpath_product_price_off = \"\"\"//*[contains(concat( \" \", @class, \" \" ), concat( \" \", \"black\", \" \" ))]/text()\"\"\"\n",
    "\n",
    "xpath_product_shipping = \"\"\"//*[contains(concat( \" \", @class, \" \" ), concat( \" \", \"ship\", \" \" ))]//*[contains(concat( \" \", @class, \" \" ), concat( \" \", \"bfsp\", \" \" ))]/text()\"\"\"\n",
    "xpath_product_fast_returns = \"\"\"//*[contains(concat( \" \", @class, \" \" ), concat( \" \", \"lvreturns\", \" \" ))]//*[contains(concat( \" \", @class, \" \" ), concat( \" \", \"bfsp\", \" \" ))]\"\"\"\n",
    "xpath_product_estimated_delivery_time = \"\"\"//*[contains(concat( \" \", @class, \" \" ), concat( \" \", \"fnftxt\", \" \" ))]//b/text()\"\"\"\n",
    "xpath_product_guaranteed_by = \"\"\"//*[(@id = \"item1c58f19dc6\")]//b/text()\"\"\"\n",
    "\n",
    "xpath_product_top_rated_plus = \"\"\"//*[contains(concat( \" \", @class, \" \" ), concat( \" \", \"iconETRS2\", \" \" ))]\"\"\"\n",
    "xpath_product_fast_n_free = \"\"\"//*[contains(concat( \" \", @class, \" \" ), concat( \" \", \"fnf-green\", \" \" ))]\"\"\"\n",
    "xpath_product_buy_spry = \"\"\"//*[contains(@class, \"lvdetails\")]//a[contains(@href, \"BuySPRY\")]\"\"\"\n",
    "\n",
    "xpath_product_watching_or_sold = \"\"\"//*[contains(concat( \" \", @class, \" \" ), concat( \" \", \"red\", \" \" ))]/text()\"\"\"\n",
    "\n",
    "xpath_product_inventory_information = \"\"\"//*[contains(concat( \" \", @class, \" \" ), concat( \" \", \"lvoptions\", \" \" ))]//a/text()\"\"\"\n",
    "\n",
    "xpath_product_image_url = \"\"\"//*[contains(concat( \" \", @class, \" \" ), concat( \" \", \"img\", \" \" ))]//img/@src\"\"\"\n",
    "\n",
    "\n",
    "xpath_next_page_button = \"\"\"//*[contains(concat( \" \", @class, \" \" ), concat( \" \", \"next\", \" \" ))]\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def collect_page_data(driver):\n",
    "    try:\n",
    "        driver.execute_script(\"window.scrollTo(0, document.body.scrollHeight*4);\")\n",
    "        driver.execute_script(\"window.scrollTo(0, document.body.scrollHeight*4);\")\n",
    "        driver.execute_script(\"window.scrollTo(0, document.body.scrollHeight*4);\")\n",
    "        driver.execute_script(\"window.scrollTo(0, document.body.scrollHeight*4);\")\n",
    "        html = driver.page_source\n",
    "\n",
    "        product_html_list = Selector(text=html).xpath(xpath_product_list).extract()\n",
    "        for product_html in product_html_list:\n",
    "            search_result = itemOfSearchResult()\n",
    "\n",
    "            if Selector(text=product_html).xpath(xpath_product_sponsored).extract_first() is not None:\n",
    "                search_result.product_sponsored = False\n",
    "            else:\n",
    "                search_result.product_sponsored = True\n",
    "            search_result.product_title = str(Selector(text=product_html).xpath(xpath_product_title).extract_first()).strip()\n",
    "            search_result.product_link = str(Selector(text=product_html).xpath(xpath_product_link).extract_first()).strip()\n",
    "            search_result.product_tags = str(Selector(text=product_html).xpath(xpath_product_tags).extract_first()).strip()\n",
    "            search_result.product_status = str(Selector(text=product_html).xpath(xpath_product_status).extract_first()).strip()\n",
    "\n",
    "            search_result.product_price_from = str(Selector(text=product_html).xpath(xpath_product_price_from).extract_first()).strip()\n",
    "            search_result.product_price_to = str(Selector(text=product_html).xpath(xpath_product_price_to).extract_first()).strip()\n",
    "            search_result.product_price_off = str(str(Selector(text=product_html).xpath(xpath_product_price_off).extract_first())).strip()\n",
    "\n",
    "            search_result.product_shipping = str(Selector(text=product_html).xpath(xpath_product_shipping).extract_first()).strip()\n",
    "            if Selector(text=product_html).xpath(xpath_product_fast_returns).extract_first() is None:\n",
    "                search_result.product_fast_returns = False\n",
    "            else: \n",
    "                search_result.product_fast_returns = True\n",
    "            search_result.product_estimated_delivery_time = str(Selector(text=product_html).xpath(xpath_product_estimated_delivery_time).extract_first()).strip()\n",
    "            search_result.product_guaranteed_by = str(Selector(text=product_html).xpath(xpath_product_guaranteed_by).extract_first()).strip()\n",
    "\n",
    "            if Selector(text=product_html).xpath(xpath_product_top_rated_plus).extract_first() is None:\n",
    "                search_result.product_top_rated_plus = False\n",
    "            else:\n",
    "                search_result.product_top_rated_plus = True\n",
    "            if Selector(text=product_html).xpath(xpath_product_fast_n_free).extract_first() is None:\n",
    "                search_result.product_fast_n_free = False\n",
    "            else:\n",
    "                search_result.product_fast_n_free = True\n",
    "            if Selector(text=product_html).xpath(xpath_product_buy_spry).extract_first() is None:\n",
    "                search_result.product_buy_spry = False\n",
    "            else:\n",
    "                search_result.product_buy_spry = True\n",
    "\n",
    "            search_result.product_watching_or_sold = str(Selector(text=product_html).xpath(xpath_product_watching_or_sold).extract_first()).strip()\n",
    "\n",
    "            search_result.product_inventory_infomation = str(Selector(text=product_html).xpath(xpath_product_inventory_information).extract_first()).strip()\n",
    "\n",
    "            search_result.product_image_url = str(Selector(text=product_html).xpath(xpath_product_image_url).extract_first()).strip()\n",
    "\n",
    "            search_result.scrapped_time = str(datetime.datetime.now().strftime(\"%I:%M%p on %B %d, %Y\"))\n",
    "\n",
    "            search_result.save()\n",
    "    except Exception as e:\n",
    "        logging.error(e)\n",
    "        logging.error(\"data parsing error: you may need to update the script\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def get_to_next_page(driver):\n",
    "    try:\n",
    "        driver.find_element_by_xpath(xpath_next_page_button).click()\n",
    "    except:\n",
    "        raise Exception(\"All Page are scrapped\")\n",
    "try:\n",
    "    while True:\n",
    "        collect_page_data(driver)\n",
    "        get_to_next_page(driver)\n",
    "        print(\"please wait the script while collecting, Successfully Get a new page. \")\n",
    "        break\n",
    "except Exception as e:\n",
    "    logging.error(e)\n",
    "    print(\"All pages are scrapped. Script Finished!\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "cursor = None\n",
    "try: \n",
    "    connection = psycopg2.connect(\"dbname='categorical_keywords' user='postgres' host='mmp' password='mmp' port='5432'\")\n",
    "    connection.autocommit = True\n",
    "    cursor = connection.cursor()\n",
    "except:\n",
    "    pprint(\"Cannot connect to server\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# insert in to commercial_goods keywords database\n",
    "for result in itemOfSearchResult.select():\n",
    "    try:\n",
    "        insert_row_command = \\\n",
    "        \"\"\"\n",
    "        INSERT INTO public.commercial_goods(\n",
    "            good_title, good_keys)\n",
    "            VALUES (%s, %s);\n",
    "        \"\"\"\n",
    "        insert_values = (result.product_title, json.dumps(result.product_title.split()))\n",
    "        cursor.execute(insert_row_command, insert_values)\n",
    "        print(\"Suceessfully inserted a new title\")\n",
    "    except Exception as e:\n",
    "        logging.error(e)\n",
    "        print(\"falied to insert a new title, may be duplicated.\") "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "cursor.close()\n",
    "print(\"commercial keyword database updated. \")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# if you want to speed up your transaction, make sure that your transaction will now raise exception first.\n",
    "# # insert into eaby_scrapping database\n",
    "# try:\n",
    "# insert_row_command = \\\n",
    "# \"\"\"INSERT INTO public.tech_products(\n",
    "#     search_string, product_title, product_sponsored, product_tags, product_status, product_price_from, product_price_to, product_price_off, product_shipping, product_fast_returns, product_estimated_delivery_time, product_guaranteed_by, product_top_rated_plus, product_fast_n_free, product_buy_spry, product_watching_or_sold, product_inventory_information, product_image_url, scrapped_time, product_link)\n",
    "#     VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);\"\"\"\n",
    "\n",
    "# args_list = []\n",
    "# for result in itemOfSearchResult.select():\n",
    "#     insert_values = (given_serach_input,search_result.product_title,search_result.product_sponsored,search_result.product_tags,search_result.product_status,search_result.product_price_from,search_result.product_price_to,search_result.product_price_off,search_result.product_shipping,search_result.product_fast_returns,search_result.product_estimated_delivery_time,search_result.product_guaranteed_by,search_result.product_top_rated_plus,search_result.product_fast_n_free,search_result.product_buy_spry,search_result.product_watching_or_sold,search_result.product_inventory_information,search_result.product_image_url,search_result.scrapped_time,search_result.product_link)\n",
    "#     args_list.append(copy.deepcopy(insert_values))\n",
    "    \n",
    "# psycopg2.extras.execute_values(cursor, insert_row_command, args_list, template=None, page_size=100)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "cursor = None\n",
    "try: \n",
    "    connection = psycopg2.connect(\"dbname='scrapping_ebay' user='postgres' host='mmp' password='mmp' port='5432'\")\n",
    "    connection.autocommit = True\n",
    "    cursor = connection.cursor()\n",
    "except:\n",
    "    pprint(\"Cannot connect to server\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "successfully updated an old product entry\n",
      "successfully updated an old product entry\n",
      "successfully updated an old product entry\n",
      "successfully updated an old product entry\n",
      "successfully updated an old product entry\n",
      "successfully updated an old product entry\n",
      "successfully updated an old product entry\n",
      "successfully updated an old product entry\n",
      "successfully updated an old product entry\n",
      "successfully updated an old product entry\n",
      "successfully updated an old product entry\n",
      "successfully updated an old product entry\n",
      "successfully updated an old product entry\n",
      "successfully updated an old product entry\n",
      "successfully updated an old product entry\n",
      "successfully updated an old product entry\n",
      "successfully updated an old product entry\n",
      "successfully updated an old product entry\n",
      "successfully updated an old product entry\n",
      "successfully updated an old product entry\n",
      "successfully updated an old product entry\n",
      "successfully updated an old product entry\n",
      "successfully updated an old product entry\n",
      "successfully updated an old product entry\n",
      "successfully updated an old product entry\n",
      "successfully updated an old product entry\n",
      "successfully updated an old product entry\n",
      "successfully updated an old product entry\n",
      "successfully updated an old product entry\n",
      "successfully updated an old product entry\n",
      "successfully updated an old product entry\n",
      "successfully updated an old product entry\n",
      "successfully updated an old product entry\n",
      "successfully updated an old product entry\n",
      "successfully updated an old product entry\n",
      "successfully updated an old product entry\n",
      "successfully updated an old product entry\n",
      "successfully updated an old product entry\n",
      "successfully updated an old product entry\n",
      "successfully updated an old product entry\n",
      "successfully updated an old product entry\n",
      "successfully updated an old product entry\n",
      "successfully updated an old product entry\n",
      "successfully updated an old product entry\n",
      "successfully updated an old product entry\n",
      "successfully updated an old product entry\n",
      "successfully updated an old product entry\n",
      "successfully updated an old product entry\n",
      "successfully updated an old product entry\n",
      "successfully updated an old product entry\n",
      "successfully updated an old product entry\n",
      "successfully updated an old product entry\n",
      "successfully updated an old product entry\n",
      "successfully updated an old product entry\n",
      "successfully updated an old product entry\n",
      "successfully updated an old product entry\n",
      "successfully updated an old product entry\n",
      "successfully updated an old product entry\n",
      "successfully updated an old product entry\n",
      "successfully updated an old product entry\n"
     ]
    }
   ],
   "source": [
    "for search_result in itemOfSearchResult.select():\n",
    "    try:\n",
    "        update_row_command = \\\n",
    "        \"\"\"\n",
    "        UPDATE public.tech_products\n",
    "            SET search_string=%s, product_title=%s, product_sponsored=%s, product_tags=%s, product_status=%s, product_price_from=%s, product_price_to=%s, product_price_off=%s, product_shipping=%s, product_fast_returns=%s, product_estimated_delivery_time=%s, product_guaranteed_by=%s, product_top_rated_plus=%s, product_fast_n_free=%s, product_buy_spry=%s, product_watching_or_sold=%s, product_inventory_information=%s, product_image_url=%s, scrapped_time=%s, product_link=%s\n",
    "            WHERE product_link=%s;\n",
    "        \"\"\"\n",
    "        update_values = (given_serach_input,search_result.product_title,search_result.product_sponsored,search_result.product_tags,search_result.product_status,search_result.product_price_from,search_result.product_price_to,search_result.product_price_off,search_result.product_shipping,search_result.product_fast_returns,search_result.product_estimated_delivery_time,search_result.product_guaranteed_by,search_result.product_top_rated_plus,search_result.product_fast_n_free,search_result.product_buy_spry,search_result.product_watching_or_sold,search_result.product_inventory_information,search_result.product_image_url,search_result.scrapped_time,search_result.product_link,search_result.product_link)\n",
    "        cursor.execute(update_row_command, update_values)\n",
    "        print(\"successfully updated an old product entry\")\n",
    "    except Exception as e:\n",
    "        logging.error(e)\n",
    "        print(\"falied to update an old product entry, this entry maybe a brand new entry. \")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "successfully inserted a new product entry. \n",
      "successfully inserted a new product entry. \n",
      "successfully inserted a new product entry. \n",
      "successfully inserted a new product entry. \n",
      "successfully inserted a new product entry. \n",
      "successfully inserted a new product entry. \n",
      "successfully inserted a new product entry. \n",
      "successfully inserted a new product entry. \n",
      "successfully inserted a new product entry. \n",
      "successfully inserted a new product entry. \n",
      "successfully inserted a new product entry. \n",
      "successfully inserted a new product entry. \n",
      "successfully inserted a new product entry. \n",
      "successfully inserted a new product entry. \n",
      "successfully inserted a new product entry. \n",
      "successfully inserted a new product entry. \n",
      "successfully inserted a new product entry. \n",
      "successfully inserted a new product entry. \n",
      "successfully inserted a new product entry. \n",
      "successfully inserted a new product entry. \n",
      "successfully inserted a new product entry. \n",
      "successfully inserted a new product entry. \n",
      "successfully inserted a new product entry. \n",
      "successfully inserted a new product entry. \n",
      "successfully inserted a new product entry. \n",
      "successfully inserted a new product entry. \n",
      "successfully inserted a new product entry. \n",
      "successfully inserted a new product entry. \n",
      "successfully inserted a new product entry. \n",
      "successfully inserted a new product entry. \n",
      "successfully inserted a new product entry. \n",
      "successfully inserted a new product entry. \n",
      "successfully inserted a new product entry. \n",
      "successfully inserted a new product entry. \n",
      "successfully inserted a new product entry. \n",
      "successfully inserted a new product entry. \n",
      "successfully inserted a new product entry. \n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "ERROR:root:duplicate key value violates unique constraint \"tech_products_pkey\"\n",
      "DETAIL:  Key (product_title, product_price_from)=(Apple iPhone7PLUS/7 32GB/256GB/128, None) already exists.\n",
      "\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "falied to insert a new product entry, may be duplicated.\n",
      "successfully inserted a new product entry. \n",
      "successfully inserted a new product entry. \n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "ERROR:root:duplicate key value violates unique constraint \"tech_products_pkey\"\n",
      "DETAIL:  Key (product_title, product_price_from)=(New listing, None) already exists.\n",
      "\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "falied to insert a new product entry, may be duplicated.\n",
      "successfully inserted a new product entry. \n",
      "successfully inserted a new product entry. \n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "ERROR:root:duplicate key value violates unique constraint \"tech_products_pkey\"\n",
      "DETAIL:  Key (product_title, product_price_from)=(New listing, None) already exists.\n",
      "\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "falied to insert a new product entry, may be duplicated.\n",
      "successfully inserted a new product entry. \n",
      "successfully inserted a new product entry. \n",
      "successfully inserted a new product entry. \n",
      "successfully inserted a new product entry. \n",
      "successfully inserted a new product entry. \n",
      "successfully inserted a new product entry. \n",
      "successfully inserted a new product entry. \n",
      "successfully inserted a new product entry. \n",
      "successfully inserted a new product entry. \n",
      "successfully inserted a new product entry. \n",
      "successfully inserted a new product entry. \n",
      "successfully inserted a new product entry. \n",
      "successfully inserted a new product entry. \n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "ERROR:root:duplicate key value violates unique constraint \"tech_products_pkey\"\n",
      "DETAIL:  Key (product_title, product_price_from)=(New listing, None) already exists.\n",
      "\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "falied to insert a new product entry, may be duplicated.\n",
      "successfully inserted a new product entry. \n",
      "successfully inserted a new product entry. \n"
     ]
    }
   ],
   "source": [
    "for search_result in itemOfSearchResult.select():\n",
    "    try:\n",
    "        insert_row_command = \\\n",
    "        \"\"\"INSERT INTO public.tech_products(\n",
    "            search_string, product_title, product_sponsored, product_tags, product_status, product_price_from, product_price_to, product_price_off, product_shipping, product_fast_returns, product_estimated_delivery_time, product_guaranteed_by, product_top_rated_plus, product_fast_n_free, product_buy_spry, product_watching_or_sold, product_inventory_information, product_image_url, scrapped_time, product_link)\n",
    "            VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);\"\"\"\n",
    "        insert_values = (given_serach_input,search_result.product_title,search_result.product_sponsored,search_result.product_tags,search_result.product_status,search_result.product_price_from,search_result.product_price_to,search_result.product_price_off,search_result.product_shipping,search_result.product_fast_returns,search_result.product_estimated_delivery_time,search_result.product_guaranteed_by,search_result.product_top_rated_plus,search_result.product_fast_n_free,search_result.product_buy_spry,search_result.product_watching_or_sold,search_result.product_inventory_information,search_result.product_image_url,search_result.scrapped_time,search_result.product_link)\n",
    "        cursor.execute(insert_row_command, insert_values)\n",
    "        print(\"successfully inserted a new product entry. \")\n",
    "    except Exception as e:\n",
    "        logging.error(e)\n",
    "        print(\"falied to insert a new product entry, may be duplicated.\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "cursor.close()\n",
    "print(\"job finished with \", len(itemOfSearchResult.select(), \" product scrapped and uploaded.\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python36 scrapy",
   "language": "python",
   "name": "myenv"
  },
  "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.5.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}