ck_scraper.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "importing Jupyter notebook from data_structures.ipynb\n"
     ]
    }
   ],
   "source": [
    "from bs4 import BeautifulSoup\n",
    "import time\n",
    "import requests\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import import_ipynb\n",
    "import data_structures as ds\n",
    "from datetime import datetime\n",
    "import psycopg2\n",
    "from psycopg2.extras import execute_values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "def ck_pull_store():\n",
    "    page = requests.get(r'https://www.cardkingdom.com/purchasing/mtg_singles?filter%5Bipp%5D=100&filter%5Bsort%5D'\n",
    "                            r'=name&filter%5Bsearch%5D=mtg_advanced&filter%5Bname%5D=&filter%5Bcategory_id%5D=0'\n",
    "                            r'&filter%5Bnonfoil%5D=1&filter%5Brarity%5D%5B0%5D=M&filter%5Brarity%5D%5B1%5D=R&filter'\n",
    "                            r'%5Brarity%5D%5B2%5D=U&filter%5Brarity%5D%5B3%5D=C&filter%5Brarity%5D%5B5%5D=S&filter'\n",
    "                            r'%5Bprice_op%5D=%3C%3D&filter%5Bprice%5D=499.99&page=1')\n",
    "\n",
    "    soup = BeautifulSoup(page.content, 'html.parser')\n",
    "\n",
    "    pg_num = int(list(soup.children)[-31].get_text())\n",
    "\n",
    "    print('CK page number = ' + str(pg_num))\n",
    "\n",
    "    items = []\n",
    "    for n in range(pg_num):\n",
    "        print(n)\n",
    "        count = 0\n",
    "        page = requests.get(r'https://www.cardkingdom.com/purchasing/mtg_singles?filter%5Bipp%5D=100&filter%5Bsort%5D'\n",
    "                            r'=name&filter%5Bsearch%5D=mtg_advanced&filter%5Bname%5D=&filter%5Bcategory_id%5D=0'\n",
    "                            r'&filter%5Bnonfoil%5D=1&filter%5Brarity%5D%5B0%5D=M&filter%5Brarity%5D%5B1%5D=R&filter'\n",
    "                            r'%5Brarity%5D%5B2%5D=U&filter%5Brarity%5D%5B3%5D=C&filter%5Brarity%5D%5B5%5D=S&filter'\n",
    "                            r'%5Bprice_op%5D=%3C%3D&filter%5Bprice%5D=499.99&page=' + str(n + 1))\n",
    "\n",
    "        soup = BeautifulSoup(page.content, 'html.parser')\n",
    "\n",
    "        cards_raw = [c for c in list(soup.children) if len(str(c)) > 10][:-13]\n",
    "        cards_raw = [b for b in cards_raw if 'sellDollarAmount' in str(b)]\n",
    "\n",
    "        for card in cards_raw:\n",
    "            card_1 = list(card.children)[-1]\n",
    "            if count == 0:\n",
    "                card_2 = list(card_1.children)[-1]\n",
    "                card_3 = list(card_2.children)[-2]\n",
    "                card_4 = list(card_3.children)[-2]\n",
    "                card_5 = list(card_4.children)[-3]\n",
    "                card_6 = list(card_5.children)[-2]\n",
    "                card_7 = list(card_6.children)[-2]\n",
    "                card_8 = list(card_7.children)[-1]\n",
    "\n",
    "                price_raw = list(card_8.children)[-1]\n",
    "                info_raw = list(card_8.children)[-3]\n",
    "\n",
    "                info_1 = list(info_raw.children)[1]\n",
    "                info_2 = list(info_1.children)[1]\n",
    "                name = list(info_2.children)[1].get_text()\n",
    "                info_3 = list(info_2.children)[3]\n",
    "                info_4 = list(info_3.children)[1]\n",
    "                set = list(info_4.children)[0].split(' (')[0]\n",
    "                if len(list(info_4.children)) > 1:\n",
    "                    foil = True\n",
    "                else:\n",
    "                    foil = False\n",
    "\n",
    "                price_1 = list(price_raw.children)[3]\n",
    "                price_2 = list(price_1.children)[3]\n",
    "                price_3 = list(price_2.children)[1]\n",
    "                price_4 = list(price_3.children)[1]\n",
    "                price_5 = list(price_4.children)[0]\n",
    "                dollars = list(price_5.children)[1].get_text()\n",
    "                cents = list(price_5.children)[2].get_text()\n",
    "                price = float(dollars) + float(cents) * .01\n",
    "            else:\n",
    "                info_raw = list(card_1.children)[-3]\n",
    "                info_1 = list(info_raw.children)[1]\n",
    "                info_2 = list(info_1.children)[1]\n",
    "                name = list(info_2.children)[1].get_text()\n",
    "                info_3 = list(info_2.children)[3]\n",
    "                info_4 = list(info_3.children)[1]\n",
    "                set = list(info_4.children)[0].split(' (')[0]\n",
    "                if len(list(info_4.children)) > 1:\n",
    "                    foil = True\n",
    "                else:\n",
    "                    foil = False\n",
    "\n",
    "                price_raw = list(card_1.children)[-1]\n",
    "                price_1 = list(price_raw.children)[3]\n",
    "                price_2 = list(price_1.children)[3]\n",
    "                price_3 = list(price_2.children)[1]\n",
    "                price_4 = list(price_3.children)[1]\n",
    "                price_5 = list(price_4.children)[0]\n",
    "                dollars = list(price_5.children)[1].get_text()\n",
    "                cents = list(price_5.children)[2].get_text()\n",
    "                price = float(dollars) + float(cents) * .01\n",
    "\n",
    "            items.append([name, foil, set, price])\n",
    "            count += 1\n",
    "\n",
    "    df = pd.DataFrame(items, columns=['name', 'foil', 'set', 'price'])\n",
    "    df = df[df['foil'] == False]\n",
    "    df['uuid'] = np.nan\n",
    "\n",
    "    conn = psycopg2.connect(host=\"localhost\", port=5432, database=\"\", user=\"\", password=\"\")\n",
    "    cur = conn.cursor()\n",
    "    \n",
    "    # So that it stops trying to convert to float\n",
    "    df['uuid']=df['uuid'].astype(str)\n",
    "    \n",
    "    c_get_u_mistakes = 0\n",
    "    for index, row in df.iterrows():\n",
    "        try:\n",
    "            uuid = ds.get_uuid(cur, row['name'], row['set'], 'ck')\n",
    "            df.at[index, 'uuid'] = uuid\n",
    "        except Exception as e:\n",
    "            c_get_u_mistakes += 1\n",
    "    print('Getting uuid mistakes: ' + str(c_get_u_mistakes))\n",
    "\n",
    "    df = df.loc[df['uuid'] != 'nan']\n",
    "    \n",
    "    pd.set_option('display.max_columns', None)\n",
    "    df = df[pd.notnull(df['uuid'])]\n",
    "    df['ckdate'] = datetime.now()\n",
    "    \n",
    "    df.drop_duplicates(subset=['uuid', 'ckdate'], inplace=True)\n",
    "    \n",
    "    t_df = tuple(df[['uuid', 'ckdate', 'price']].itertuples(index=False, name=None))\n",
    "    execute_values(cur, \"INSERT INTO ckbuydata (uuid, ckdate, ckbuyprice) VALUES %s\", t_df)\n",
    "    conn.commit()\n",
    "    \n",
    "    cur.close()\n",
    "    conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "def main():\n",
    "    start = time.time()\n",
    "    ck_pull_store()\n",
    "    end = time.time()\n",
    "    print(end - start)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CK page number = 218\n",
      "0\n",
      "1\n",
      "2\n",
      "3\n",
      "4\n",
      "5\n",
      "6\n",
      "7\n",
      "8\n",
      "9\n",
      "10\n",
      "11\n",
      "12\n",
      "13\n",
      "14\n",
      "15\n",
      "16\n",
      "17\n",
      "18\n",
      "19\n",
      "20\n",
      "21\n",
      "22\n",
      "23\n",
      "24\n",
      "25\n",
      "26\n",
      "27\n",
      "28\n",
      "29\n",
      "30\n",
      "31\n",
      "32\n",
      "33\n",
      "34\n",
      "35\n",
      "36\n",
      "37\n",
      "38\n",
      "39\n",
      "40\n",
      "41\n",
      "42\n",
      "43\n",
      "44\n",
      "45\n",
      "46\n",
      "47\n",
      "48\n",
      "49\n",
      "50\n",
      "51\n",
      "52\n",
      "53\n",
      "54\n",
      "55\n",
      "56\n",
      "57\n",
      "58\n",
      "59\n",
      "60\n",
      "61\n",
      "62\n",
      "63\n",
      "64\n",
      "65\n",
      "66\n",
      "67\n",
      "68\n",
      "69\n",
      "70\n",
      "71\n",
      "72\n",
      "73\n",
      "74\n",
      "75\n",
      "76\n",
      "77\n",
      "78\n",
      "79\n",
      "80\n",
      "81\n",
      "82\n",
      "83\n",
      "84\n",
      "85\n",
      "86\n",
      "87\n",
      "88\n",
      "89\n",
      "90\n",
      "91\n",
      "92\n",
      "93\n",
      "94\n",
      "95\n",
      "96\n",
      "97\n",
      "98\n",
      "99\n",
      "100\n",
      "101\n",
      "102\n",
      "103\n",
      "104\n",
      "105\n",
      "106\n",
      "107\n",
      "108\n",
      "109\n",
      "110\n",
      "111\n",
      "112\n",
      "113\n",
      "114\n",
      "115\n",
      "116\n",
      "117\n",
      "118\n",
      "119\n",
      "120\n",
      "121\n",
      "122\n",
      "123\n",
      "124\n",
      "125\n",
      "126\n",
      "127\n",
      "128\n",
      "129\n",
      "130\n",
      "131\n",
      "132\n",
      "133\n",
      "134\n",
      "135\n",
      "136\n",
      "137\n",
      "138\n",
      "139\n",
      "140\n",
      "141\n",
      "142\n",
      "143\n",
      "144\n",
      "145\n",
      "146\n",
      "147\n",
      "148\n",
      "149\n",
      "150\n",
      "151\n",
      "152\n",
      "153\n",
      "154\n",
      "155\n",
      "156\n",
      "157\n",
      "158\n",
      "159\n",
      "160\n",
      "161\n",
      "162\n",
      "163\n",
      "164\n",
      "165\n",
      "166\n",
      "167\n",
      "168\n",
      "169\n",
      "170\n",
      "171\n",
      "172\n",
      "173\n",
      "174\n",
      "175\n",
      "176\n",
      "177\n",
      "178\n",
      "179\n",
      "180\n",
      "181\n",
      "182\n",
      "183\n",
      "184\n",
      "185\n",
      "186\n",
      "187\n",
      "188\n",
      "189\n",
      "190\n",
      "191\n",
      "192\n",
      "193\n",
      "194\n",
      "195\n",
      "196\n",
      "197\n",
      "198\n",
      "199\n",
      "200\n",
      "201\n",
      "202\n",
      "203\n",
      "204\n",
      "205\n",
      "206\n",
      "207\n",
      "208\n",
      "209\n",
      "210\n",
      "211\n",
      "212\n",
      "213\n",
      "214\n",
      "215\n",
      "216\n",
      "217\n",
      "Getting uuid mistakes: 6382\n",
      "751.051374912262\n"
     ]
    }
   ],
   "source": [
    "if __name__ == '__main__':\n",
    "    main()"
   ]
  }
 ],
 "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.6.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}