scrapPage.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pymysql\n",
    "connection = pymysql.connect(host='localhost',\n",
    "                             user='root',\n",
    "                             password='',\n",
    "                             db='test_ocw',\n",
    "                             charset='utf8',\n",
    "                             # Selectの結果をdictionary形式で受け取る\n",
    "                             cursorclass=pymysql.cursors.DictCursor)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests\n",
    "from bs4 import BeautifulSoup\n",
    "\n",
    "'''\n",
    "OCWから学院一覧を取得するスクリプト(6個くらいだから必要ない気もする)\n",
    "gakuinListの各要素は次のような辞書に鳴っている\n",
    "{\n",
    "\t'name' : 学院名,\n",
    "\t'url' : その学院の授業の一覧のurl,\n",
    "}\n",
    "'''\n",
    "def getGakuinList():\n",
    "\turl = \"http://www.ocw.titech.ac.jp/\"\n",
    "\tresponse = requests.get(url)\n",
    "\tsoup = BeautifulSoup(response.content,\"lxml\")\n",
    "    \n",
    "\tprint(\"Get GakuinList\")\n",
    "\n",
    "\ttopMainNav = soup.find(\"ul\",id=\"top-mein-navi\")\n",
    "\n",
    "\tgakubus = topMainNav.find_all(class_=\"gakubuBox\")\n",
    "\n",
    "\tgakuinList = []\n",
    "\tfor gakubu_div in gakubus:\n",
    "\t\tgakuin = gakubu_div.find(class_=\"gakubuHead\").span.string\n",
    "\t\t#if gakuin[-2::] != \"学院\":\n",
    "\t\t#\tcontinue\n",
    "\t\tgakuin_url = url + gakubu_div.parent['href']\n",
    "\t\tgakuinList.append({'gakuin':gakuin,'gakuin_url':gakuin_url})\n",
    "\n",
    "\treturn gakuinList\n",
    "\n",
    "'''\n",
    "[{'name': '理学院', 'url': 'http://www.ocw.titech.ac.jp//index.php?module=General&action=T0100&GakubuCD=1&lang=JA'},\n",
    "{'name': '工学院', 'url': 'http://www.ocw.titech.ac.jp//index.php?module=General&action=T0100&GakubuCD=2&lang=JA'},\n",
    "{'name': '物質理工学院', 'url': 'http://www.ocw.titech.ac.jp//index.php?module=General&action=T0100&GakubuCD=3&lang=JA'},\n",
    "{'name': '情報理工学院', 'url': 'http://www.ocw.titech.ac.jp//index.php?module=General&action=T0100&GakubuCD=4&lang=JA'},\n",
    "{'name': '生命理工学院', 'url': 'http://www.ocw.titech.ac.jp//index.php?module=General&action=T0100&GakubuCD=5&lang=JA'},\n",
    "{'name': '環境・社会理工学院', 'url': 'http://www.ocw.titech.ac.jp//index.php?module=General&action=T0100&GakubuCD=6&lang=JA'},\n",
    "{'name': '教養科目群', 'url': 'http://www.ocw.titech.ac.jp//index.php?module=General&action=T0200&GakubuCD=7&GakkaCD=370000&tab=2&focus=100&lang=JA'},\n",
    "{'name': '類科目', 'url': 'http://www.ocw.titech.ac.jp//index.php?module=General&action=T0100&GakubuCD=0&lang=JA'}]\n",
    "'''\n",
    "\n",
    "'''\n",
    "学院名とurlを渡されたらその学院の授業一覧を持ってくる\n",
    "'''\n",
    "def getLectures(name,url):\n",
    "    urlprefix = \"http://www.ocw.titech.ac.jp\"\n",
    "    response = requests.get(url)\n",
    "    soup = BeautifulSoup(response.content,'lxml')\n",
    "    \n",
    "    print(\"\\tGet Lectures:\",name)\n",
    "    \n",
    "    tables = soup.find_all('table',class_='ranking-list')\n",
    "    \n",
    "    LecList = []\n",
    "\n",
    "    for t in tables:\n",
    "        table = t.tbody\n",
    "        for item in table.find_all('tr'):\n",
    "            code = item.find('td',class_='code').string\n",
    "            name = item.find('td',class_='course_title').a.string #講義名\n",
    "            lecture_url = urlprefix + item.find('td',class_='course_title').a['href']\n",
    "            teachers = [te.string for te in item.find('td',class_='lecturer').find_all('a')]\n",
    "            #quater = item.find('td',class_='opening_department').a.string\t#TODO ちゃんととれてない\n",
    "            if not name or not code:\t# 文字列が空の場合はスキップ\n",
    "                continue\n",
    "            if code:\n",
    "                code = code.strip()\n",
    "            if name:\n",
    "                name = name.strip()\n",
    "            #if quater:\n",
    "            #    quater = quater.strip()\n",
    "            #print(name)\n",
    "            #print(teachers)\n",
    "            #print(lecture_url)\n",
    "            #print(quater)\n",
    "\n",
    "            LecList.append({\"code\":code,\"name\":name,\"lecture_url\":lecture_url})\n",
    "\n",
    "    return LecList\n",
    "\n",
    "#print(getGakuinList())\n",
    "#getLectures('情報理工学院','http://www.ocw.titech.ac.jp/index.php?module=General&action=T0100&GakubuCD=4&lang=JA')\n",
    "\n",
    "#Q.曜日・時限が複数になった時もリスト構造にするか(今はリスト構造になっていない)\n",
    "#各教員にaタグが入っている前提で以下の様になっている\n",
    "#アクセスランキングは必要に感じなかったので空\n",
    "\n",
    "#時折text内に凄いへんな文字が入る可能性がある(元のOCWの書かれ方のせい)\n",
    "def fetch_OCW(Gakuin,Lecture):\n",
    "    response = requests.get(Lecture[\"lecture_url\"])\n",
    "    soup = BeautifulSoup(response.content, \"html.parser\")\n",
    "    print(\"\\t\\tSCRAPE Lecture:\",Lecture[\"name\"])\n",
    "    \n",
    "    OCW = {}\n",
    "\n",
    "    for key in soup.find(attrs={\"class\":\"gaiyo-data clearfix\"}).find_all(\"dl\"): #上部\n",
    "        value_list = []\n",
    "        for value in key.dd.strings:\n",
    "            value = value.strip().replace(\" \",\"\").replace(\"\\n\",\"\")\n",
    "            if len(value)>0: value_list.append(value)\n",
    "        OCW[key.dt.text] = \",\".join(value_list)\n",
    "\n",
    "    for key in soup.find_all(attrs={\"class\":\"cont-sec\"}): #下部\n",
    "        keyString = key.h3.text\n",
    "\n",
    "        if key.table is not None: #授業計画・学生が身につける力\n",
    "            tr_list = []\n",
    "            #print([j.text for j in key.thead.tr.find_all(\"th\")]) #ヘッダ\n",
    "            for TR in key.tbody.find_all(\"tr\"): #ボディ\n",
    "                tr_list.append(\"[{}]\".format(\n",
    "                    \",\".join([\"\\\\'{}\\\\'\".format(j.text) for j in TR.find_all(\"td\")])\n",
    "                     ))\n",
    "            OCW[keyString] = \"[{}]\".format(\",\".join(tr_list))\n",
    "            ''' リスト構造で保持する場合\n",
    "            for TR in i.tbody.find_all(\"tr\"): #ボディ\n",
    "                print([j.text.split() for j in TR.find_all(\"td\")])\n",
    "            '''\n",
    "            \n",
    "        elif key.ul is not None: #関連する科目\n",
    "            OCW[keyString] = \",\".join([j.text for j in key.ul.find_all(\"li\")])\n",
    "            \n",
    "        else: #そのほか(文章で書かれた項目)\n",
    "            OCW[keyString] = \"\\n\".join(key.p.strings)\n",
    "    \n",
    "    #詳細ページに記載のなかった項目は適宜\n",
    "    OCW[\"講義名\"] = Lecture[\"name\"]\n",
    "    OCW[\"URL\"] = Lecture[\"lecture_url\"]\n",
    "    if Gakuin[\"gakuin\"][-2::] != \"学院\":\n",
    "        OCW[\"学院\"] = \"その他\"\n",
    "    else:\n",
    "        OCW[\"学院\"] = Gakuin[\"gakuin\"]\n",
    "        \n",
    "    return OCW\n",
    "\n",
    "#データベースのカラム情報\n",
    "#科目コードをキーとして持たせたい気持ち\n",
    "column = {# TOP側 #\n",
    "          \"科目コード\":\"LectureCode\", # キー\n",
    "          \"講義名\":\"LectureName\",\n",
    "          \"担当教員名\":\"Professor\",\n",
    "          \"開講元\":\"Department\",\n",
    "          \"曜日・時限(講義室)\":\"DateRoom\",\n",
    "          \"URL\":\"URL\",\n",
    "         #\"講義室\":\"Room\",\n",
    "          \"単位数\":\"Credit\",\n",
    "          \"開講クォーター\":\"Quarter\",\n",
    "          \"使用言語\":\"Language\",\n",
    "          # シラバス側 #\n",
    "          \"授業計画・課題\":\"LecturePlan\",\n",
    "          \"成績評価の基準及び方法\":\"AssessStyle\",\n",
    "          \"履修の条件(知識・技能・履修済科目等)\":\"CourseCond\",\n",
    "          # 検索用 #\n",
    "          \"学院\":\"Gakuin\"}\n",
    "\n",
    "#TABLEをつくる　データベースの構造が完全になったら不要\n",
    "def createTable(column):\n",
    "    with connection.cursor() as cursor:\n",
    "        KEY_COLUMN = \"科目コード\"\n",
    "        KEY_LENGTH = 10\n",
    "        \n",
    "        sub_column = []\n",
    "        for k in column:\n",
    "            if column[k] == KEY_COLUMN:\n",
    "                #key処理そのいち\n",
    "                sub_column.append(column[k]+\" TEXT NOT NULL\")\n",
    "            else:\n",
    "                sub_column.append(column[k]+\" TEXT\")\n",
    "        \n",
    "        sub_column.append(\"PRIMARY KEY({}({}))\".format(column[KEY_COLUMN],KEY_LENGTH)) #key処理そのに\n",
    "        sql = \"CREATE TABLE lecture({});\".format(\",\".join(sub_column))\n",
    "        #print(sql)\n",
    "        cursor.execute(sql)\n",
    "        \n",
    "#デバッグ用　TABLEを削除する\n",
    "def dropTable():\n",
    "    print(\"本当にOCW Tableを削除しますか？(する：y，しない：otherwise)\")\n",
    "    if input()==\"y\":\n",
    "        with connection.cursor() as cursor:\n",
    "            sql = \"DROP TABLE lecture;\"\n",
    "            cursor.execute(sql)\n",
    "    else:\n",
    "        print(\"OCW Tableの削除を中止しました\")\n",
    "        \n",
    "#講義情報をデータベースに格納する\n",
    "def insertLecture(column,LectureData):\n",
    "    with connection.cursor() as cursor:\n",
    "        sql = \"INSERT INTO lecture ({}) \".format(\",\".join(map(lambda x:column[x],column)))\n",
    "        sql += \"VALUES ({}) \".format(\",\".join(map(lambda x:\"\\'{}\\'\".format(LectureData[x]),column)))\n",
    "        sql += \"ON DUPLICATE KEY UPDATE {};\".format(\",\".join([\"{} = VALUES({})\".format(column[k],column[k]) for k in list(filter(lambda x:x!=\"科目コード\",column))]))\n",
    "        cursor.execute(sql)\n",
    "        print(\"\\t\\tUPDATE Lecture:\",LectureData[\"講義名\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "#\"科目コード\":\"LectureCode\", # キー\n",
    "#学院\":\"Gakuin\"\n",
    "\n",
    "def createLforGtable(column):\n",
    "    with connection.cursor() as cursor:\n",
    "        sub_column = []\n",
    "        KEY_LENGTH = 20\n",
    "        sub_column.append(\"{} NVARCHAR({}) NOT NULL\".format(column[\"科目コード\"],KEY_LENGTH))\n",
    "        sub_column.append(\"{} NVARCHAR({}) NOT NULL\".format(column[\"学院\"],KEY_LENGTH))\n",
    "        sub_column.append(\"PRIMARY KEY({},{})\".format(column[\"科目コード\"],column[\"学院\"]))\n",
    "        sql = \"CREATE TABLE LforG({});\".format(\",\".join(sub_column))\n",
    "        #print(sql)\n",
    "        cursor.execute(sql)\n",
    "        \n",
    "def dropLforGtable():\n",
    "    print(\"本当にOCW LforG Tableを削除しますか？(する：y，しない：otherwise)\")\n",
    "    if input()==\"y\":\n",
    "        with connection.cursor() as cursor:\n",
    "            sql = \"DROP TABLE LforG;\"\n",
    "            cursor.execute(sql)\n",
    "    else:\n",
    "        print(\"OCW Tableの削除を中止しました\")\n",
    "        \n",
    "def insertLforG(column,code,gakuin):\n",
    "    with connection.cursor() as cursor:\n",
    "        sql = \"INSERT IGNORE INTO LforG ({},{}) \".format(column[\"科目コード\"],column[\"学院\"])\n",
    "        sql += \"VALUES (\\'{}\\',\\'{}\\') \".format(code,gakuin)\n",
    "        cursor.execute(sql)\n",
    "        print(\"\\t\\tUPDATE LforG:\",code,\"-\",gakuin)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Get GakuinList\n",
      "\tGet Lectures: 理学院\n",
      "\t\tSCRAPE Lecture: 磁気浮上と磁気支持工学\n",
      "\tUPDATE Lecture: 磁気浮上と磁気支持工学\n"
     ]
    }
   ],
   "source": [
    "#OCWスクレイピング実行\n",
    "if __name__=='__main__':\n",
    "    #limit値　越えて設定した場合，要素数ぶんが最大になる\n",
    "    Glimit = 1 #頭からいくつ学院数見るか\n",
    "    Llimit = 1 #頭からいくつ講義詳細見るか\n",
    "    \n",
    "    for Gakuin in getGakuinList()[:Glimit]:\n",
    "        for Lecture in getLectures(Gakuin[\"gakuin\"],Gakuin[\"gakuin_url\"])[:Llimit]:\n",
    "            OCWData = fetch_OCW(Gakuin,Lecture)\n",
    "            insertLecture(column,OCWData)\n",
    "            connection.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "本当にOCW LforG Tableを削除しますか？(する：y，しない：otherwise)\n",
      "a\n",
      "OCW Tableの削除を中止しました\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/anaconda3/lib/python3.6/site-packages/pymysql/cursors.py:329: Warning: (3720, 'NATIONAL/NCHAR/NVARCHAR implies the character set UTF8MB3, which will be replaced by UTF8MB4 in a future release. Please consider using CHAR(x) CHARACTER SET UTF8MB4 in order to be unambiguous.')\n",
      "  self._do_get_result()\n"
     ]
    }
   ],
   "source": [
    "dropLforGtable()\n",
    "createLforGtable(column)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'\\nfor Gakuin in getGakuinList():\\n    for Lecture in getLectures(Gakuin[\"gakuin\"],Gakuin[\"gakuin_url\"]):\\n        insertLforG(column,Lecture[\"code\"],Gakuin[\"gakuin\"])\\n'"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "'''\n",
    "for Gakuin in getGakuinList():\n",
    "    for Lecture in getLectures(Gakuin[\"gakuin\"],Gakuin[\"gakuin_url\"]):\n",
    "        insertLforG(column,Lecture[\"code\"],Gakuin[\"gakuin\"])\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "http://www.ocw.titech.ac.jp/index.php?module=General&action=T0300&GakubuCD=2&GakkaCD=321800&KeiCD=18&KougiCD=201807330&Nendo=2018&lang=JA&vid=03"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "OCWData = fetch_OCW(Gakuin,Lecture)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "本当にOCW Tableを削除しますか？(する：y，しない：otherwise)\n",
      "y\n"
     ]
    }
   ],
   "source": [
    "dropTable()\n",
    "createTable(column)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\tUPDATE Lecture: 解析力学\n"
     ]
    }
   ],
   "source": [
    "insertLecture(column,OCWData)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'\\n#検索テスト\\nwith connection.cursor() as cursor:\\n    sql = \"SELECT * FROM lecture\"\\n    cursor.execute(sql)\\n \\n    dbdata = cursor.fetchall()\\n    for rows in dbdata:\\n        print(rows)\\n'"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "'''\n",
    "#検索テスト\n",
    "with connection.cursor() as cursor:\n",
    "    sql = \"SELECT * FROM lecture\"\n",
    "    cursor.execute(sql)\n",
    " \n",
    "    dbdata = cursor.fetchall()\n",
    "    for rows in dbdata:\n",
    "        print(rows)\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'\\nwith connection.cursor() as cursor:\\n    sql = \"SELECT * FROM LforG\"\\n    cursor.execute(sql)\\n \\n    dbdata = cursor.fetchall()\\n    for rows in dbdata:\\n        print(rows)\\n'"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#検索テスト\n",
    "'''\n",
    "with connection.cursor() as cursor:\n",
    "    sql = \"SELECT * FROM LforG\"\n",
    "    cursor.execute(sql)\n",
    " \n",
    "    dbdata = cursor.fetchall()\n",
    "    for rows in dbdata:\n",
    "        print(rows)\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}