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)
In [3]:
df.head()
Out[3]:
In [10]:
with UsingDB("periodic_table.db") as db2:
print(db2.data_source)
df2 = pd.read_sql_query("SELECT * FROM elements", db2.conn)
In [8]:
! ls -g periodic_table.db
In [11]:
df2.head()
Out[11]:
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)
In [13]:
df2.head()
Out[13]:
In [ ]: