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.
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
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
df.head()
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 |
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
! ls -g periodic_table.db
-rw-r--r--@ 1 staff 40960 Apr 12 20:49 periodic_table.db
df2.head()
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 |
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
df2.head()
elem_symbol | elem_mass | |
---|---|---|
0 | Al | 26.981539 |
1 | Ar | 39.948000 |
2 | B | 10.811000 |
3 | Be | 9.012182 |
4 | C | 12.010700 |