{ "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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
namecontinent
0A P Hill Aaf (Fort A P Hill) AirportNA
1Aappilattoq (Kujalleq) HeliportNA
2Aappilattoq (Qaasuitsup) HeliportNA
3Aasiaat AirportNA
4Abaco I Walker C AirportNA
\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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
elem_protonselem_symbolelem_long_nameelem_masselem_seriesupdated_atupdated_by
017ClChlorine35.453000Halogen1469802789KTU
18OOxygen15.999400Other nonmetal1469802789KTU
25BBoron10.811000Metalloid1469802789KTU
315PPhosphorous30.973762Other nonmetal1469802789KTU
410NeNeon20.179700Noble gas1469802789KTU
\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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
elem_symbolelem_mass
0Al26.981539
1Ar39.948000
2B10.811000
3Be9.012182
4C12.010700
\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 }