extract_wikipedia_hierarchy.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Pixiedust database opened successfully\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "\n",
       "        <div style=\"margin:10px\">\n",
       "            <a href=\"https://github.com/ibm-watson-data-lab/pixiedust\" target=\"_new\">\n",
       "                <img src=\"https://github.com/ibm-watson-data-lab/pixiedust/raw/master/docs/_static/pd_icon32.png\" style=\"float:left;margin-right:10px\"/>\n",
       "            </a>\n",
       "            <span>Pixiedust version 1.1.11</span>\n",
       "        </div>\n",
       "        "
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import pixiedust\n",
    "import time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "fetch root category\n"
     ]
    }
   ],
   "source": [
    "#!/usr/bin/env python\n",
    "# -*- coding: utf-8 -*-\n",
    "\n",
    "import os\n",
    "import MySQLdb\n",
    "import csv\n",
    "import sys\n",
    "\n",
    "class WikipediaHelper:\n",
    "    @staticmethod\n",
    "    def fetch_cat(cursor, cat_title):\n",
    "        sql = 'select cat_id, cat_title from category where cat_title = %s'\n",
    "        cursor.execute(sql, args=[cat_title.encode('utf-8')])\n",
    "        for row in cursor.fetchall():\n",
    "            return {'cat_id': row[0], 'cat_title': row[1].decode('utf-8'), 'parent_cat_id': ''}\n",
    "\n",
    "    @staticmethod\n",
    "    def fetch_and_write_cat(cursor, cat_hash, writer , s=set()):\n",
    "        \n",
    "        subcat = \"subca\"\n",
    "        \n",
    "        l = len(s)\n",
    "        s.add('%d_%s' % (cat_hash['cat_id'], cat_hash['parent_cat_id']))\n",
    "        if l == len(s):\n",
    "            return\n",
    "        \n",
    "        # write category\n",
    "        # print('write category %s' % cat_hash)\n",
    "        writer.writeline(cat_hash)\n",
    "        sql = 'select cat2.cat_id, page.page_title from categorylinks ' \\\n",
    "                'join page on page.page_id = categorylinks.cl_from ' \\\n",
    "                'join category on categorylinks.cl_to = category.cat_title ' \\\n",
    "                'inner join category as cat2 on cat2.cat_title = page.page_title ' \\\n",
    "                  'where categorylinks.cl_type = %s and category.cat_title = %s and page.page_title <> %s;'\n",
    "        cursor.execute(sql,\n",
    "                           args=\n",
    "                           [\n",
    "                               subcat,\n",
    "                               cat_hash['cat_title'].encode('utf-8'),\n",
    "                               cat_hash['cat_title'].encode('utf-8'),\n",
    "                           ])\n",
    "        for row in cursor.fetchall():\n",
    "            childhash = {'cat_id': row[0], 'cat_title': row[1].decode('utf-8'), 'parent_cat_id': cat_hash['cat_id']}\n",
    "            WikipediaHelper.fetch_and_write_cat(cursor=cursor, cat_hash=childhash, writer=writer, s=s)\n",
    "\n",
    "\n",
    "class CsvSaver:\n",
    "    def __init__(self):\n",
    "        pass\n",
    "\n",
    "    def set_filepath(self, filepath):\n",
    "        self.filepath = filepath\n",
    "\n",
    "    def set_header(self, header):\n",
    "        self.header = header\n",
    "\n",
    "    def init(self):\n",
    "        if os.path.isfile(self.filepath):\n",
    "            os.remove(self.filepath)\n",
    "\n",
    "        self.f = open(self.filepath, 'w', encoding='utf-8')\n",
    "        self.w = csv.DictWriter(self.f, self.header)\n",
    "        self.w.writeheader()\n",
    "\n",
    "    def writeline(self, hash):\n",
    "        self.w.writerow(hash)\n",
    "\n",
    "    def finalize(self):\n",
    "        self.f.close()\n",
    "\n",
    "        \n",
    "start = time.time()       \n",
    "        \n",
    "# for stack overflow measure\n",
    "sys.setrecursionlimit(20000)\n",
    "\n",
    "\n",
    "conn = MySQLdb.connect(\n",
    "    user='root',\n",
    "    passwd='passwd',\n",
    "    host='localhost',\n",
    "    database='wiki'\n",
    ")\n",
    "\n",
    "c = conn.cursor()\n",
    "\n",
    "# 1. init writer\n",
    "writer = CsvSaver()\n",
    "writer.set_header(('cat_id', 'cat_title', 'parent_cat_id'))\n",
    "writer.set_filepath('categories.csv')\n",
    "writer.init()\n",
    "\n",
    "# 1. fetch root category\n",
    "print('fetch root category')\n",
    "root_cat = WikipediaHelper.fetch_cat(c, 'Ana_kategoriler')\n",
    "# 2. fetch and writer category recursively\n",
    "WikipediaHelper.fetch_and_write_cat(cursor=c, cat_hash=root_cat, writer=writer)\n",
    "\n",
    "c.close()\n",
    "conn.close()\n",
    "\n",
    "stop = time.time()       "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'cat_id': 8260, 'cat_title': 'Ana_kategoriler', 'parent_cat_id': ''}"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "root_cat"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1351.7709324359894"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stop - start"
   ]
  }
 ],
 "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.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}