Untitled.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import requests\n",
    "from bs4 import BeautifulSoup\n",
    "import pymysql\n",
    "import json\n",
    "import time\n",
    "import re"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": true,
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "def get_post_data(strrec):\n",
    "    data = json.loads(r'''{\n",
    "        \"find_key1\": \"關鍵字\",\n",
    "        \"search_work\": \"軟體工程師\",\n",
    "        \"find_work_mode1\": \"2_1011_0001_0005\",\n",
    "        \"s_find_work_mode1\": \"軟體工程師\",\n",
    "        \"strrec\": %s,\n",
    "        \"search_type\": \"job\",\n",
    "        \"search_item\": \"1\",\n",
    "        \"search_from\": \"index\"}'''%(strrec))\n",
    "    return data\n",
    "\n",
    "def get_headers():\n",
    "    headers = json.loads(r'''{\n",
    "        \"Accept\": \"text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,image/apng,*/*;q=0.8\",\n",
    "        \"Accept-Encoding\": \"gzip, deflate, br\",\n",
    "        \"Accept-Language\": \"zh-TW,zh;q=0.9,en-US;q=0.8,en;q=0.7\",\n",
    "        \"Cache-Control\": \"max-age=0\",\n",
    "        \"Connection\": \"keep-alive\",\n",
    "        \"Content-Length\": \"4419\",\n",
    "        \"Content-Type\": \"application/x-www-form-urlencoded\",\n",
    "        \"Cookie\": \"_sh_c_1=search_type%3Ajob%7Csearch_item%3A1%7Cs_find_work_mode1%3A%E8%BB%9F%E9%AB%94%E5%B7%A5%E7%A8%8B%E5%B8%AB%7Cfind_work_mode1%3A2_1011_0001_0005%7C_mu_ckb_21%3Acheckbox%7C_mu_ckb_22%3Acheckbox%7C_mu_ckb_23%3Acheckbox%7C_mu_ckb_24%3Acheckbox%7C_mu_ckb_25%3Acheckbox; _sh_c_2=search_type%3Ajob%7Csearch_item%3A1%7Cs_find_work_mode1%3A%E8%BB%9F%E9%AB%94%E5%B7%A5%E7%A8%8B%E5%B8%AB%7Cfind_work_mode1%3A2_1011_0001_0005%7C_mu_ckb_21%3Acheckbox%7C_mu_ckb_22%3Acheckbox%7C_mu_ckb_23%3Acheckbox%7C_mu_ckb_24%3Acheckbox%7C_mu_ckb_25%3Acheckbox; _ga=GA1.3.1697330314.1516179527; ASPSESSIONIDSSDBATTA=LMBMJICCHJBAPBOEGMGKJNPF; ASPSESSIONIDSSARRSRQ=ABIHPEBCNGMJIANDINJBOIOI; _gid=GA1.3.509086006.1516527407; yes123_make_cookie=cc1a299f7899bf32341f07b7ab716f16; view%5Fjob%5Fid%5Fstr=20171030160315%5F23750095%40%21%4020171110154748%5F5061151%40%21%40%E7%A8%8B%E5%BC%8F%E7%B3%BB%E7%B5%B1%E5%B7%A5%E7%A8%8B%E5%B8%AB%24%21%2420171121100345%5F53216057%40%21%4020180108084033%5F24394301%40%21%40%E8%BB%9F%E9%AB%94%E7%B6%AD%E8%AD%B7%E5%B7%A5%E7%A8%8B%E5%B8%AB; ASPSESSIONIDQSDABSSA=OJLIBGCCDFENIJOKNNCOLDFC; ASPSESSIONIDQQCBASTA=HMOHDCECDEJLOHHIAGHMHOAJ; ASPSESSIONIDSSCTTSSR=ACOFJBNBKCPJHOGMGCJAHJNF; ASPSESSIONIDQQADBTSA=AAFOLEECEACBLFJEMGPEJIDC; citrix_ns_id=BzZl3GUO9H+Fqy0PdyubyQ2TckwA000; ASP.NET_SessionId=608953571; StepCookie_id=608953571; ClientIP=122.116.5.248; _gat_UA-4824195-2=1; step=45\",\n",
    "        \"Host\": \"www.yes123.com.tw\",\n",
    "        \"Origin\": \"https://www.yes123.com.tw\",\n",
    "        \"Referer\": \"https://www.yes123.com.tw/admin/index.asp\",\n",
    "        \"Upgrade-Insecure-Requests\": \"1\",\n",
    "        \"User-Agent\": \"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/63.0.3239.132 Safari/537.36\"\n",
    "    }''')\n",
    "    return headers\n",
    "    \n",
    "def get_url_soup(url, strrec):\n",
    "    resp = requests.post(url, data=get_post_data(strrec), headers=get_headers())\n",
    "    resp.encoding = 'utf-8'\n",
    "    soup = BeautifulSoup(resp.text, 'html5lib')\n",
    "    return soup\n",
    "\n",
    "def get_url_list(url, strrec):\n",
    "    soup = get_url_soup(url, strrec)\n",
    "    url_list = []\n",
    "    for jobname in [a for a in soup.select('a.jobname') if a.has_attr('href')]:\n",
    "        url_list.append(home + jobname['href'])\n",
    "    return url_list\n",
    "\n",
    "def get_detail(url):\n",
    "    soup = get_url_soup(url, 0)\n",
    "    \n",
    "    # 初始Job化欄位\n",
    "    job = {}\n",
    "    job['title'] = ''\n",
    "    job['company'] = ''\n",
    "    job['location'] = ''\n",
    "    job['skills'] = ''\n",
    "    \n",
    "    # 職位\n",
    "    span = soup.select_one('.jobname_title > span')\n",
    "    if span is not None:\n",
    "        job['title'] = span.previousSibling\n",
    "    else:\n",
    "        return job\n",
    "    \n",
    "    # 公司名稱\n",
    "    span = soup.select_one('div.jobname_title p a')\n",
    "    if span is not None:\n",
    "        job['company'] = span.text\n",
    "    \n",
    "    # 工作地點\n",
    "    comp_titles = soup.select('.comp_detail h2')\n",
    "    hire_title = [title for title in comp_titles if title.text == '徵才說明']\n",
    "    job['location'] = hire_title[0].findNext('ul').select('span')[3].text.strip().split()[0]\n",
    "    \n",
    "    # 工作技能\n",
    "    skill_title = [title for title in comp_titles if title.text == '技能與求職專長']\n",
    "    skill_spans = skill_title[0].findNext('ul').select('span.rr')\n",
    "    for span in skill_spans:\n",
    "        skills_list = span.text.split('：')\n",
    "        if len(skills_list) > 1: \n",
    "            job['skills'] += skills_list[1].replace('Java Script', 'JavaScript').replace('／', ' ').replace('/', ' ') + ' '\n",
    "    return job\n",
    "\n",
    "def getTotalPage(url):\n",
    "    soup = get_url_soup(url, 0)\n",
    "    td_text = soup.select('table.sift td')[2].text.replace(' ','')\n",
    "    total_page = re.search(r'/(\\d+)頁', td_text).group(1)\n",
    "    return total_page"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total pages: 27\n",
      "page: 1\n"
     ]
    },
    {
     "ename": "ProgrammingError",
     "evalue": "(1146, \"Table 'jobs.job1' doesn't exist\")",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mProgrammingError\u001b[0m                          Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-15-61e6055940d1>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[1;32m     20\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     21\u001b[0m         \u001b[0mjobs\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;34m[\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mjob\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'company'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mjob\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'title'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mjob\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'location'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mjob\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'skills'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;32mfor\u001b[0m \u001b[0mjob\u001b[0m \u001b[0;32min\u001b[0m \u001b[0mjob_list\u001b[0m \u001b[0;32mif\u001b[0m \u001b[0mjob\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'company'\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;34m!=\u001b[0m \u001b[0;34m''\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 22\u001b[0;31m         \u001b[0mcur\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecutemany\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0minsert_1111_template\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mjobs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     23\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     24\u001b[0m \u001b[0mcur\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mclose\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/.pyenv/versions/3.6.1/lib/python3.6/site-packages/pymysql/cursors.py\u001b[0m in \u001b[0;36mexecutemany\u001b[0;34m(self, query, args)\u001b[0m\n\u001b[1;32m    192\u001b[0m                                          self._get_db().encoding)\n\u001b[1;32m    193\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 194\u001b[0;31m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mrowcount\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0msum\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mquery\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0marg\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;32mfor\u001b[0m \u001b[0marg\u001b[0m \u001b[0;32min\u001b[0m \u001b[0margs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    195\u001b[0m         \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mrowcount\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    196\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/.pyenv/versions/3.6.1/lib/python3.6/site-packages/pymysql/cursors.py\u001b[0m in \u001b[0;36m<genexpr>\u001b[0;34m(.0)\u001b[0m\n\u001b[1;32m    192\u001b[0m                                          self._get_db().encoding)\n\u001b[1;32m    193\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 194\u001b[0;31m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mrowcount\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0msum\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mquery\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0marg\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;32mfor\u001b[0m \u001b[0marg\u001b[0m \u001b[0;32min\u001b[0m \u001b[0margs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    195\u001b[0m         \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mrowcount\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    196\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/.pyenv/versions/3.6.1/lib/python3.6/site-packages/pymysql/cursors.py\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, query, args)\u001b[0m\n\u001b[1;32m    163\u001b[0m         \u001b[0mquery\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mmogrify\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mquery\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0margs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    164\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 165\u001b[0;31m         \u001b[0mresult\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_query\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mquery\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    166\u001b[0m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_executed\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mquery\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    167\u001b[0m         \u001b[0;32mreturn\u001b[0m \u001b[0mresult\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/.pyenv/versions/3.6.1/lib/python3.6/site-packages/pymysql/cursors.py\u001b[0m in \u001b[0;36m_query\u001b[0;34m(self, q)\u001b[0m\n\u001b[1;32m    319\u001b[0m         \u001b[0mconn\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_get_db\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    320\u001b[0m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_last_executed\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mq\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 321\u001b[0;31m         \u001b[0mconn\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mquery\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mq\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    322\u001b[0m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_do_get_result\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    323\u001b[0m         \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mrowcount\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/.pyenv/versions/3.6.1/lib/python3.6/site-packages/pymysql/connections.py\u001b[0m in \u001b[0;36mquery\u001b[0;34m(self, sql, unbuffered)\u001b[0m\n\u001b[1;32m    858\u001b[0m                 \u001b[0msql\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0msql\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mencode\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mencoding\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'surrogateescape'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    859\u001b[0m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_execute_command\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mCOMMAND\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mCOM_QUERY\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0msql\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 860\u001b[0;31m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_affected_rows\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_read_query_result\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0munbuffered\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0munbuffered\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    861\u001b[0m         \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_affected_rows\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    862\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/.pyenv/versions/3.6.1/lib/python3.6/site-packages/pymysql/connections.py\u001b[0m in \u001b[0;36m_read_query_result\u001b[0;34m(self, unbuffered)\u001b[0m\n\u001b[1;32m   1059\u001b[0m         \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1060\u001b[0m             \u001b[0mresult\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mMySQLResult\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1061\u001b[0;31m             \u001b[0mresult\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mread\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1062\u001b[0m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_result\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mresult\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1063\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mresult\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mserver_status\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/.pyenv/versions/3.6.1/lib/python3.6/site-packages/pymysql/connections.py\u001b[0m in \u001b[0;36mread\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m   1347\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mread\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1348\u001b[0m         \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1349\u001b[0;31m             \u001b[0mfirst_packet\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mconnection\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_read_packet\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1350\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1351\u001b[0m             \u001b[0;32mif\u001b[0m \u001b[0mfirst_packet\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mis_ok_packet\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/.pyenv/versions/3.6.1/lib/python3.6/site-packages/pymysql/connections.py\u001b[0m in \u001b[0;36m_read_packet\u001b[0;34m(self, packet_type)\u001b[0m\n\u001b[1;32m   1016\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1017\u001b[0m         \u001b[0mpacket\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mpacket_type\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mbuff\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mencoding\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1018\u001b[0;31m         \u001b[0mpacket\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcheck_error\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1019\u001b[0m         \u001b[0;32mreturn\u001b[0m \u001b[0mpacket\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1020\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/.pyenv/versions/3.6.1/lib/python3.6/site-packages/pymysql/connections.py\u001b[0m in \u001b[0;36mcheck_error\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m    382\u001b[0m             \u001b[0merrno\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mread_uint16\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    383\u001b[0m             \u001b[0;32mif\u001b[0m \u001b[0mDEBUG\u001b[0m\u001b[0;34m:\u001b[0m \u001b[0mprint\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"errno =\"\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0merrno\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 384\u001b[0;31m             \u001b[0merr\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mraise_mysql_exception\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_data\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    385\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    386\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mdump\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/.pyenv/versions/3.6.1/lib/python3.6/site-packages/pymysql/err.py\u001b[0m in \u001b[0;36mraise_mysql_exception\u001b[0;34m(data)\u001b[0m\n\u001b[1;32m    105\u001b[0m         \u001b[0merrval\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mdata\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m3\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdecode\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'utf-8'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'replace'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    106\u001b[0m     \u001b[0merrorclass\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0merror_map\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mget\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0merrno\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mInternalError\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 107\u001b[0;31m     \u001b[0;32mraise\u001b[0m \u001b[0merrorclass\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0merrno\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0merrval\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;31mProgrammingError\u001b[0m: (1146, \"Table 'jobs.job1' doesn't exist\")"
     ]
    }
   ],
   "source": [
    "home = 'https://www.yes123.com.tw/admin/'\n",
    "search_url = \"https://www.yes123.com.tw/admin/job_refer_list.asp\"\n",
    "total_page = int(getTotalPage(search_url)) + 1\n",
    "\n",
    "conn = pymysql.connect(host='localhost', port=3306, user='root', password='1234', db='jobs', use_unicode=True, charset=\"utf8mb4\", autocommit=True)\n",
    "cur = conn.cursor()\n",
    "insert_1111_template = \"insert into job1(site, company, title, location, skills) values('yes123', %s, %s, %s, %s)\"\n",
    "print('Total pages: ' + str(total_page))\n",
    "\n",
    "for page1 in range(0, total_page):\n",
    "    time.sleep(1)\n",
    "    print('page: ' + str(page1+1))\n",
    "    page2 = page1 * 20\n",
    "    url_list = get_url_list(search_url, page2) # 查詢頁抓取每筆工作連結\n",
    "\n",
    "    job_list = []\n",
    "    for job_url in url_list:\n",
    "        job = get_detail(job_url) # 內容頁抓取工作資訊\n",
    "        job_list.append(job)\n",
    "        \n",
    "        jobs = [(job['company'], job['title'], job['location'], job['skills']) for job in job_list if job['company'] != '']\n",
    "        cur.executemany(insert_1111_template, jobs)\n",
    "\n",
    "cur.close\n",
    "conn.close"
   ]
  }
 ],
 "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.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}