SQLite is a relational database that requires no server and can be set up in seconds. It stores data for SQL queries and local analysis. section 10.5
It makes data retrieval more self-service: instead of waiting for someone else to extract records, you can query tables directly with SQL. section 10.5
Useful SQL operations include:
- SELECT and WHERE retrieve selected columns and records. section 10.5
- GROUP BY, ORDER BY, and CASE organize and transform query results. section 10.5
- INNER JOIN, LEFT JOIN, subqueries, common table expressions, and windowing functions connect or extend analyses. section 10.5
A local data workaround
When a vendor connection repeatedly timed out, batches were downloaded to SQLite with Python. The script resumed from the last completed batch, eventually producing a local database that the team could use daily. section 10.13
import urllib.request
import sqlite3
import pandas as pd
db_url =
"https://github.com/thomasnield/training-data-repository/raw/refs/heads/main/storm_chasers.db"
urllib.request.urlretrieve(db_url, 'storm_chasers.db')
conn = sqlite3.connect('storm_chasers.db')
sql = "SELECT * FROM TORNADO_TRX"
df = pd.read_sql(sql, conn)
print(df)
Download a SQLite database, connect with Python, query a tornado-transaction table, and load the results into pandas. section 10.5
