{
"cells": [
{
"cell_type": "code",
"execution_count": 1,
"metadata": {},
"outputs": [],
"source": [
"import pandas as pd\n",
"import os\n",
"import os.path\n",
"import json\n",
"import sys\n",
"import importlib\n",
"\n",
"module_dir = \"../../modules\"\n",
"module_path = os.path.abspath(module_dir)\n",
"if not module_path in sys.path:\n",
" sys.path.append(module_path)\n",
"\n",
"import dbutil\n",
"importlib.reload(dbutil)\n",
"\n",
"%load_ext sql"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Set User Credentials\n",
"\n",
"> **Edit `creds.json` to reflect your mysql user and password**\n",
"\n",
"> This must be done **prior** to executing the following cell "
]
},
{
"cell_type": "code",
"execution_count": 2,
"metadata": {},
"outputs": [],
"source": [
"dbsource = \"sqlite\"\n",
"db = \"book\"\n",
"cstring = dbutil.db_cstring(dbsource, \"creds.json\", \".\", db)"
]
},
{
"cell_type": "code",
"execution_count": 3,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Connection string: sqlite:///../../dbfiles/book.db\n"
]
}
],
"source": [
"print(\"Connection string:\", cstring)"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"### Establish Connection from Client to Server\n",
"\n",
"The cell below uses the Python variable constructed earlier to actually establish a connection with the server. \n",
"\n",
"If you cannot get a connection established, switch over to MySQL Workbench and try from that different client."
]
},
{
"cell_type": "code",
"execution_count": 4,
"metadata": {},
"outputs": [],
"source": [
"%sql $cstring"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"#### Make a Query"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Try and execute the following line, which uses an \"inline\" sql magic to exectute a query to the database and retrieve the result."
]
},
{
"cell_type": "code",
"execution_count": 5,
"metadata": {
"nbgrader": {
"grade": true,
"grade_id": "cell-bbdb09230afb60cf",
"locked": false,
"points": 0,
"schema_version": 3,
"solution": true,
"task": false
}
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" * sqlite:///../../dbfiles/book.db\n",
"Done.\n"
]
},
{
"data": {
"text/html": [
"
\n",
" \n",
" code | \n",
" pop | \n",
" gdp | \n",
" life | \n",
" cell | \n",
"
\n",
" \n",
" CHN | \n",
" 1386.4 | \n",
" 12143.5 | \n",
" 76.4 | \n",
" 1469.88 | \n",
"
\n",
" \n",
" FRA | \n",
" 66.87 | \n",
" 2586.29 | \n",
" 82.5 | \n",
" 69.02 | \n",
"
\n",
" \n",
" GBR | \n",
" 66.06 | \n",
" 2637.87 | \n",
" 81.2 | \n",
" 79.1 | \n",
"
\n",
" \n",
" IND | \n",
" 1338.66 | \n",
" 2652.55 | \n",
" 68.8 | \n",
" 1168.9 | \n",
"
\n",
" \n",
" USA | \n",
" 325.15 | \n",
" 19485.4 | \n",
" 78.5 | \n",
" 391.6 | \n",
"
\n",
"
"
],
"text/plain": [
"[('CHN', 1386.4, 12143.5, 76.4, 1469.88),\n",
" ('FRA', 66.87, 2586.29, 82.5, 69.02),\n",
" ('GBR', 66.06, 2637.87, 81.2, 79.1),\n",
" ('IND', 1338.66, 2652.55, 68.8, 1168.9),\n",
" ('USA', 325.15, 19485.4, 78.5, 391.6)]"
]
},
"execution_count": 5,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%sql SELECT * from indicators0"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": []
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": []
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": []
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": []
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": []
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": []
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": []
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": []
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": []
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": []
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": []
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": []
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": []
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Shift to a Different Database"
]
},
{
"cell_type": "code",
"execution_count": 6,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Connection string: sqlite:///../../dbfiles/school.db\n"
]
}
],
"source": [
"dbsource = \"sqlite\"\n",
"db = \"school\"\n",
"cstring = dbutil.db_cstring(dbsource, \"creds.json\", \".\", db)\n",
"print(\"Connection string:\", cstring)"
]
},
{
"cell_type": "code",
"execution_count": 7,
"metadata": {},
"outputs": [],
"source": [
"%sql $cstring"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": []
},