Last active
July 25, 2017 01:47
-
-
Save yamasakih/762a942d215c915e2c199730f6b7d504 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
{ | |
"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のデータは全て消えてしまうようだ。以下の例を実行するともうstudentsテーブルは消えてしまっているのでエラーとなる。" | |
] | |
}, | |
{ | |
"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メソッドを実行しない限りセルをまたがってもデータは消えていない。以下の例はIn[4]で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 | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment