170725_1-using_sqlite3_with_in_memory.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "SQLite3をin memoryで使える機能があるそうなので試してみる。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import sqlite3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "SQLite3ライブラリの[ドキュメント](https://docs.python.org/3/library/sqlite3.html)の例を行ってみる。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "connect関数で:memory:と指定するとin memoryでSQLite3が利用できる。以下の例はin memoryでSQLite3データベースサーバーを立ち上げ、SQL文を入力することでテーブルの作成やデータのinsert, select, delete, updateなどができる。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Enter your SQL commands to execute in sqlite3.\n",
      "Enter a blank line to exit.\n",
      "CREATE TABLE students (id int, name text);\n",
      "INSERT INTO students VALUES (1, \"yamasakih\"), (2, \"kandam\");\n",
      "SELECT * FROM students;\n",
      "[(1, 'yamasakih'), (2, 'kandam')]\n",
      "\n"
     ]
    }
   ],
   "source": [
    "con = sqlite3.connect(\":memory:\")\n",
    "con.isolation_level = None\n",
    "cur = con.cursor()\n",
    "\n",
    "buffer = \"\"\n",
    "\n",
    "print(\"Enter your SQL commands to execute in sqlite3.\")\n",
    "print(\"Enter a blank line to exit.\")\n",
    "\n",
    "while True:\n",
    "    line = input()\n",
    "    if line == \"\":\n",
    "        break\n",
    "    buffer += line\n",
    "    if sqlite3.complete_statement(buffer):\n",
    "        try:\n",
    "            buffer = buffer.strip()\n",
    "            cur.execute(buffer)\n",
    "\n",
    "            if buffer.lstrip().upper().startswith(\"SELECT\"):\n",
    "                print(cur.fetchall())\n",
    "        except sqlite3.Error as e:\n",
    "            print(\"An error occurred:\", e.args[0])\n",
    "        buffer = \"\"\n",
    "\n",
    "con.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "closeメソッドを実行するとin memoryのデータは全て消えてしまうようだ。以下の例を実行するともうstudnetsテーブルは消えてしまっているのでエラーとなる。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "ename": "OperationalError",
     "evalue": "no such table: students",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mOperationalError\u001b[0m                          Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-3-c470a9200276>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[1;32m      2\u001b[0m \u001b[0mcon\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0misolation_level\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      3\u001b[0m \u001b[0mcur\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mcon\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcursor\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 4\u001b[0;31m \u001b[0mcur\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'SELECT * FROM students'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      5\u001b[0m \u001b[0mprint\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mcur\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mfetchall\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;31mOperationalError\u001b[0m: no such table: students"
     ]
    }
   ],
   "source": [
    "con = sqlite3.connect(\":memory:\")\n",
    "con.isolation_level = None\n",
    "cur = con.cursor()\n",
    "cur.execute('SELECT * FROM students')\n",
    "print(cur.fetchall())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x2b4d38265ab0>"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.execute('CREATE TABLE students (id int, name text)')\n",
    "cur.execute('INSERT INTO students VALUES (1, \"yamasakih\"), (2, \"kandam\")')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "con.closeメソッドを実行しない限りセルをまたがってもデータは消えていない。以下の例は一つ上のセルでstudentsテーブルを作成し、データを入れているのでエラーとならない。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(1, 'yamasakih'), (2, 'kandam')]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.execute('SELECT * FROM students')\n",
    "cur.fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "最後にcloseメソッドを実行しておく。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "cur.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "SQLite3のin memoryを利用することで一時的にテーブルを作成し高速にプログラムを実行することが可能となるかもしれない。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "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.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}