Week 2 — Lesson 3: SQL from Python with sqlite3 — Week 2 — Python data retrieval and exploration

The NorthPeak data also lives in data/diagnostics.sqlite, a database in a single file with three tables: machines, readings and incidents. Many questions about it are shorter in SQL than in pandas, and a join between tables is a natural SQL operation. Python talks to this file with the built-in sqlite3 module, or with pd.read_sql_query to get a DataFrame back. This lesson covers the four SQL words you need and the code that runs them.

A database in a file

data/diagnostics.sqlite is a database. It is one file. It holds three tables: machines, readings, incidents. They contain the same data as the clean CSV files.

You talk to it in SQL. A SQL question is a query. Four words do most of the work.

SQL word	What it does	pandas twin
`SELECT ... FROM table`	Chooses columns from a table	`df[["a", "b"]]`
`WHERE condition`	Keeps some rows	`df[df["a"] > 80]`
`GROUP BY key`	One result per group, with `COUNT`, `AVG`, `SUM`	`df.groupby("key")`
`JOIN t2 ON t1.k = t2.k`	Glues two tables on a shared column	`df1.merge(df2, on="k")`

Two more words help: ORDER BY col DESC sorts, biggest first. LIMIT 5 keeps five rows.

On the NorthPeak data

Python has SQLite built in. No install. Open the file, run a query, read the result.

python

import sqlite3
import pandas as pd

con = sqlite3.connect("data/diagnostics.sqlite")
cur = con.cursor()
cur.execute("SELECT COUNT(*) FROM machines")
print(cur.fetchone())                                     # (40,)
cur.execute("SELECT machine_id, site FROM machines WHERE machine_type = 'chiller' LIMIT 3")
print(cur.fetchall())   # [('M031', 'Quebec City'), ('M032', 'Toronto'), ('M033', 'Montreal')]
print(pd.read_sql_query("SELECT site, COUNT(*) AS n FROM machines GROUP BY site", con))
con.close()

The cursor gives tuples. pd.read_sql_query gives a DataFrame. Prefer the second one. You get the pandas commands of Lesson 1 for free.

A JOIN mixes two tables. The mean temperature by site needs readings for the temperature and machines for the site:

sql

SELECT m.site, ROUND(AVG(r.temperature_c), 1) AS t
FROM readings r
JOIN machines m ON r.machine_id = m.machine_id
GROUP BY m.site

Result: Montreal 44.4, Quebec City 46.1, Toronto 48.9. Toronto is the warmest site.

A common mistake

Text in SQL uses single quotes: 'chiller'. Double quotes name a column. WHERE machine_type = "chiller" looks right but asks for a column called chiller. SQLite is forgiving and often returns nothing instead of an error. If a WHERE returns zero rows, check your quotes.