scrape_insert.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "view-in-github"
   },
   "source": [
    "<a href=\"https://colab.research.google.com/github/AstroBoy1/chess-ds/blob/master/scrape.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "LDUUtUdDpmBk"
   },
   "source": [
    "# Imports"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "l6-FpRyaod9w"
   },
   "outputs": [],
   "source": [
    "from bs4 import BeautifulSoup\n",
    "from requests import get\n",
    "from requests.exceptions import RequestException\n",
    "from contextlib import closing\n",
    "import pandas as pd\n",
    "import re\n",
    "import sqlite3\n",
    "import os.path"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "slt0RtcZprys"
   },
   "source": [
    "# Methods"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "pj8K1FXMpkQA"
   },
   "outputs": [],
   "source": [
    "def simple_get(url):\n",
    "    \"\"\"\n",
    "    Attempts to get the content at `url` by making an HTTP GET request.\n",
    "    If the content-type of response is some kind of HTML/XML, return the\n",
    "    text content, otherwise return None.\n",
    "    \"\"\"\n",
    "    try:\n",
    "        with closing(get(url, stream=True)) as resp:\n",
    "            if is_good_response(resp):\n",
    "                return resp.content\n",
    "            else:\n",
    "                return None\n",
    "\n",
    "    except RequestException as e:\n",
    "        log_error('Error during requests to {0} : {1}'.format(url, str(e)))\n",
    "        return None\n",
    "\n",
    "\n",
    "def is_good_response(resp):\n",
    "    \"\"\"\n",
    "    Returns True if the response seems to be HTML, False otherwise.\n",
    "    \"\"\"\n",
    "    content_type = resp.headers['Content-Type'].lower()\n",
    "    return (resp.status_code == 200\n",
    "            and content_type is not None\n",
    "            and content_type.find('html') > -1)\n",
    "\n",
    "\n",
    "def log_error(e):\n",
    "    \"\"\"\n",
    "    It is always a good idea to log errors.\n",
    "    This function just prints them, but you can\n",
    "    make it do anything.\n",
    "    \"\"\"\n",
    "    print(e)\n",
    "\n",
    "\n",
    "def get_user_info(parsed_html, user_id, conn, c):\n",
    "    \"\"\"Takes in html from beautiful soup, finds the gender and state, and returns a user object\"\"\"\n",
    "    s = str(parsed_html)\n",
    "    st = s.strip()\n",
    "    if \"Error\" in st:\n",
    "        print(\"Non-existent player\")\n",
    "        return -1\n",
    "    state_index = st.index(\"State\")\n",
    "    gender_index = st.index(\"Gender\")\n",
    "    s = st[state_index + 21: state_index + 23]\n",
    "    g = st[gender_index + 22: gender_index + 23]\n",
    "    values = [(user_id, g, s)]\n",
    "    c.execute(\"INSERT INTO users VALUES (?, ?, ?)\", values)\n",
    "    conn.commit()\n",
    "    return 0\n",
    "\n",
    "\n",
    "def rating_helper(rating_string):\n",
    "    \"\"\"Gets the before and after rating for one instance with simple processing\"\"\"\n",
    "    rating_split = rating_string.split(\"=>\")\n",
    "    first, second = rating_split[0].strip(), rating_split[1].strip()\n",
    "    return first, second\n",
    "\n",
    "\n",
    "def get_event_ratings(parsed_html, user_id, conn, c):\n",
    "    \"\"\"Takes in the html from beautiful soup and gets the tournament event id, dates, and ratings\"\"\"\n",
    "    if \"Error\" in str(parsed_html):\n",
    "        return -1\n",
    "    table = parsed_html.find('table', {'valign': 'top', 'width': '960', 'cellspacing': '0', 'cellpadding': '4', 'border': '1'})\n",
    "    if table:\n",
    "        dates = table.find_all(\"td\", {'width': '120'})\n",
    "        tournaments = table.find_all(\"td\", {'width': '350'})\n",
    "        ratings = table.find_all(\"td\", {'width': '160'})\n",
    "        rating_i = 0\n",
    "        for d, t in zip(dates, tournaments):\n",
    "            d_s = d.getText()\n",
    "            parsed_date = d_s[:10]\n",
    "            parsed_event_id = d_s[10:]\n",
    "            t_text = t.getText()\n",
    "            # Matches anything that starts with ( and ends with ), .* says there can be zero or more of any characters\n",
    "            match = re.search(\"\\(.*\\)\", t_text)\n",
    "            start, end = match.span()\n",
    "            section = t_text[end:]\n",
    "            state = t_text[start + 1:end - 1]\n",
    "            tournament_name = t_text[:start - 1]\n",
    "            reg_ratings = ratings[rating_i].getText()\n",
    "            quick_ratings = ratings[rating_i + 1].getText()\n",
    "            blitz_ratings = ratings[rating_i + 2].getText()\n",
    "            rating_i += 3\n",
    "            reg_rating_before, reg_rating_after, quick_rating_before, quick_rating_after, blitz_rating_before, \\\n",
    "                blitz_rating_after = None, None, None, None, None, None\n",
    "            if \"=>\" in reg_ratings:\n",
    "                reg_rating_before, reg_rating_after = rating_helper(reg_ratings)\n",
    "            if \"=>\" in quick_ratings:\n",
    "                quick_rating_before, quick_rating_after = rating_helper(quick_ratings)\n",
    "            if \"=>\" in blitz_ratings:\n",
    "                blitz_rating_before, blitz_rating_after = rating_helper(blitz_ratings)\n",
    "            values = [(parsed_event_id, user_id, parsed_date, tournament_name, reg_rating_before, reg_rating_after,\n",
    "                            quick_rating_after, quick_rating_after, blitz_rating_before, blitz_rating_after, section,\n",
    "                            state)]\n",
    "            c.execute('INSERT INTO user_events VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)', values)\n",
    "            conn.commit()\n",
    "        return 0\n",
    "    else:\n",
    "        return -1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "Dz9ExTvnpuYs"
   },
   "source": [
    "# Main"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "YFctDsE-liM0"
   },
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name '__file__' is not defined",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-5-a6c453232205>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m()\u001b[0m\n\u001b[1;32m----> 1\u001b[1;33m \u001b[0mbase_dir\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mos\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mpath\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mdirname\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mos\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mpath\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mabspath\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0m__file__\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      2\u001b[0m \u001b[0mdb_path\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mos\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mpath\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mjoin\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mbase_dir\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;34m'chessDB.db'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      3\u001b[0m \u001b[0mconn\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0msqlite3\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mconnect\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mdb_path\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      4\u001b[0m \u001b[0mc\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mconn\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mcursor\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      5\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mNameError\u001b[0m: name '__file__' is not defined"
     ],
     "output_type": "error"
    }
   ],
   "source": [
    "base_dir = os.path.dirname(os.path.abspath(__file__))\n",
    "db_path = os.path.join(base_dir, 'chessDB.db')\n",
    "conn = sqlite3.connect(db_path)\n",
    "c = conn.cursor()\n",
    "\n",
    "for i in range(1, 2):\n",
    "    uid = str(1243502) + str(i)\n",
    "    page = \"http://www.uschess.org/msa/MbrDtlMain.php?\" + uid\n",
    "    rating_page = \"http://www.uschess.org/msa/MbrDtlTnmtHst.php?\" + uid\n",
    "    # Pages then go http://www.uschess.org/msa/MbrDtlTnmtHst.php?12641216.2\n",
    "    # http://www.uschess.org/msa/MbrDtlTnmtHst.php?12641216.3\n",
    "    # http://www.uschess.org/msa/MbrDtlTnmtHst.php?12641216.11\n",
    "    # and so forth\n",
    "    raw_html = simple_get(page)\n",
    "    html = BeautifulSoup(raw_html, 'html.parser')\n",
    "    get_user_info(html, uid, users, conn, c)\n",
    "    \n",
    "    count = 1\n",
    "    event_return = 0\n",
    "    while event_return != -1:\n",
    "        rating_page = \"http://www.uschess.org/msa/MbrDtlTnmtHst.php?12641216\" + \".\" + str(count)\n",
    "        raw_html = simple_get(rating_page)\n",
    "        html = BeautifulSoup(raw_html, 'html.parser')\n",
    "        event_return = get_event_ratings(html, uid, user_events)\n",
    "        count += 1\n",
    "        \n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "accelerator": "GPU",
  "colab": {
   "collapsed_sections": [
    "LDUUtUdDpmBk",
    "w6UDA27Upo-D"
   ],
   "include_colab_link": true,
   "name": "scrape.ipynb",
   "provenance": [],
   "version": "0.3.2"
  },
  "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.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}