1-concat-csvs-into-sqlite.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "_cell_guid": "b1076dfc-b9ad-4769-8c92-a6c4dae69d19",
    "_uuid": "8f2839f25d086af736a60e9eeb907d3b93b6e0e5"
   },
   "outputs": [],
   "source": [
    "%matplotlib inline\n",
    "from IPython.core.interactiveshell import InteractiveShell\n",
    "InteractiveShell.ast_node_interactivity = \"all\"\n",
    "import sqlite3\n",
    "import os\n",
    "from tqdm import tqdm\n",
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "BASE_PATH =  '/home/eric/jupyter-project/quickdraw-doodle-recognition/'\n",
    "INPUT_PATH = f'{BASE_PATH}/input'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "!mkdir -p ./data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "_cell_guid": "79c7e3d0-c299-4dcb-8224-4455121ee9b0",
    "_uuid": "d629ff2d2480ee46fbb7e2d37f6b5fab8052498a"
   },
   "outputs": [],
   "source": [
    "def f2cat(filename):\n",
    "    return filename.split('.')[0]\n",
    "\n",
    "def list_all_categories():\n",
    "    files = os.listdir(f'{INPUT_PATH}/train_simplified')\n",
    "    return sorted([f2cat(f) for f in files], key=str.lower)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "_uuid": "0c8edb17435ea500d98aef554b38f551cdc17c93"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "340\n"
     ]
    }
   ],
   "source": [
    "categories = list_all_categories()\n",
    "cat2id = {c: i for i, c in enumerate(categories)}\n",
    "print(len(categories))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "rm: impossible de supprimer './data/quickdraw_dataset.db': Aucun fichier ou dossier de ce type\r\n"
     ]
    }
   ],
   "source": [
    "!rm ./data/quickdraw_dataset.db\n",
    "db = sqlite3.connect('./data/quickdraw_dataset.db')\n",
    "c = db.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Maximize performance, we suppose no system crash for the course of this notebook\n",
    "_ = c.execute('PRAGMA JOURNAL_MODE=OFF')\n",
    "_ = c.execute('PRAGMA SYNCHRONOUS=OFF')\n",
    "_ = c.execute('PRAGMA LOCKING_MODE=EXCLUSIVE')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "_ = c.execute('CREATE TABLE classes (y INTEGER PRIMARY KEY, label VARCHAR)')\n",
    "_ = c.execute('CREATE TABLE train (key_id INTEGER PRIMARY KEY, drawing VARCHAR, recognized BOOLEAN, y INTEGER)')\n",
    "_ = c.execute('CREATE TABLE test (key_id INTEGER PRIMARY KEY, drawing VARCHAR)')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 112199/112199 [00:07<00:00, 15284.33it/s]\n"
     ]
    }
   ],
   "source": [
    "df = pd.read_csv(f'{INPUT_PATH}/test_simplified.csv', usecols=['key_id', 'drawing'])\n",
    "df.sort_values('key_id', inplace=True)\n",
    "_ = c.execute('BEGIN TRANSACTION')\n",
    "for index, row in tqdm(df.iterrows(), total=df.shape[0]):\n",
    "    _ = c.execute('INSERT INTO test (key_id, drawing) VALUES (?,?)', (row.key_id, row.drawing))\n",
    "_ = c.execute('COMMIT')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "_uuid": "fa103ea464f8b04cce685a3084cdca920c062797"
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 340/340 [2:50:15<00:00, 66.05s/it]  \n"
     ]
    }
   ],
   "source": [
    "for cat in tqdm(categories):\n",
    "    _ = c.execute('BEGIN TRANSACTION')\n",
    "    y = cat2id[cat]\n",
    "    _ = c.execute('INSERT INTO classes (y, label) VALUES (?,?)', (y, cat))\n",
    "    df = pd.read_csv(f'{INPUT_PATH}/train_simplified/{cat}.csv', usecols=['key_id', 'drawing', 'recognized'])\n",
    "    df.sort_values('key_id', inplace=True)\n",
    "    for index, row in df.iterrows():\n",
    "        _ = c.execute('INSERT INTO train (key_id, drawing, recognized, y) VALUES (?,?,?,?)', (row.key_id, row.drawing, row.recognized, y))\n",
    "    _ = c.execute('COMMIT')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "db.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Sanity check"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "# open database in read only mode\n",
    "db = sqlite3.connect(f'file:{BASE_PATH}/data/quickdraw_dataset.db?mode=ro', uri=True)\n",
    "c = db.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(4503599667216384, '[[[0, 18, 150, 216, 223, 223, 218, 210, 197, 173, 123, 75, 38, 14, 6], [27, 29, 10, 5, 18, 38, 57, 70, 81, 93, 103, 103, 87, 59, 30]], [[1, 0, 9, 24, 90, 131, 166, 191, 222, 234, 246, 249, 249, 246, 231, 209], [28, 43, 75, 97, 120, 126, 125, 120, 101, 91, 67, 52, 29, 20, 6, 0]], [[65, 68, 73], [59, 64, 65]], [[95, 121], [38, 37]], [[126, 141], [64, 63]], [[155, 164], [47, 32]], [[176, 187], [59, 52]], [[200, 203], [12, 8]], [[240, 252, 255, 254, 248, 225, 195], [16, 24, 31, 50, 65, 87, 105]], [[34, 40], [48, 52]], [[56, 76], [29, 40]], [[78, 79, 88], [73, 78, 80]], [[117, 143], [82, 84]], [[161, 173], [78, 78]], [[186, 194], [42, 28]], [[149, 151], [20, 20]], [[92, 95], [62, 62]]]', 1, 329)\n",
      "CPU times: user 1.1 ms, sys: 122 µs, total: 1.22 ms\n",
      "Wall time: 807 µs\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "c.execute('SELECT key_id, drawing, recognized, y FROM train LIMIT 5')\n",
    "print(c.fetchone())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "db.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.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}