datasets.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# IMPORTS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import csv\n",
    "import gzip\n",
    "import pickle\n",
    "import shelve\n",
    "import sqlite3\n",
    "\n",
    "from tinymongo import TinyMongoClient\n",
    "from tinymongo.errors import DuplicateKeyError"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# DATASETS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "IMDB = 'datasets/imdb'\n",
    "\n",
    "\n",
    "SUBQUERY = \"\"\"\n",
    "    SELECT tconst FROM basics\n",
    "    WHERE isAdult = 0\n",
    "    AND startYear = 2017\n",
    "    AND titleType = 'movie'\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "def read_gzip(root, file):\n",
    "    path = os.path.join(root, file)\n",
    "\n",
    "    with gzip.open(path, \"rt\") as r:\n",
    "        reader = csv.reader(r, delimiter=\"\\t\", quoting=csv.QUOTE_NONE)\n",
    "        next(reader)  # skip first row\n",
    "\n",
    "        for row in reader:\n",
    "            yield [col if col != \"\\\\N\" else None for col in row]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# DATABASE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "CONN = sqlite3.connect('datasets/imdb.sqlite')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "def create_tables(conn):\n",
    "    print(\"[*] Setting options ...\")\n",
    "    conn.executescript(\n",
    "        \"\"\"\n",
    "    PRAGMA journal_mode=off;\n",
    "    PRAGMA synchronous=off;\n",
    "        \"\"\"\n",
    "    )\n",
    "    conn.commit()\n",
    "\n",
    "    print(\"[*] Creating tables ...\")\n",
    "    conn.executescript(\n",
    "        \"\"\"\n",
    "        CREATE TABLE IF NOT EXISTS names (\n",
    "            nconst TEXT NOT NULL,\n",
    "            primaryName TEXT NOT NULL,\n",
    "            birthYear INTEGER,\n",
    "            deathYear INTEGER,\n",
    "            primaryProfession TEXT NOT NULL,\n",
    "            knownForTitles TEXT,\n",
    "            PRIMARY KEY(nconst)\n",
    "        );\n",
    "        CREATE TABLE IF NOT EXISTS basics (\n",
    "            tconst TEXT NOT NULL,\n",
    "            titleType TEXT NOT NULL,\n",
    "            primaryTitle TEXT NOT NULL,\n",
    "            originalTitle TEXT,\n",
    "            isAdult BOOLEAN NOT NULL,\n",
    "            startYear INTEGER,\n",
    "            endYear INTEGER,\n",
    "            runtimeMinutes INTEGER,\n",
    "            genres TEXT,\n",
    "            PRIMARY KEY(tconst)\n",
    "        );\n",
    "        CREATE TABLE IF NOT EXISTS akas (\n",
    "            titleId TEXT NOT NULL,\n",
    "            ordering INTEGER NOT NULL,\n",
    "            title TEX NOT NULL,\n",
    "            region TEXT,\n",
    "            language TEXT,\n",
    "            types TEXT,\n",
    "            attributes TEXT,\n",
    "            isOriginalTitle BOOLEAN,\n",
    "            PRIMARY KEY(titleId, ordering)\n",
    "        );\n",
    "        CREATE TABLE IF NOT EXISTS ratings (\n",
    "            tconst TEXT NOT NULL,\n",
    "            averageRating REAL NOT NULL,\n",
    "            numVotes INTEGER,\n",
    "            PRIMARY KEY(tconst)\n",
    "        );\n",
    "        CREATE TABLE IF NOT EXISTS episode (\n",
    "            tconst TEXT NOT NULL,\n",
    "            parentTconst TEXT NOT NULL,\n",
    "            seasonNumber INTEGER,\n",
    "            episodeNumber INTEGER,\n",
    "            PRIMARY KEY(tconst, parentTconst)\n",
    "        );\n",
    "        CREATE TABLE IF NOT EXISTS writers (\n",
    "            tconst TEXT NOT NULL,\n",
    "            nconst TEXT NOT NULL,\n",
    "            PRIMARY KEY(tconst, nconst)\n",
    "        );\n",
    "        CREATE TABLE IF NOT EXISTS directors (\n",
    "            tconst TEXT NOT NULL,\n",
    "            nconst TEXT NOT NULL,\n",
    "            PRIMARY KEY(tconst, nconst)\n",
    "        );\n",
    "        CREATE TABLE IF NOT EXISTS principals (\n",
    "            tconst TEXT NOT NULL,\n",
    "            ordering INTEGER NOT NULL,\n",
    "            nconst TEXT NOT NULL,\n",
    "            category TEXT NOT NULL,\n",
    "            job TEXT,\n",
    "            characters TEXT,\n",
    "            PRIMARY KEY(tconst, ordering)\n",
    "        );\n",
    "        \"\"\"\n",
    "    )\n",
    "    conn.commit()\n",
    "\n",
    "\n",
    "def insert_values(conn, root):\n",
    "    print(\"[*] Processing names material ...\")\n",
    "    for row in read_gzip(root, \"name.basics.tsv.gz\"):\n",
    "        conn.execute(\"INSERT INTO names VALUES (?,?,?,?,?,?)\", row)\n",
    "    conn.commit()\n",
    "\n",
    "    print(\"[*] Processing basics material ...\")\n",
    "    for row in read_gzip(root, \"title.basics.tsv.gz\"):\n",
    "        conn.execute(\"INSERT INTO basics VALUES (?,?,?,?,?,?,?,?,?)\", row)\n",
    "    conn.commit()\n",
    "\n",
    "    print(\"[*] Processing akas material ...\")\n",
    "    for row in read_gzip(root, \"title.akas.tsv.gz\"):\n",
    "        conn.execute(\"INSERT INTO akas VALUES (?,?,?,?,?,?,?,?)\", row)\n",
    "    conn.commit()\n",
    "\n",
    "    print(\"[*] Processing ratings material ...\")\n",
    "    for row in read_gzip(root, \"title.ratings.tsv.gz\"):\n",
    "        conn.execute(\"INSERT INTO ratings VALUES (?,?,?)\", row)\n",
    "    conn.commit()\n",
    "\n",
    "    print(\"[*] Processing episode material ...\")\n",
    "    for row in read_gzip(root, \"title.episode.tsv.gz\"):\n",
    "        conn.execute(\"INSERT INTO episode VALUES (?,?,?,?)\", row)\n",
    "    conn.commit()\n",
    "\n",
    "    print(\"[*] Processing principals material ...\")\n",
    "    for row in read_gzip(root, \"title.principals.tsv.gz\"):\n",
    "        conn.execute(\"INSERT INTO principals VALUES (?,?,?,?,?,?)\", row)\n",
    "    conn.commit()\n",
    "\n",
    "    print(\"[*] Processing crew material ...\")\n",
    "    for row in read_gzip(root, \"title.crew.tsv.gz\"):\n",
    "        tconst, directors, writers = row\n",
    "\n",
    "        if writers is not None:\n",
    "            for writer in writers.split(\",\"):\n",
    "                conn.execute(\"INSERT INTO writers VALUES (?,?)\", (tconst, writer))\n",
    "\n",
    "        if directors is not None:\n",
    "            for director in directors.split(\",\"):\n",
    "                conn.execute(\"INSERT INTO directors VALUES (?,?)\", (tconst, director))\n",
    "    conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[*] Setting options ...\n",
      "[*] Creating tables ...\n",
      "[*] Processing names material ...\n",
      "[*] Processing basics material ...\n",
      "[*] Processing akas material ...\n",
      "[*] Processing ratings material ...\n",
      "[*] Processing episode material ...\n",
      "[*] Processing principals material ...\n",
      "[*] Processing crew material ...\n"
     ]
    }
   ],
   "source": [
    "create_tables(CONN)\n",
    "insert_values(CONN, IMDB)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# P1. SQL REMINDERS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "P1 = sqlite3.connect('datasets/p1.sqlite')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "def insert_values_p1(inconn, outconn):\n",
    "    print(\"[*] Processing names relation ...\")\n",
    "    for row in inconn.execute(\n",
    "        \"\"\"\n",
    "        SELECT * FROM names WHERE nconst IN (\n",
    "            SELECT nconst FROM writers WHERE tconst IN (%s)\n",
    "            UNION SELECT nconst FROM directors WHERE tconst IN (%s)\n",
    "            UNION SELECT nconst FROM principals WHERE tconst IN (%s)\n",
    "        )\n",
    "        \"\"\"\n",
    "        % (SUBQUERY, SUBQUERY, SUBQUERY)\n",
    "    ).fetchall():\n",
    "        outconn.execute(\"INSERT INTO names VALUES (?,?,?,?,?,?)\", row)\n",
    "    outconn.commit()\n",
    "\n",
    "    print(\"[*] Processing basics relation ...\")\n",
    "    for row in inconn.execute(\n",
    "        \"\"\"SELECT * FROM basics WHERE tconst IN (%s)\"\"\" % (SUBQUERY,)\n",
    "    ).fetchall():\n",
    "        outconn.execute(\"INSERT INTO basics VALUES (?,?,?,?,?,?,?,?,?)\", row)\n",
    "    outconn.commit()\n",
    "\n",
    "    print(\"[*] Processing akas relation ...\")\n",
    "    for row in inconn.execute(\n",
    "        \"\"\"SELECT * FROM akas WHERE titleId IN (%s)\"\"\" % (SUBQUERY,)\n",
    "    ).fetchall():\n",
    "        outconn.execute(\"INSERT INTO akas VALUES (?,?,?,?,?,?,?,?)\", row)\n",
    "    outconn.commit()\n",
    "\n",
    "    print(\"[*] Processing ratings relation ...\")\n",
    "    for row in inconn.execute(\n",
    "        \"\"\"SELECT * FROM ratings WHERE tconst IN (%s)\"\"\" % (SUBQUERY,)\n",
    "    ).fetchall():\n",
    "        outconn.execute(\"INSERT INTO ratings VALUES (?,?,?)\", row)\n",
    "    outconn.commit()\n",
    "\n",
    "    print(\"[*] Processing writers relation ...\")\n",
    "    for row in inconn.execute(\n",
    "        \"\"\"SELECT * FROM writers WHERE tconst IN (%s)\"\"\" % (SUBQUERY)\n",
    "    ).fetchall():\n",
    "        outconn.execute(\"INSERT INTO writers VALUES (?,?)\", row)\n",
    "    outconn.commit()\n",
    "\n",
    "    print(\"[*] Processing directors relation ...\")\n",
    "    for row in inconn.execute(\n",
    "        \"\"\"SELECT * FROM directors WHERE tconst IN (%s)\"\"\" % (SUBQUERY,)\n",
    "    ).fetchall():\n",
    "        outconn.execute(\"INSERT INTO directors VALUES (?,?)\", row)\n",
    "    outconn.commit()\n",
    "\n",
    "    print(\"[*] Processing principals relation ...\")\n",
    "    for row in inconn.execute(\n",
    "        \"\"\"SELECT * FROM principals WHERE tconst IN (%s)\"\"\" % (SUBQUERY,)\n",
    "    ).fetchall():\n",
    "        outconn.execute(\"INSERT INTO principals VALUES (?,?,?,?,?,?)\", row)\n",
    "    outconn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[*] Setting options ...\n",
      "[*] Creating tables ...\n",
      "[*] Processing names relation ...\n",
      "[*] Processing basics relation ...\n",
      "[*] Processing akas relation ...\n",
      "[*] Processing ratings relation ...\n",
      "[*] Processing writers relation ...\n",
      "[*] Processing directors relation ...\n",
      "[*] Processing principals relation ...\n"
     ]
    }
   ],
   "source": [
    "create_tables(P1)\n",
    "insert_values_p1(P1, CONN)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# P2: SQL INTERNALS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "P2 = open('datasets/p2.pickle', \"wb\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "def insert_values_p2(inconn, outconn):\n",
    "    coll = dict()\n",
    "    \n",
    "    print(\"[*] Processing names relation ...\")\n",
    "    coll[\"names\"] = inconn.execute(\"\"\"SELECT * FROM names\"\"\").fetchall()\n",
    "\n",
    "    print(\"[*] Processing basics relation ...\")\n",
    "    coll[\"basics\"] = inconn.execute(\"\"\"SELECT * FROM basics\"\"\").fetchall()\n",
    "\n",
    "    print(\"[*] Processing akas relation ...\")\n",
    "    coll[\"akas\"] = inconn.execute(\"\"\"SELECT * FROM akas\"\"\").fetchall()\n",
    "\n",
    "    print(\"[*] Processing ratings relation ...\")\n",
    "    coll[\"ratings\"] = inconn.execute(\"\"\"SELECT * FROM ratings\"\"\").fetchall()\n",
    "\n",
    "    print(\"[*] Processing writers relation ...\")\n",
    "    coll[\"writers\"] = inconn.execute(\"\"\"SELECT * FROM writers\"\"\").fetchall()\n",
    "\n",
    "    print(\"[*] Processing directors relation ...\")\n",
    "    coll[\"directors\"] = inconn.execute(\"\"\"SELECT * FROM directors\"\"\").fetchall()\n",
    "\n",
    "    print(\"[*] Processing principals relation ...\")\n",
    "    coll[\"principals\"] = inconn.execute(\"\"\"SELECT * FROM principals\"\"\").fetchall()\n",
    "    \n",
    "    pickle.dump(coll, outconn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[*] Processing names relation ...\n",
      "[*] Processing basics relation ...\n",
      "[*] Processing akas relation ...\n",
      "[*] Processing ratings relation ...\n",
      "[*] Processing writers relation ...\n",
      "[*] Processing directors relation ...\n",
      "[*] Processing principals relation ...\n"
     ]
    }
   ],
   "source": [
    "insert_values_p2(P1, P2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# P3. NOSQL DATABASES"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "def insert_values_p3_mongo(inconn, outconn):\n",
    "    coll = list()\n",
    "    database = outconn['p3']\n",
    "     \n",
    "    print(\"[*] Processing names relation with mongo...\")\n",
    "    for row in inconn.execute(\n",
    "        \"\"\"SELECT * FROM names ORDER BY birthYear DESC LIMIT 10000\"\"\"\n",
    "    ).fetchall():\n",
    "        nconst, primaryName, birthYear, deathYear, primaryProfession, knownForTitles = (\n",
    "            row\n",
    "        )\n",
    "\n",
    "        coll.append(\n",
    "            {\n",
    "                \"_id\": nconst,\n",
    "                \"primaryName\": primaryName,\n",
    "                \"birthYear\": birthYear,\n",
    "                \"deathYear\": deathYear,\n",
    "                \"knownForTitles\": knownForTitles.split(\",\") if knownForTitles else [],\n",
    "                \"primaryProfession\": primaryProfession.split(\",\")\n",
    "                if primaryProfession\n",
    "                else [],\n",
    "            }\n",
    "        )\n",
    "    database.names.insert_many(coll, bypass_document_validation=True)\n",
    "\n",
    "\n",
    "def insert_values_p3_shelve(inconn, outconn):\n",
    "    print(\"[*] Processing basics relation with shelve ...\")\n",
    "    for row in inconn.execute(\"\"\"SELECT * FROM basics\"\"\").fetchall():\n",
    "        tconst, titleType, primaryTitle, originalTitle, isAdult, startYear, endYear, runtimeMinutes, genres = (\n",
    "            row\n",
    "        )\n",
    "\n",
    "        outconn[tconst] = {\n",
    "            \"_id\": tconst,\n",
    "            \"titleType\": titleType,\n",
    "            \"primaryTitle\": primaryTitle,\n",
    "            \"originalTitle\": originalTitle,\n",
    "            \"isAdult\": isAdult,\n",
    "            \"startYear\": startYear,\n",
    "            \"endYear\": endYear,\n",
    "            \"runtimeMinutes\": runtimeMinutes,\n",
    "            \"genres\": genres.split(\",\") if genres else [],\n",
    "        }"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[*] Processing names relation with mongo...\n",
      "[*] Processing basics relation with shelve ...\n"
     ]
    }
   ],
   "source": [
    "P3_MONGO = TinyMongoClient('datasets')\n",
    "P3_SHELVE = shelve.open('datasets/p3.shelve', \"c\")\n",
    "\n",
    "insert_values_p3_mongo(CONN, P3_MONGO)\n",
    "insert_values_p3_shelve(CONN, P3_SHELVE)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'DONE'"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "CONN.close()\n",
    "\n",
    "P1.close()\n",
    "P2.close()\n",
    "P3_MONGO.close()\n",
    "P3_SHELVE.close()\n",
    "\n",
    "\"DONE\""
   ]
  },
  {
   "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.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}