practiceSQL


LAB 1: Practice SQL from inside a Jupyter Notebook

In the context of this Lab, you have various .db files, all SQLite.

For example, below we have an airports.db and use it to extract the names of airports in North America, in alphabetical order.

What databases do you have, and how much SQL do you know?

Here's your sandbox in which to find out.


In [1]:
import sqlite3 as sql
import pandas as pd

class UsingDB:
    def __init__(self, source):
        self.data_source = source
        
    def __enter__(self):
        print('Starting')
        self.conn = sql.connect(self.data_source)
        self.curs = self.conn.cursor()
        return self

    def __exit__(self, *exc):
        print('Finishing')
        self.conn.close()
        return True

In [2]:
with UsingDB("airports.db") as db:
    # conn visible because global
    df = pd.read_sql_query("SELECT name, continent from airports WHERE continent = 'NA' " 
                           "and name not NULL order by name", db.conn)


Starting
Finishing

In [3]:
df.head()


Out[3]:
name continent
0 A P Hill Aaf (Fort A P Hill) Airport NA
1 Aappilattoq (Kujalleq) Heliport NA
2 Aappilattoq (Qaasuitsup) Heliport NA
3 Aasiaat Airport NA
4 Abaco I Walker C Airport NA

In [10]:
with UsingDB("periodic_table.db") as db2:
    print(db2.data_source)
    df2 = pd.read_sql_query("SELECT * FROM elements", db2.conn)


Starting
periodic_table.db
Finishing

In [8]:
! ls -g periodic_table.db


-rw-r--r--@ 1 staff  40960 Apr 12 20:49 periodic_table.db

In [11]:
df2.head()


Out[11]:
elem_protons elem_symbol elem_long_name elem_mass elem_series updated_at updated_by
0 17 Cl Chlorine 35.453000 Halogen 1469802789 KTU
1 8 O Oxygen 15.999400 Other nonmetal 1469802789 KTU
2 5 B Boron 10.811000 Metalloid 1469802789 KTU
3 15 P Phosphorous 30.973762 Other nonmetal 1469802789 KTU
4 10 Ne Neon 20.179700 Noble gas 1469802789 KTU

In [12]:
with UsingDB("periodic_table.db") as db2:
    print(db2.data_source)
    df2 = pd.read_sql_query("SELECT elem_symbol, elem_mass FROM "
                            "elements ORDER BY elem_symbol", db2.conn)


Starting
periodic_table.db
Finishing

In [13]:
df2.head()


Out[13]:
elem_symbol elem_mass
0 Al 26.981539
1 Ar 39.948000
2 B 10.811000
3 Be 9.012182
4 C 12.010700

In [ ]: