Skip to content

Instantly share code, notes, and snippets.

@messa

messa/PyWorking_SQL_demo.ipynb

Last active April 8, 2017 13:02
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 2 You must be signed in to fork a gist
  • Save messa/bb6063214182b922fcdc66bcb1d69e27 to your computer and use it in GitHub Desktop.
Save messa/bb6063214182b922fcdc66bcb1d69e27 to your computer and use it in GitHub Desktop.
Download ZIP
Display the source blob
Display the rendered blob
Raw
PyWorking_SQL_demo.ipynb
{
"cells": [
{
"cell_type": "code",
"execution_count": 3,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"import sqlite3\n",
"from tabulate import tabulate"
]
},
{
"cell_type": "code",
"execution_count": 4,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"conn = sqlite3.connect(':memory:')\n",
"c = conn.cursor()"
]
},
{
"cell_type": "code",
"execution_count": 6,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"[(1,)]\n"
]
}
],
"source": [
"c.execute('SELECT 1')\n",
"print(c.fetchall())"
]
},
{
"cell_type": "code",
"execution_count": 7,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"def run(*args):\n",
" c.execute(*args)\n",
" rows = c.fetchall()\n",
" if rows:\n",
" column_names = [desc[0] for desc in c.description]\n",
" print(tabulate(rows, headers=column_names))"
]
},
{
"cell_type": "code",
"execution_count": 8,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"run('''\n",
" CREATE TABLE groceries (\n",
" id INTEGER PRIMARY KEY,\n",
" name TEXT,\n",
" quantity INTEGER,\n",
" aisle INTEGER)\n",
"''')"
]
},
{
"cell_type": "code",
"execution_count": 9,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"run('INSERT INTO groceries VALUES (1, \"Bananas\", 4, 7)')"
]
},
{
"cell_type": "code",
"execution_count": 10,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"run('INSERT INTO groceries VALUES (2, \"Peanut Butter\", 1, 4)')"
]
},
{
"cell_type": "code",
"execution_count": 11,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"run('INSERT INTO groceries VALUES (3, \"Dark chocolate bars\", 2, 2)')"
]
},
{
"cell_type": "code",
"execution_count": 14,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" id name quantity aisle\n",
"---- ------------------- ---------- -------\n",
" 3 Dark chocolate bars 2 2\n",
" 2 Peanut Butter 1 4\n",
" 1 Bananas 4 7\n"
]
}
],
"source": [
"run('SELECT * FROM groceries ORDER BY id DESC')"
]
},
{
"cell_type": "code",
"execution_count": 15,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"run('INSERT INTO groceries VALUES(4, \"Ice cream\", 1, 12)')\n",
"run('INSERT INTO groceries VALUES(5, \"Cherries\", 6, 2)')\n",
"run('INSERT INTO groceries VALUES(6, \"Chocolate syrup\", 1, 4)')"
]
},
{
"cell_type": "code",
"execution_count": 16,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" id name quantity aisle\n",
"---- ------------------- ---------- -------\n",
" 1 Bananas 4 7\n",
" 2 Peanut Butter 1 4\n",
" 3 Dark chocolate bars 2 2\n",
" 4 Ice cream 1 12\n",
" 5 Cherries 6 2\n",
" 6 Chocolate syrup 1 4\n"
]
}
],
"source": [
"run('SELECT * FROM groceries')"
]
},
{
"cell_type": "code",
"execution_count": 18,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" id name quantity aisle\n",
"---- ------------------- ---------- -------\n",
" 3 Dark chocolate bars 2 2\n",
" 5 Cherries 6 2\n",
" 2 Peanut Butter 1 4\n",
" 6 Chocolate syrup 1 4\n",
" 1 Bananas 4 7\n"
]
}
],
"source": [
"run('SELECT * FROM groceries WHERE aisle > 1 AND aisle < 9 ORDER BY aisle')"
]
},
{
"cell_type": "code",
"execution_count": 20,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" SUM(quantity)\n",
"---------------\n",
" 7\n"
]
}
],
"source": [
"run('SELECT SUM(quantity) FROM groceries WHERE aisle > 3')"
]
},
{
"cell_type": "code",
"execution_count": 23,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" COUNT(quantity)\n",
"-----------------\n",
" 6\n"
]
}
],
"source": [
"run('SELECT COUNT(quantity) FROM groceries')"
]
},
{
"cell_type": "code",
"execution_count": 24,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"<sqlite3.Cursor at 0x10c4ee880>"
]
},
"execution_count": 24,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"c.executescript('''\n",
"CREATE TABLE students (\n",
" id INTEGER PRIMARY KEY,\n",
" first_name TEXT,\n",
" last_name TEXT,\n",
" email TEXT,\n",
" phone TEXT,\n",
" birthdate TEXT);\n",
"\n",
"INSERT INTO students (first_name, last_name, email, phone, birthdate)\n",
" VALUES (\"Peter\", \"Rabbit\", \"peter@rabbit.com\", \"555-6666\", \"2002-06-24\");\n",
"INSERT INTO students (first_name, last_name, email, phone, birthdate)\n",
" VALUES (\"Alice\", \"Wonderland\", \"alice@wonderland.com\", \"555-4444\", \"2002-07-04\");\n",
"INSERT INTO students (first_name, last_name, email, phone, birthdate)\n",
" VALUES (\"Joe\", \"Smith\", \"joe@example.com\", \"555-7777\", \"2002-03-04\");\n",
"\n",
"CREATE TABLE student_grades (id INTEGER PRIMARY KEY,\n",
" student_id INTEGER,\n",
" test TEXT,\n",
" grade INTEGER);\n",
"\n",
"INSERT INTO student_grades (student_id, test, grade)\n",
" VALUES (1, \"Nutrition\", 95);\n",
"INSERT INTO student_grades (student_id, test, grade)\n",
" VALUES (2, \"Nutrition\", 92);\n",
"INSERT INTO student_grades (student_id, test, grade)\n",
" VALUES (1, \"Chemistry\", 85);\n",
"INSERT INTO student_grades (student_id, test, grade)\n",
" VALUES (2, \"Chemistry\", 95);\n",
"''')"
]
},
{
"cell_type": "code",
"execution_count": 27,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" id student_id test grade\n",
"---- ------------ --------- -------\n",
" 1 1 Nutrition 95\n",
" 2 2 Nutrition 92\n",
" 3 1 Chemistry 85\n",
" 4 2 Chemistry 95\n",
" id first_name last_name\n",
"---- ------------ -----------\n",
" 1 Peter Rabbit\n",
" 2 Alice Wonderland\n",
" 3 Joe Smith\n"
]
}
],
"source": [
"run('SELECT * FROM student_grades')\n",
"run('SELECT id, first_name, last_name FROM students')"
]
},
{
"cell_type": "code",
"execution_count": 35,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" id first_name test grade\n",
"---- ------------ --------- -------\n",
" 1 Peter Chemistry 85\n",
" 1 Peter Nutrition 95\n",
" 2 Alice Chemistry 95\n",
" 2 Alice Nutrition 92\n",
" 3 Joe\n"
]
}
],
"source": [
"run('''\n",
" SELECT students.id, students.first_name, test, grade \n",
" FROM students \n",
" LEFT JOIN student_grades \n",
" ON (students.id = student_grades.student_id) ORDER BY students.id ''')\n"
]
},
{
"cell_type": "code",
"execution_count": 34,
"metadata": {},
"outputs": [],
"source": [
"#run(''' SELECT * FROM students, student_grades WHERE ''')"
]
},
{
"cell_type": "code",
"execution_count": 18,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" id grade\n",
"---- -------\n",
" 1 85\n",
" 1 95\n",
" 2 92\n",
" 2 95\n",
" 3\n"
]
}
],
"source": [
"run('''\n",
" SELECT students.id, grade \n",
" FROM students \n",
" LEFT JOIN student_grades \n",
" ON (students.id = student_grades.student_id) ''')"
]
},
{
"cell_type": "code",
"execution_count": 37,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" id name quantity aisle\n",
"---- ------------------- ---------- -------\n",
" 4 Ice cream 1 12\n",
" 1 Bananas 4 7\n",
" 2 Peanut Butter 1 4\n",
" 6 Chocolate syrup 1 4\n",
" 5 Cherries 6 2\n",
" 3 Dark chocolate bars 2 2\n"
]
}
],
"source": [
"run('''\n",
" SELECT * FROM groceries\n",
" ORDER BY aisle DESC, quantity DESC\n",
"''')"
]
},
{
"cell_type": "code",
"execution_count": 41,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" id name quantity aisle\n",
"---- --------------- ---------- -------\n",
" 5 Cherries 6 2\n",
" 6 Chocolate syrup 1 4\n"
]
}
],
"source": [
"run('SELECT * FROM groceries ORDER BY id LIMIT 4, 2')"
]
},
{
"cell_type": "code",
"execution_count": 65,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"name aisle aisle_desc\n",
"------------------- ------- ------------\n",
"Bananas 7\n",
"Peanut Butter 4 uprostred\n",
"Dark chocolate bars 2\n",
"Ice cream 12\n",
"Cherries 2\n",
"Chocolate syrup 4 uprostred\n"
]
}
],
"source": [
"run('''\n",
" SELECT \n",
" name, aisle, \n",
" CASE aisle \n",
" --WHEN 2 THEN \"vepredu\" \n",
" WHEN 4 THEN \"uprostred\" \n",
" --ELSE 'jinde'\n",
" END AS aisle_desc\n",
" FROM groceries\n",
"''')"
]
},
{
"cell_type": "code",
"execution_count": 47,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"DATE(\"now\")\n",
"-------------\n",
"2017-04-08\n"
]
}
],
"source": [
"run('SELECT DATE(\"now\")')"
]
},
{
"cell_type": "code",
"execution_count": 49,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" id name quantity aisle\n",
"---- ------------------- ---------- -------\n",
" 1 Bananas 4 7\n",
" 2 Peanut Butter 1 4\n",
" 3 Dark chocolate bars 2 2\n",
" 4 Ice cream 1 12\n",
" 5 Cherries 6 2\n",
" 6 Chocolate syrup 1 4\n"
]
}
],
"source": [
"run('''SELECT * FROM groceries;''')"
]
},
{
"cell_type": "code",
"execution_count": 53,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" id name quantity aisle COUNT(id) SUM(quantity)\n",
"---- --------------- ---------- ------- ----------- ---------------\n",
" 5 Cherries 6 2 2 8\n",
" 6 Chocolate syrup 1 4 2 2\n",
" 1 Bananas 4 7 1 4\n",
" 4 Ice cream 1 12 1 1\n"
]
}
],
"source": [
"run('''SELECT *, COUNT(id), SUM(quantity) FROM groceries GROUP BY aisle''')"
]
},
{
"cell_type": "code",
"execution_count": 55,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" aisle COUNT(id) SUM(quantity)\n",
"------- ----------- ---------------\n",
" 2 1 2\n",
" 4 2 2\n",
" 7 1 4\n",
" 12 1 1\n"
]
}
],
"source": [
"run('''SELECT aisle, COUNT(id), SUM(quantity)\n",
"FROM groceries WHERE quantity < 5 GROUP BY aisle''')"
]
},
{
"cell_type": "code",
"execution_count": 57,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" aisle COUNT(id) SUM(quantity)\n",
"------- ----------- ---------------\n",
" 4 2 2\n",
" 7 1 4\n",
" 12 1 1\n"
]
}
],
"source": [
"run('''SELECT aisle, COUNT(id), SUM(quantity)\n",
"FROM groceries GROUP BY aisle HAVING SUM(quantity) < 5''')"
]
},
{
"cell_type": "code",
"execution_count": 58,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" aisle COUNT(id) SUM(quantity)\n",
"------- ----------- ---------------\n",
" 2 2 8\n"
]
}
],
"source": [
"run('''SELECT aisle, COUNT(id), SUM(quantity)\n",
"FROM groceries GROUP BY aisle HAVING aisle = 2''')"
]
},
{
"cell_type": "code",
"execution_count": 59,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" id name quantity aisle\n",
"---- ------------------- ---------- -------\n",
" 1 Bananas 4 7\n",
" 2 Peanut Butter 1 4\n",
" 3 Dark chocolate bars 2 2\n",
" 4 Ice cream 1 12\n",
" 5 Cherries 6 2\n",
" 6 Chocolate syrup 1 4\n"
]
}
],
"source": [
"run('SELECT * FROM groceries')"
]
},
{
"cell_type": "code",
"execution_count": 60,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"run('ALTER TABLE groceries ADD COLUMN music TEXT')"
]
},
{
"cell_type": "code",
"execution_count": 63,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" id name quantity aisle music\n",
"---- ------------------- ---------- ------- ---------\n",
" 1 Bananas 4 7 Metallica\n",
" 2 Peanut Butter 1 4 Metallica\n",
" 3 Dark chocolate bars 2 2 Metallica\n",
" 4 Ice cream 1 12 Metallica\n",
" 5 Cherries 6 2 Metallica\n",
" 6 Chocolate syrup 1 4 Metallica\n"
]
}
],
"source": [
"run('SELECT * FROM groceries')"
]
},
{
"cell_type": "code",
"execution_count": 62,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": [
"run('UPDATE groceries SET music = \"Metallica\"')"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"collapsed": true
},
"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.4.6"
}
},
"nbformat": 4,
"nbformat_minor": 2
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment