web_scrapping.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# mongodb functions\n",
    "from pymongo import MongoClient\n",
    "# remember to start te mongodb server: sudo service mongod start\n",
    "\n",
    "# scrapping functions\n",
    "from selenium import webdriver\n",
    "from selenium.webdriver.firefox.options import Options\n",
    "from selenium.webdriver.common.by import By\n",
    "from selenium.webdriver.support.ui import WebDriverWait\n",
    "from selenium.webdriver.support import expected_conditions as EC\n",
    "\n",
    "# sql functions\n",
    "from sqlalchemy import create_engine\n",
    "from sqlalchemy_utils import database_exists, create_database\n",
    "import psycopg2\n",
    "import pandas as pd\n",
    "\n",
    "# other\n",
    "import pickle"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# get id and url info from mongodb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "myclient = MongoClient(\"mongodb://localhost:27017/\")\n",
    "mydb = myclient[\"kick\"] #pyramids is the database\n",
    "mycol = mydb[\"k1912\"] #invoice is the collection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# get the urls\n",
    "id_urls = []\n",
    "\n",
    "for proposal in mycol.find({\"data.state\": { \"$in\": [\"successful\", \"failed\"]}},\n",
    "                           {\"data.urls.web\": 1, \"data.blurb\" : 1, \"data.state\" : 1, \"data.id\" :1}):\n",
    "    urls            = proposal['data']['urls']['web']['project']\n",
    "    blurbs_finished = proposal['data']['blurb']\n",
    "    blurbs_state    = proposal['data']['state']\n",
    "    ids             = proposal['data']['id']\n",
    "    id_url = [urls, blurbs_finished, blurbs_state, ids]\n",
    "    id_urls.append(id_url)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## create browser"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "options = Options()\n",
    "options.add_argument('-headless')\n",
    "browser = webdriver.Firefox(options = options)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## connect to postgre database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "dbname = 'birth_db'\n",
    "username = 'shengsql'\n",
    "pswd = '1234'\n",
    "\n",
    "# connect:\n",
    "con = None\n",
    "con = psycopg2.connect(database = dbname, user = username, host='localhost', password=pswd)\n",
    "cursor = con.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>project_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2202</th>\n",
       "      <td>1.660540e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2203</th>\n",
       "      <td>1.781537e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2204</th>\n",
       "      <td>9.153386e+08</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        project_id\n",
       "2202  1.660540e+09\n",
       "2203  1.781537e+09\n",
       "2204  9.153386e+08"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# example query:\n",
    "sql_query = \"\"\"SELECT project_id FROM stories;\"\"\"\n",
    "\n",
    "birth_data_from_sql = pd.read_sql_query(sql_query,con)\n",
    "birth_data_from_sql.tail(3)\n",
    "#2204"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## scrapping the kickstarter website"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "inser = \"INSERT INTO stories(project_id, url, story) VALUES(%s, %s, %s)\"\n",
    "\n",
    "for i in range(2209, 2210):\n",
    "    b = browser\n",
    "    p_id = id_urls[i][3]\n",
    "    p_url = id_urls[i][0]\n",
    "    b.get(p_url)\n",
    "    try:\n",
    "        element = WebDriverWait(browser, 100).until(\n",
    "            EC.presence_of_element_located((By.CLASS_NAME, \"rte__content\"))\n",
    "        ) # this part ensures that the next command wait until the element loads \n",
    "    finally:\n",
    "        s = b.find_element_by_class_name(\"rte__content\").text.replace('\\n','').lower()\n",
    "        record = [p_id, p_url, s]\n",
    "        cursor.execute(inser, record)\n",
    "        con.commit() "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## scrape more failed project\n",
    "b.c. the first part of the url list had more successful projects than failed, not representing the population"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# query all project information, joining project metadata with storis info:\n",
    "sql_query = \"\"\"SELECT projects.project_id, projects.url, state\n",
    "    FROM projects LEFT JOIN stories \n",
    "    ON (projects.project_id = stories.project_id) \n",
    "    WHERE stories.project_id IS NULL \n",
    "    AND state = 'failed';\"\"\"\n",
    "\n",
    "failed_project_url = pd.read_sql_query(sql_query,con)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'https://www.kickstarter.com/projects/2045709688/re-work-napoleon-dynamite?ref=discovery_category_newest'"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "failed_project_url.url[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "#for i in range(1000)\n",
    "\n",
    "inser_300 = \"INSERT INTO stories(project_id, url, story) VALUES(%s, %s, %s)\"\n",
    "\n",
    "for i in range(700, 701):\n",
    "    b = browser\n",
    "    p_id = str(failed_project_url.project_id[i])\n",
    "    p_url = failed_project_url.url[i]\n",
    "    b.get(p_url)\n",
    "    try:\n",
    "        element = WebDriverWait(browser, 100).until(\n",
    "            EC.presence_of_element_located((By.CLASS_NAME, \"rte__content\"))\n",
    "        ) # this part ensures that the next command wait until the element loads \n",
    "    finally:\n",
    "        s = b.find_element_by_class_name(\"rte__content\").text.replace('\\n','').lower()\n",
    "        record = [p_id, p_url, s]\n",
    "        cursor.execute(inser_300, record)\n",
    "        con.commit() "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# scrape an example for the app"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "url_app_example = \"https://www.kickstarter.com/projects/blackdiplomats/black-diplomats-decolonize-the-global-affairs-conversation?ref=discovery&term=Black%20Diplomats%20-%20Decolonize%20the%20global%20affairs%20conversation\"\n",
    "browser.get(url_app_example)\n",
    "try:\n",
    "  element = WebDriverWait(browser, 100).until(\n",
    "    EC.presence_of_element_located((By.CLASS_NAME, \"rte__content\")))\n",
    "finally:\n",
    "  example_story = browser.find_element_by_class_name(\"rte__content\").text.replace('\\n','').lower()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "with open(\"pickle/app_example.pkl\", \"wb\") as f:\n",
    "    pickle.dump(example_story, f)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.8.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}