
{
"cell_type": "code",
"execution_count": 8,
"metadata": {
"nbgrader": {
"grade": true,
"grade_id": "cell-bbdb09230afb60cf",
"locked": false,
"points": 0,
"schema_version": 3,
"solution": true,
"task": false
}
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" sqlite:///../../dbfiles/book.db\n",
" * sqlite:///../../dbfiles/school.db\n",
"Done.\n"
]
},
{
"data": {
"text/html": [
"\n",
" \n",
" departmentid | \n",
" departmentname | \n",
" departmentchair | \n",
" division | \n",
"
\n",
" \n",
" ANSO | \n",
" Anthropology and Sociology | \n",
" None | \n",
" Social Sciences | \n",
"
\n",
" \n",
" ART | \n",
" Art History and Visual Culture | \n",
" 9252 | \n",
" Fine Arts | \n",
"
\n",
" \n",
" BIOL | \n",
" Biology | \n",
" None | \n",
" Natural Sciences | \n",
"
\n",
" \n",
" BLST | \n",
" Black Studies | \n",
" 9133 | \n",
" Interdisciplinary | \n",
"
\n",
" \n",
" CHEM | \n",
" Chemistry and Biochemistry | \n",
" 9143 | \n",
" Natural Sciences | \n",
"
\n",
" \n",
" CINE | \n",
" Cinema | \n",
" 9042 | \n",
" Fine Arts | \n",
"
\n",
" \n",
" CLAS | \n",
" Classical Studies | \n",
" 9111 | \n",
" Humanities | \n",
"
\n",
" \n",
" COMM | \n",
" Communication | \n",
" 9144 | \n",
" Social Sciences | \n",
"
\n",
" \n",
" DANC | \n",
" Dance | \n",
" 9180 | \n",
" Fine Arts | \n",
"
\n",
" \n",
" EAST | \n",
" East Asian Studies | \n",
" 9257 | \n",
" Interdisciplinary | \n",
"
\n",
" \n",
" ECON | \n",
" Economics | \n",
" 9291 | \n",
" Social Sciences | \n",
"
\n",
" \n",
" EDUC | \n",
" Education | \n",
" None | \n",
" Social Sciences | \n",
"
\n",
" \n",
" ENGL | \n",
" English | \n",
" 9142 | \n",
" Humanities | \n",
"
\n",
" \n",
" ENVS | \n",
" Environmental Studies | \n",
" 9253 | \n",
" Interdisciplinary | \n",
"
\n",
" \n",
" FYS | \n",
" First Year Seminar | \n",
" None | \n",
" Interdisciplinary | \n",
"
\n",
" \n",
" GEOS | \n",
" Geosciences | \n",
" None | \n",
" Natural Sciences | \n",
"
\n",
" \n",
" HIST | \n",
" History | \n",
" 9248 | \n",
" Humanities | \n",
"
\n",
" \n",
" HNRS | \n",
" Honors | \n",
" None | \n",
" Interdisciplinary | \n",
"
\n",
" \n",
" INTD | \n",
" Interdepartmental | \n",
" None | \n",
" Interdisciplinary | \n",
"
\n",
" \n",
" INTL | \n",
" International Studies | \n",
" 9178 | \n",
" Interdisciplinary | \n",
"
\n",
" \n",
" LACS | \n",
" Latin Amer &Caribbean Studies | \n",
" None | \n",
" Interdisciplinary | \n",
"
\n",
" \n",
" LANG | \n",
" Modern Language | \n",
" 9153 | \n",
" Humanities | \n",
"
\n",
" \n",
" MATH | \n",
" Mathematics & Computer Science | \n",
" 9140 | \n",
" Natural Sciences | \n",
"
\n",
" \n",
" MTA | \n",
" Media Technology and Arts | \n",
" None | \n",
" Interdisciplinary | \n",
"
\n",
" \n",
" MUS | \n",
" Music | \n",
" 9116 | \n",
" Fine Arts | \n",
"
\n",
" \n",
" NEUR | \n",
" Neuroscience | \n",
" None | \n",
" Interdisciplinary | \n",
"
\n",
" \n",
" PHED | \n",
" Health, Exercise, & Sport Stud | \n",
" 9073 | \n",
" Social Sciences | \n",
"
\n",
" \n",
" PHIL | \n",
" Philosophy | \n",
" 9085 | \n",
" Humanities | \n",
"
\n",
" \n",
" PHYS | \n",
" Physics | \n",
" 9089 | \n",
" Natural Sciences | \n",
"
\n",
" \n",
" POSC | \n",
" Political Science | \n",
" 9064 | \n",
" Social Sciences | \n",
"
\n",
" \n",
" PPE | \n",
" Philosophy/PoliSci/Economics | \n",
" None | \n",
" Interdisciplinary | \n",
"
\n",
" \n",
" PSYC | \n",
" Psychology | \n",
" 9107 | \n",
" Natural Sciences | \n",
"
\n",
" \n",
" QS | \n",
" Queer Studies | \n",
" None | \n",
" Interdisciplinary | \n",
"
\n",
" \n",
" REL | \n",
" Religion | \n",
" 9285 | \n",
" Humanities | \n",
"
\n",
" \n",
" THTR | \n",
" Theatre | \n",
" 9204 | \n",
" Fine Arts | \n",
"
\n",
" \n",
" WGST | \n",
" Women's and Gender Studies | \n",
" None | \n",
" Interdisciplinary | \n",
"
\n",
"
"
],
"text/plain": [
"[('ANSO', 'Anthropology and Sociology', None, 'Social Sciences'),\n",
" ('ART', 'Art History and Visual Culture', 9252, 'Fine Arts'),\n",
" ('BIOL', 'Biology', None, 'Natural Sciences'),\n",
" ('BLST', 'Black Studies', 9133, 'Interdisciplinary'),\n",
" ('CHEM', 'Chemistry and Biochemistry', 9143, 'Natural Sciences'),\n",
" ('CINE', 'Cinema', 9042, 'Fine Arts'),\n",
" ('CLAS', 'Classical Studies', 9111, 'Humanities'),\n",
" ('COMM', 'Communication', 9144, 'Social Sciences'),\n",
" ('DANC', 'Dance', 9180, 'Fine Arts'),\n",
" ('EAST', 'East Asian Studies', 9257, 'Interdisciplinary'),\n",
" ('ECON', 'Economics', 9291, 'Social Sciences'),\n",
" ('EDUC', 'Education', None, 'Social Sciences'),\n",
" ('ENGL', 'English', 9142, 'Humanities'),\n",
" ('ENVS', 'Environmental Studies', 9253, 'Interdisciplinary'),\n",
" ('FYS', 'First Year Seminar', None, 'Interdisciplinary'),\n",
" ('GEOS', 'Geosciences', None, 'Natural Sciences'),\n",
" ('HIST', 'History', 9248, 'Humanities'),\n",
" ('HNRS', 'Honors', None, 'Interdisciplinary'),\n",
" ('INTD', 'Interdepartmental', None, 'Interdisciplinary'),\n",
" ('INTL', 'International Studies', 9178, 'Interdisciplinary'),\n",
" ('LACS', 'Latin Amer &Caribbean Studies', None, 'Interdisciplinary'),\n",
" ('LANG', 'Modern Language', 9153, 'Humanities'),\n",
" ('MATH', 'Mathematics & Computer Science', 9140, 'Natural Sciences'),\n",
" ('MTA', 'Media Technology and Arts', None, 'Interdisciplinary'),\n",
" ('MUS', 'Music', 9116, 'Fine Arts'),\n",
" ('NEUR', 'Neuroscience', None, 'Interdisciplinary'),\n",
" ('PHED', 'Health, Exercise, & Sport Stud', 9073, 'Social Sciences'),\n",
" ('PHIL', 'Philosophy', 9085, 'Humanities'),\n",
" ('PHYS', 'Physics', 9089, 'Natural Sciences'),\n",
" ('POSC', 'Political Science', 9064, 'Social Sciences'),\n",
" ('PPE', 'Philosophy/PoliSci/Economics', None, 'Interdisciplinary'),\n",
" ('PSYC', 'Psychology', 9107, 'Natural Sciences'),\n",
" ('QS', 'Queer Studies', None, 'Interdisciplinary'),\n",
" ('REL', 'Religion', 9285, 'Humanities'),\n",
" ('THTR', 'Theatre', 9204, 'Fine Arts'),\n",
" ('WGST', \"Women's and Gender Studies\", None, 'Interdisciplinary')]"
]
},
"execution_count": 8,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%sql SELECT * from departments"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": []
}
],
"metadata": {
"celltoolbar": "Create Assignment",
"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.9"
}
},
"nbformat": 4,
"nbformat_minor": 4
}