{
"cells": [
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# LAB 1: Practice SQL from inside a Jupyter Notebook\n",
"\n",
"In the context of this Lab, you have various .db files, all SQLite. \n",
"\n",
"For example, below we have an airports.db and use it to extract the names of airports in North America, in alphabetical order. \n",
"\n",
"What databases do you have, and how much SQL do you know? \n",
"\n",
"Here's your sandbox in which to find out."
]
},
{
"cell_type": "code",
"execution_count": 1,
"metadata": {},
"outputs": [],
"source": [
"import sqlite3 as sql\n",
"import pandas as pd\n",
"\n",
"class UsingDB:\n",
" def __init__(self, source):\n",
" self.data_source = source\n",
" \n",
" def __enter__(self):\n",
" print('Starting')\n",
" self.conn = sql.connect(self.data_source)\n",
" self.curs = self.conn.cursor()\n",
" return self\n",
"\n",
" def __exit__(self, *exc):\n",
" print('Finishing')\n",
" self.conn.close()\n",
" return True"
]
},
{
"cell_type": "code",
"execution_count": 2,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Starting\n",
"Finishing\n"
]
}
],
"source": [
"with UsingDB(\"airports.db\") as db:\n",
" # conn visible because global\n",
" df = pd.read_sql_query(\"SELECT name, continent from airports WHERE continent = 'NA' \" \n",
" \"and name not NULL order by name\", db.conn)"
]
},
{
"cell_type": "code",
"execution_count": 3,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"
\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" name | \n",
" continent | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" A P Hill Aaf (Fort A P Hill) Airport | \n",
" NA | \n",
"
\n",
" \n",
" 1 | \n",
" Aappilattoq (Kujalleq) Heliport | \n",
" NA | \n",
"
\n",
" \n",
" 2 | \n",
" Aappilattoq (Qaasuitsup) Heliport | \n",
" NA | \n",
"
\n",
" \n",
" 3 | \n",
" Aasiaat Airport | \n",
" NA | \n",
"
\n",
" \n",
" 4 | \n",
" Abaco I Walker C Airport | \n",
" NA | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" name continent\n",
"0 A P Hill Aaf (Fort A P Hill) Airport NA\n",
"1 Aappilattoq (Kujalleq) Heliport NA\n",
"2 Aappilattoq (Qaasuitsup) Heliport NA\n",
"3 Aasiaat Airport NA\n",
"4 Abaco I Walker C Airport NA"
]
},
"execution_count": 3,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df.head()"
]
},
{
"cell_type": "code",
"execution_count": 10,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Starting\n",
"periodic_table.db\n",
"Finishing\n"
]
}
],
"source": [
"with UsingDB(\"periodic_table.db\") as db2:\n",
" print(db2.data_source)\n",
" df2 = pd.read_sql_query(\"SELECT * FROM elements\", db2.conn)"
]
},
{
"cell_type": "code",
"execution_count": 8,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"-rw-r--r--@ 1 staff 40960 Apr 12 20:49 periodic_table.db\r\n"
]
}
],
"source": [
"! ls -g periodic_table.db"
]
},
{
"cell_type": "code",
"execution_count": 11,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" elem_protons | \n",
" elem_symbol | \n",
" elem_long_name | \n",
" elem_mass | \n",
" elem_series | \n",
" updated_at | \n",
" updated_by | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" 17 | \n",
" Cl | \n",
" Chlorine | \n",
" 35.453000 | \n",
" Halogen | \n",
" 1469802789 | \n",
" KTU | \n",
"
\n",
" \n",
" 1 | \n",
" 8 | \n",
" O | \n",
" Oxygen | \n",
" 15.999400 | \n",
" Other nonmetal | \n",
" 1469802789 | \n",
" KTU | \n",
"
\n",
" \n",
" 2 | \n",
" 5 | \n",
" B | \n",
" Boron | \n",
" 10.811000 | \n",
" Metalloid | \n",
" 1469802789 | \n",
" KTU | \n",
"
\n",
" \n",
" 3 | \n",
" 15 | \n",
" P | \n",
" Phosphorous | \n",
" 30.973762 | \n",
" Other nonmetal | \n",
" 1469802789 | \n",
" KTU | \n",
"
\n",
" \n",
" 4 | \n",
" 10 | \n",
" Ne | \n",
" Neon | \n",
" 20.179700 | \n",
" Noble gas | \n",
" 1469802789 | \n",
" KTU | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" elem_protons elem_symbol elem_long_name elem_mass elem_series \\\n",
"0 17 Cl Chlorine 35.453000 Halogen \n",
"1 8 O Oxygen 15.999400 Other nonmetal \n",
"2 5 B Boron 10.811000 Metalloid \n",
"3 15 P Phosphorous 30.973762 Other nonmetal \n",
"4 10 Ne Neon 20.179700 Noble gas \n",
"\n",
" updated_at updated_by \n",
"0 1469802789 KTU \n",
"1 1469802789 KTU \n",
"2 1469802789 KTU \n",
"3 1469802789 KTU \n",
"4 1469802789 KTU "
]
},
"execution_count": 11,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df2.head()"
]
},
{
"cell_type": "code",
"execution_count": 12,
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Starting\n",
"periodic_table.db\n",
"Finishing\n"
]
}
],
"source": [
"with UsingDB(\"periodic_table.db\") as db2:\n",
" print(db2.data_source)\n",
" df2 = pd.read_sql_query(\"SELECT elem_symbol, elem_mass FROM \"\n",
" \"elements ORDER BY elem_symbol\", db2.conn)"
]
},
{
"cell_type": "code",
"execution_count": 13,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" elem_symbol | \n",
" elem_mass | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" Al | \n",
" 26.981539 | \n",
"
\n",
" \n",
" 1 | \n",
" Ar | \n",
" 39.948000 | \n",
"
\n",
" \n",
" 2 | \n",
" B | \n",
" 10.811000 | \n",
"
\n",
" \n",
" 3 | \n",
" Be | \n",
" 9.012182 | \n",
"
\n",
" \n",
" 4 | \n",
" C | \n",
" 12.010700 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" elem_symbol elem_mass\n",
"0 Al 26.981539\n",
"1 Ar 39.948000\n",
"2 B 10.811000\n",
"3 Be 9.012182\n",
"4 C 12.010700"
]
},
"execution_count": 13,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df2.head()"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"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.6.4"
}
},
"nbformat": 4,
"nbformat_minor": 2
}