9 Loading Data with SQL

9.1 Learning Objectives

By the end of this chapter, you will be able to:

Check that SQLite is installed and create a new .db database file from the terminal.
Distinguish SQLite dot-commands from SQL statements, and use .mode, .headers, and .import correctly.
Write a CREATE TABLE schema and load a clean CSV into the table.
Connect to a SQLite database from Python using sqlite3.connect and read query results into a pandas DataFrame with pd.read_sql_query.
Write exploratory SQL using SELECT, FROM, WHERE, GROUP BY, ORDER BY, and LIMIT, and translate between SQL and pandas equivalents.

9.2 Why SQL

This chapter marks the beginning of a new tool — SQL and the SQLite database engine — applied to the same Load–Clean–Extend–Reshape workflow you already know from pandas. Many of the concepts are identical: you are still loading data from files, inspecting it for problems, checking types and counts, and verifying that nothing went wrong. What changes is the interface: instead of calling Python methods on a DataFrame, you write declarative queries that describe what you want, and the database engine figures out how to get it.

SQL (Structured Query Language) has been the standard language for working with databases since the 1970s. You will encounter it in virtually every data-related career. SQLite is a lightweight database engine that stores an entire database in a single file on disk — no server, no installation wizard, no network configuration. This makes it ideal for learning SQL and for working with datasets that fit on a single machine.

The workflow in this chapter moves through four stages: orienting to the terminal, creating a database and schema in the SQLite shell, connecting to the database from Python, and running diagnostic queries with pd.read_sql_query().

You already know the Load–Clean–Extend–Reshape rhythm from Part II. The goal of this part is translation, not starting over. Most of your intuitions about inspecting data, validating transformations, and documenting decisions carry over — only the syntax changes.

9.3 Terminal Orientation

Before touching SQLite, you need three terminal commands. If you completed Chapter 1, these are review.

Command (Mac/Linux)	Command (Windows)	Purpose
`pwd`	`cd` (no arguments)	Print the current directory
`ls`	`dir`	List files in the current directory
`cd folder`	`cd folder`	Move into a folder

9.3.1 Checking SQLite installation

sqlite3 --version

If you see a version number (e.g., 3.43.2), SQLite is installed and on your PATH. If you get command not found, see Chapter 1 for installation instructions.

9.4 The SQLite Shell

The SQLite shell (sqlite3) is a command-line interface for interacting with a database directly. Think of it as a text-based spreadsheet viewer that also lets you define tables and run queries.

9.4.1 Dot-commands vs. SQL statements

The SQLite shell has two distinct types of commands. Dot-commands start with a period, do not end with a semicolon, and control the shell itself (.tables, .schema, .import, .quit). SQL statements are standard SQL, always end with a semicolon, and operate on data (SELECT ...;, CREATE TABLE ...;).

A SQL statement without a semicolon will sit and wait — SQLite thinks you are still typing. Adding a semicolon after a dot-command will produce an error. Getting these conventions straight early saves considerable frustration.

9.4.2 Step 1: Create the database file

Running sqlite3 cannabis.db either opens an existing file or creates a new one:

sqlite3 cannabis.db ".tables"

The file is created on disk when you first write data to it.

9.4.3 Step 2: Prepare the CSV

SQL builds on Python

The SQL workflow in this chapter depends on the Python workflow from Chapter 5 and Chapter 6. SQLite’s .import command requires a clean, rectangular CSV — exactly one header row, no aggregate rows, no currency formatting, no merged cells. Real-world government files rarely meet these requirements out of the box. The standard approach is to use pandas to load and clean the raw file first, then export a normalized CSV for SQLite to consume. If you have not completed Chapter 5 and Chapter 6, do so before continuing here.

SQLite’s .import command expects a clean, rectangular CSV with exactly one header row and no aggregate rows. The standard approach is to clean the file in pandas first — applying the same operations from Chapter 6 — and export a clean CSV for SQLite to consume.

9.5 Public Interest Context: Enclosure by Interface

SQL databases are how institutions store and gate-keep their data. A schema is not a neutral technical artifact: it encodes what the institution has decided counts as a record, what columns are worth collecting, and what categories are allowed. The fact that the cannabis file must be cleaned in pandas before SQLite can import it is not accidental — government datasets are typically published through web portals designed for human download, one file at a time, formatted for spreadsheet users. The publishing workflow is optimized for a different kind of user than you.

Understanding schemas is understanding how organizations decide what to count. When an agency publishes data without a schema — or with a schema that does not match the published CSV — the friction is itself a form of enclosure. When you build your own schema (the CREATE TABLE above), you are naming what you consider the meaningful shape of the record. What’s in the schema you’d write if you designed it from scratch, and what’s missing from the one the agency actually publishes?

Connection to Chapter 4

This section connects to the concept of enclosure introduced in Chapter 4. SQL schemas are how institutions structure and gate access to data; understanding them is part of understanding how public data is enclosed in practice.

This chapter uses pandas, numpy, and Python’s built-in sqlite3 module.

If you get an ImportError

sqlite3 ships with Python’s standard library — if you get ModuleNotFoundError: No module named 'sqlite3', your Python install is unusual (this is very rare). For pandas or numpy, run conda install pandas numpy or pip install pandas numpy and restart the kernel. See Chapter 1.

import pandas as pd
import numpy as np

df_raw = pd.read_csv(
    'colorado_cannabis_sales_by_county.csv',
    header=2,
    skipfooter=8,
    engine='python',
    na_values=['NR']
)

# Rename to clean, SQL-friendly column names
df_raw.columns = ['month', 'year', 'county', 'medical_sales', 'retail_sales']

# Strip currency formatting and cast to nullable integer
for col in ['medical_sales', 'retail_sales']:
    df_raw[col] = (
        df_raw[col]
        .str.strip()
        .str.replace('$', '', regex=False)
        .str.replace(',', '', regex=False)
        .pipe(pd.to_numeric, errors='coerce')
        .astype('Int64')
    )

# Remove aggregate Total rows
df_raw = df_raw[df_raw['county'].str.strip() != 'Total']

# Export
df_raw.to_csv('cannabis_clean.csv', index=False)
print(f"Exported {len(df_raw):,} rows to cannabis_clean.csv")

SQL naming rules

Use lowercase letters, underscores, and no spaces or special characters for column names. The names in the CSV must exactly match the names in the CREATE TABLE schema you will define next. A mismatch causes the header row to be inserted as a data row — one of the most common SQLite loading errors.

9.5.1 Step 3: Define the schema

Before loading data, you declare the table structure — column names and their storage types:

CREATE TABLE IF NOT EXISTS cannabis_sales (
    month INTEGER,
    year INTEGER,
    county TEXT,
    medical_sales INTEGER,
    retail_sales INTEGER
);

SQLite has five storage classes: INTEGER, REAL, TEXT, BLOB, and NULL. For most work, you will use INTEGER, REAL, and TEXT.

9.5.2 Step 4: Load the CSV

The .import command reads a delimited file and inserts rows into a table. Always set .mode csv and .headers on before importing:

sqlite3 cannabis.db << 'EOF'
.mode csv
.headers on
.import cannabis_clean.csv cannabis_sales
EOF

The heredoc syntax

The << 'EOF' syntax sends multiple lines of input to SQLite through stdin. This is necessary because dot-commands cannot be passed as quoted string arguments on the command line. If you are on Windows, use the Python-side approach described in the next section instead.

9.5.3 Step 5: Post-load diagnostics

Run three checks immediately after loading:

# 1. Row count
sqlite3 cannabis.db "SELECT COUNT(*) AS row_count FROM cannabis_sales;"

# 2. First rows
sqlite3 -column -header cannabis.db "SELECT * FROM cannabis_sales LIMIT 5;"

# 3. Check for accidentally imported header row
sqlite3 cannabis.db "SELECT COUNT(*) AS header_rows FROM cannabis_sales WHERE county = 'county';"

If the header row count is greater than zero, delete it:

DELETE FROM cannabis_sales WHERE county = 'county';

9.6 Connecting from Python

The terminal workflow above is useful for setup and debugging, but your analysis work will happen in Jupyter notebooks using Python’s built-in sqlite3 module and pandas’ pd.read_sql_query().

import sqlite3

conn = sqlite3.connect('cannabis.db')
print("Connection established:", conn)

Warning

If the path is wrong, sqlite3.connect() will silently create a new empty file rather than raising an error. If your queries return “no such table,” check that you are pointing at the right file.

9.6.1 Low-level access with a cursor

cursor = conn.cursor()
cursor.execute("SELECT COUNT(*) FROM cannabis_sales;")
result = cursor.fetchone()
print("Row count:", result[0])

9.6.2 The preferred workflow: `pd.read_sql_query()`

This function sends a SQL query and returns a DataFrame — exactly like pd.read_csv(), but reading from a database instead of a file:

df = pd.read_sql_query("SELECT * FROM cannabis_sales LIMIT 10;", conn)
df

The result is a real pandas DataFrame. All the methods you learned in previous chapters — .shape, .dtypes, .describe(), .value_counts() — work exactly the same way.

9.7 SQL Syntax Fundamentals

Every query you write follows a standard clause structure. The clauses must appear in this order:

SELECT   county, year, retail_sales      -- 1. Which columns to return
FROM     cannabis_sales                  -- 2. Which table to read from
WHERE    year = 2023                     -- 3. Which rows to keep (filter)
GROUP BY county                          -- 4. How to collapse rows into groups
HAVING   SUM(retail_sales) > 1000000     -- 5. Filter on group-level results
ORDER BY retail_sales DESC               -- 6. How to sort the output
LIMIT    10;                             -- 7. Maximum rows to return

Not every clause is required. A minimal query needs only SELECT and FROM:

SELECT * FROM cannabis_sales;

9.7.1 Keywords and case

SQL is case-insensitive for keywords — SELECT, select, and Select all work. The convention is to capitalize SQL keywords and use lowercase for table names, column names, and aliases. This helps your eye parse the clause structure quickly.

9.7.2 Literals

When you write a value directly in a query, always use single quotes for text strings ('Boulder'), no quotes for numbers (2023), and NULL for missing values. Double quotes in SQLite are for identifiers (column names), not string values.

9.7.3 The NULL gotcha

NULL = NULL evaluates to NULL, not TRUE. You cannot use = to test for missing values. Always use IS NULL or IS NOT NULL.

9.8 Exploratory Queries

With the data loaded and a Python connection established, apply the post-load checklist using SQL:

# Shape equivalent
pd.read_sql_query("SELECT COUNT(*) AS n_rows FROM cannabis_sales;", conn)

# Head equivalent
pd.read_sql_query("SELECT * FROM cannabis_sales LIMIT 5;", conn)

# Value counts equivalent
pd.read_sql_query("""
    SELECT county, COUNT(*) AS n_months
    FROM cannabis_sales
    GROUP BY county
    ORDER BY n_months DESC
    LIMIT 10;
""", conn)

# Describe equivalent for a numeric column
pd.read_sql_query("""
    SELECT
        MIN(retail_sales) AS min_val,
        MAX(retail_sales) AS max_val,
        AVG(retail_sales) AS mean_val,
        COUNT(*) AS n_rows,
        SUM(CASE WHEN retail_sales IS NULL THEN 1 ELSE 0 END) AS n_nulls
    FROM cannabis_sales;
""", conn)

9.9 SQL ↔︎ pandas Equivalents

pandas	SQL
`df.shape[0]`	`SELECT COUNT(*) FROM table;`
`df.head()`	`SELECT * FROM table LIMIT 5;`
`df['col'].value_counts()`	`SELECT col, COUNT() FROM table GROUP BY col ORDER BY COUNT() DESC;`
`df.describe()`	`SELECT MIN(col), MAX(col), AVG(col), COUNT(col) FROM table;`
`df['col'].isna().sum()`	`SELECT COUNT(*) FROM table WHERE col IS NULL;`
`df[df['col'] == 'x']`	`SELECT * FROM table WHERE col = 'x';`
`df.sort_values('col')`	`SELECT * FROM table ORDER BY col;`

9.10 Common Issues in This Chapter

Error: near "<dot-command>": syntax error

What it means: You tried to run a dot-command ending with a semicolon, or you tried to run dot-commands inside a .sql file.

How to fix it: Dot-commands (like .tables, .mode csv, .import) are shell commands — no semicolon. SQL statements (like SELECT ...;) always end with a semicolon. They belong in different places.

Error: no such table: cannabis_sales

What it means: Your connection is pointing at the wrong database file (often a brand-new empty one created accidentally). sqlite3.connect('cannabis.db') silently creates an empty file if cannabis.db is not in the current directory.

How to fix it: Run print(os.getcwd()) in your notebook. Confirm cannabis.db is in that directory. Use an absolute path if necessary. Verify the file has tables with sqlite3 cannabis.db ".tables" from the terminal.

The header row got imported as data

What it means: You forgot .headers on before .import, so SQLite treated the first row as data.

How to fix it: Run DELETE FROM <table> WHERE <text_col> = '<header_name>'; to remove the stray row. Then rebuild the workflow with .headers on set before .import.

A numeric column contains NULL where you expected numbers

What it means: SQLite does not enforce storage classes strictly — a TEXT value in an INTEGER column is stored as text unless you pre-cleaned it. If you loaded a CSV with $1,234 still present, the column is a mix of text and numbers.

How to fix it: Clean the CSV in pandas before importing. .describe() in pandas and SELECT MIN(...), MAX(...), COUNT(...) in SQL will reveal the issue. See Chapter 6.

9.11 Key Takeaways

SQLite is a file-based database: your whole database is one .db file. No server, no setup wizard.
Always clean CSVs in pandas before .import. SQLite expects rectangular, single-header-row data.
Dot-commands control the shell; SQL statements operate on data. Know which is which.
pd.read_sql_query(sql, conn) is the bridge — it returns a pandas DataFrame, so every pandas skill you built in Part II still applies.
The SQL query structure (SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT) appears in a fixed order. Memorize the order; the clauses inside each are flexible.

9.12 Further Reading

📚 Going deeper on SQL and SQLite

SQLite documentation — sqlite.org/docs.html is the canonical reference. Start with “SQL syntax” and “Command-line shell.”
Python sqlite3 module — docs.python.org/3/library/sqlite3.html documents the Python driver.
pandas: read_sql_query — pandas.pydata.org/docs/reference/api/pandas.read_sql_query.html is the API reference.
SQLZoo — sqlzoo.net is a free, interactive SQL tutorial that runs in the browser. Highly recommended for drilling query syntax.
Mode’s SQL Tutorial — mode.com/sql-tutorial/ walks through SQL with realistic business datasets.

9.13 Exercises

Exercise 1: Create and load a second table

Clean the Colorado population data in pandas, export it as a CSV, define a schema for a population table, and load it into the same database. Run the three post-load diagnostic checks.

Solution

pop = pd.read_csv('sya-county.csv', skiprows=1, dtype={'countyfips': str})
pop = pop[pop['datatype'] == 'Estimate']
pop.to_csv('population_clean.csv', index=False)

CREATE TABLE IF NOT EXISTS population (
    countyfips TEXT,
    county TEXT,
    year INTEGER,
    age INTEGER,
    malepopulation INTEGER,
    femalepopulation INTEGER,
    totalpopulation INTEGER,
    datatype TEXT
);

Then import using .mode csv, .headers on, and .import population_clean.csv population.

Exercise 2: Diagnostic queries

Write SQL queries to answer: (a) How many distinct counties are in the cannabis_sales table? (b) What is the year range? (c) How many rows have NULL retail_sales? (d) Which county has the most months of data?

Solution

queries = [
    "SELECT COUNT(DISTINCT county) AS n_counties FROM cannabis_sales;",
    "SELECT MIN(year) AS min_year, MAX(year) AS max_year FROM cannabis_sales;",
    "SELECT COUNT(*) AS null_retail FROM cannabis_sales WHERE retail_sales IS NULL;",
    """SELECT county, COUNT(*) AS n_months FROM cannabis_sales
       GROUP BY county ORDER BY n_months DESC LIMIT 5;"""
]

for q in queries:
    print(pd.read_sql_query(q, conn))
    print()

# Loading Data with SQL {#sec-sql-loading} ## Learning Objectives By the end of this chapter, you will be able to: - Check that [SQLite](https://www.sqlite.org/cli.html) is installed and create a new `.db` database file from the terminal. - Distinguish SQLite dot-commands from SQL statements, and use `.mode`, `.headers`, and `.import` correctly. - Write a `CREATE TABLE` schema and load a clean CSV into the table. - Connect to a SQLite database from Python using [`sqlite3.connect`](https://docs.python.org/3/library/sqlite3.html#sqlite3.connect) and read query results into a pandas DataFrame with [`pd.read_sql_query`](https://pandas.pydata.org/docs/reference/api/pandas.read_sql_query.html). - Write exploratory SQL using `SELECT`, `FROM`, `WHERE`, `GROUP BY`, `ORDER BY`, and `LIMIT`, and translate between SQL and pandas equivalents. ## Why SQL This chapter marks the beginning of a new tool — [SQL](https://www.sqlite.org/lang.html) and the [SQLite](https://www.sqlite.org/docs.html) database engine — applied to the same Load–Clean–Extend–Reshape workflow you already know from pandas. Many of the concepts are identical: you are still loading data from files, inspecting it for problems, checking types and counts, and verifying that nothing went wrong. What changes is the interface: instead of calling Python methods on a DataFrame, you write declarative queries that describe *what* you want, and the database engine figures out *how* to get it. SQL (Structured Query Language) has been the standard language for working with databases since the 1970s. You will encounter it in virtually every data-related career. [SQLite](https://www.sqlite.org/docs.html) is a lightweight database engine that stores an entire database in a single file on disk — no server, no installation wizard, no network configuration. This makes it ideal for learning SQL and for working with datasets that fit on a single machine. The workflow in this chapter moves through four stages: orienting to the terminal, creating a database and schema in the SQLite shell, connecting to the database from Python, and running diagnostic queries with [`pd.read_sql_query()`](https://pandas.pydata.org/docs/reference/api/pandas.read_sql_query.html). You already know the Load–Clean–Extend–Reshape rhythm from Part II. The goal of this part is translation, not starting over. Most of your intuitions about inspecting data, validating transformations, and documenting decisions carry over — only the syntax changes. ## Terminal Orientation Before touching SQLite, you need three terminal commands. If you completed @sec-setup, these are review. | Command (Mac/Linux) | Command (Windows) | Purpose | |---|---|---| | `pwd` | `cd` (no arguments) | Print the current directory | | `ls` | `dir` | List files in the current directory | | `cd folder` | `cd folder` | Move into a folder | ### Checking SQLite installation ```bash sqlite3 --version ``` If you see a version number (e.g., `3.43.2`), SQLite is installed and on your PATH. If you get `command not found`, see @sec-setup for installation instructions. ## The SQLite Shell The SQLite shell (`sqlite3`) is a command-line interface for interacting with a database directly. Think of it as a text-based spreadsheet viewer that also lets you define tables and run queries. ### Dot-commands vs. SQL statements The SQLite shell has two distinct types of commands. **Dot-commands** start with a period, do not end with a semicolon, and control the shell itself (`.tables`, `.schema`, `.import`, `.quit`). **SQL statements** are standard SQL, always end with a semicolon, and operate on data (`SELECT ...;`, `CREATE TABLE ...;`). A SQL statement without a semicolon will sit and wait — SQLite thinks you are still typing. Adding a semicolon after a dot-command will produce an error. Getting these conventions straight early saves considerable frustration. ### Step 1: Create the database file Running `sqlite3 cannabis.db` either opens an existing file or creates a new one: ```bash sqlite3 cannabis.db ".tables" ``` The file is created on disk when you first write data to it. ### Step 2: Prepare the CSV ::: {.callout-important} ## SQL builds on Python The SQL workflow in this chapter depends on the Python workflow from @sec-py-loading and @sec-py-cleaning. SQLite's `.import` command requires a clean, rectangular CSV — exactly one header row, no aggregate rows, no currency formatting, no merged cells. Real-world government files rarely meet these requirements out of the box. The standard approach is to use pandas to load and clean the raw file first, then export a normalized CSV for SQLite to consume. If you have not completed @sec-py-loading and @sec-py-cleaning, do so before continuing here. ::: SQLite's `.import` command expects a clean, rectangular CSV with exactly one header row and no aggregate rows. The standard approach is to clean the file in pandas first — applying the same operations from @sec-py-cleaning — and export a clean CSV for SQLite to consume. ## Public Interest Context: Enclosure by Interface SQL databases are how institutions store and gate-keep their data. A schema is not a neutral technical artifact: it encodes what the institution has decided counts as a record, what columns are worth collecting, and what categories are allowed. The fact that the cannabis file must be cleaned in pandas before SQLite can import it is not accidental — government datasets are typically published through web portals designed for human download, one file at a time, formatted for spreadsheet users. The publishing workflow is optimized for a different kind of user than you. Understanding schemas is understanding how organizations decide what to count. When an agency publishes data without a schema — or with a schema that does not match the published CSV — the friction is itself a form of enclosure. When you build your own schema (the `CREATE TABLE` above), you are naming what you consider the meaningful shape of the record. What's in the schema you'd write if you designed it from scratch, and what's missing from the one the agency actually publishes? ::: {.callout-note} ## Connection to @sec-public-interest This section connects to the concept of **enclosure** introduced in [Chapter 4](04-public-interest.qmd). SQL schemas are how institutions structure and gate access to data; understanding them is part of understanding how public data is enclosed in practice. ::: This chapter uses [pandas](https://pandas.pydata.org/docs/), [numpy](https://numpy.org/doc/stable/), and Python's built-in [`sqlite3`](https://docs.python.org/3/library/sqlite3.html) module. ::: {.callout-warning collapse="true"} ## If you get an `ImportError` `sqlite3` ships with Python's standard library — if you get `ModuleNotFoundError: No module named 'sqlite3'`, your Python install is unusual (this is very rare). For pandas or numpy, run `conda install pandas numpy` or `pip install pandas numpy` and restart the kernel. See @sec-setup. ::: ```{python} import pandas as pd import numpy as np df_raw = pd.read_csv( 'colorado_cannabis_sales_by_county.csv', header=2, skipfooter=8, engine='python', na_values=['NR'] ) # Rename to clean, SQL-friendly column names df_raw.columns = ['month', 'year', 'county', 'medical_sales', 'retail_sales'] # Strip currency formatting and cast to nullable integer for col in ['medical_sales', 'retail_sales']: df_raw[col] = ( df_raw[col] .str.strip() .str.replace('$', '', regex=False) .str.replace(',', '', regex=False) .pipe(pd.to_numeric, errors='coerce') .astype('Int64') ) # Remove aggregate Total rows df_raw = df_raw[df_raw['county'].str.strip() != 'Total'] # Export df_raw.to_csv('cannabis_clean.csv', index=False) print(f"Exported {len(df_raw):,} rows to cannabis_clean.csv") ``` ::: {.callout-tip} ## SQL naming rules Use lowercase letters, underscores, and no spaces or special characters for column names. The names in the CSV must exactly match the names in the `CREATE TABLE` schema you will define next. A mismatch causes the header row to be inserted as a data row — one of the most common SQLite loading errors. ::: ### Step 3: Define the schema Before loading data, you declare the table structure — column names and their storage types: ```sql CREATE TABLE IF NOT EXISTS cannabis_sales ( month INTEGER, year INTEGER, county TEXT, medical_sales INTEGER, retail_sales INTEGER ); ``` SQLite has five storage classes: `INTEGER`, `REAL`, `TEXT`, `BLOB`, and `NULL`. For most work, you will use `INTEGER`, `REAL`, and `TEXT`. ### Step 4: Load the CSV The `.import` command reads a delimited file and inserts rows into a table. Always set `.mode csv` and `.headers on` before importing: ```bash sqlite3 cannabis.db << 'EOF' .mode csv .headers on .import cannabis_clean.csv cannabis_sales EOF ``` ::: {.callout-warning} ## The heredoc syntax The `<< 'EOF'` syntax sends multiple lines of input to SQLite through stdin. This is necessary because dot-commands cannot be passed as quoted string arguments on the command line. If you are on Windows, use the Python-side approach described in the next section instead. ::: ### Step 5: Post-load diagnostics Run three checks immediately after loading: ```bash # 1. Row count sqlite3 cannabis.db "SELECT COUNT(*) AS row_count FROM cannabis_sales;" # 2. First rows sqlite3 -column -header cannabis.db "SELECT * FROM cannabis_sales LIMIT 5;" # 3. Check for accidentally imported header row sqlite3 cannabis.db "SELECT COUNT(*) AS header_rows FROM cannabis_sales WHERE county = 'county';" ``` If the header row count is greater than zero, delete it: ```sql DELETE FROM cannabis_sales WHERE county = 'county'; ``` ## Connecting from Python The terminal workflow above is useful for setup and debugging, but your analysis work will happen in Jupyter notebooks using Python's built-in `sqlite3` module and pandas' `pd.read_sql_query()`. ```{python} import sqlite3 conn = sqlite3.connect('cannabis.db') print("Connection established:", conn) ``` ::: {.callout-warning} If the path is wrong, `sqlite3.connect()` will silently create a **new empty file** rather than raising an error. If your queries return "no such table," check that you are pointing at the right file. ::: ### Low-level access with a cursor ```{python} cursor = conn.cursor() cursor.execute("SELECT COUNT(*) FROM cannabis_sales;") result = cursor.fetchone() print("Row count:", result[0]) ``` ### The preferred workflow: `pd.read_sql_query()` This function sends a SQL query and returns a DataFrame — exactly like `pd.read_csv()`, but reading from a database instead of a file: ```{python} df = pd.read_sql_query("SELECT * FROM cannabis_sales LIMIT 10;", conn) df ``` The result is a real pandas DataFrame. All the methods you learned in previous chapters — `.shape`, `.dtypes`, `.describe()`, `.value_counts()` — work exactly the same way. ## SQL Syntax Fundamentals Every query you write follows a standard clause structure. The clauses must appear in this order: ```sql SELECT county, year, retail_sales -- 1. Which columns to return FROM cannabis_sales -- 2. Which table to read from WHERE year = 2023 -- 3. Which rows to keep (filter) GROUP BY county -- 4. How to collapse rows into groups HAVING SUM(retail_sales) > 1000000 -- 5. Filter on group-level results ORDER BY retail_sales DESC -- 6. How to sort the output LIMIT 10; -- 7. Maximum rows to return ``` Not every clause is required. A minimal query needs only `SELECT` and `FROM`: ```sql SELECT * FROM cannabis_sales; ``` ### Keywords and case SQL is case-insensitive for keywords — `SELECT`, `select`, and `Select` all work. The convention is to capitalize SQL keywords and use lowercase for table names, column names, and aliases. This helps your eye parse the clause structure quickly. ### Literals When you write a value directly in a query, always use single quotes for text strings (`'Boulder'`), no quotes for numbers (`2023`), and `NULL` for missing values. Double quotes in SQLite are for identifiers (column names), not string values. ### The NULL gotcha `NULL = NULL` evaluates to `NULL`, not `TRUE`. You cannot use `=` to test for missing values. Always use `IS NULL` or `IS NOT NULL`. ## Exploratory Queries With the data loaded and a Python connection established, apply the post-load checklist using SQL: ```{python} # Shape equivalent pd.read_sql_query("SELECT COUNT(*) AS n_rows FROM cannabis_sales;", conn) ``` ```{python} # Head equivalent pd.read_sql_query("SELECT * FROM cannabis_sales LIMIT 5;", conn) ``` ```{python} # Value counts equivalent pd.read_sql_query(""" SELECT county, COUNT(*) AS n_months FROM cannabis_sales GROUP BY county ORDER BY n_months DESC LIMIT 10; """, conn) ``` ```{python} # Describe equivalent for a numeric column pd.read_sql_query(""" SELECT MIN(retail_sales) AS min_val, MAX(retail_sales) AS max_val, AVG(retail_sales) AS mean_val, COUNT(*) AS n_rows, SUM(CASE WHEN retail_sales IS NULL THEN 1 ELSE 0 END) AS n_nulls FROM cannabis_sales; """, conn) ``` ## SQL ↔ pandas Equivalents | pandas | SQL | |--------|-----| | `df.shape[0]` | `SELECT COUNT(*) FROM table;` | | `df.head()` | `SELECT * FROM table LIMIT 5;` | | `df['col'].value_counts()` | `SELECT col, COUNT(*) FROM table GROUP BY col ORDER BY COUNT(*) DESC;` | | `df.describe()` | `SELECT MIN(col), MAX(col), AVG(col), COUNT(col) FROM table;` | | `df['col'].isna().sum()` | `SELECT COUNT(*) FROM table WHERE col IS NULL;` | | `df[df['col'] == 'x']` | `SELECT * FROM table WHERE col = 'x';` | | `df.sort_values('col')` | `SELECT * FROM table ORDER BY col;` | ## Common Issues in This Chapter ::: {.callout-warning} ## `Error: near "<dot-command>": syntax error` **What it means:** You tried to run a dot-command ending with a semicolon, or you tried to run dot-commands inside a `.sql` file. **How to fix it:** Dot-commands (like `.tables`, `.mode csv`, `.import`) are shell commands — no semicolon. SQL statements (like `SELECT ...;`) always end with a semicolon. They belong in different places. ::: ::: {.callout-warning} ## `Error: no such table: cannabis_sales` **What it means:** Your connection is pointing at the wrong database file (often a brand-new empty one created accidentally). `sqlite3.connect('cannabis.db')` silently creates an empty file if `cannabis.db` is not in the current directory. **How to fix it:** Run `print(os.getcwd())` in your notebook. Confirm `cannabis.db` is in that directory. Use an absolute path if necessary. Verify the file has tables with `sqlite3 cannabis.db ".tables"` from the terminal. ::: ::: {.callout-warning} ## The header row got imported as data **What it means:** You forgot `.headers on` before `.import`, so SQLite treated the first row as data. **How to fix it:** Run `DELETE FROM <table> WHERE <text_col> = '<header_name>';` to remove the stray row. Then rebuild the workflow with `.headers on` set before `.import`. ::: ::: {.callout-warning} ## A numeric column contains `NULL` where you expected numbers **What it means:** SQLite does not enforce storage classes strictly — a `TEXT` value in an `INTEGER` column is stored as text unless you pre-cleaned it. If you loaded a CSV with `$1,234` still present, the column is a mix of text and numbers. **How to fix it:** Clean the CSV in pandas before importing. `.describe()` in pandas and `SELECT MIN(...), MAX(...), COUNT(...)` in SQL will reveal the issue. See @sec-py-cleaning. ::: ## Key Takeaways - SQLite is a file-based database: your whole database is one `.db` file. No server, no setup wizard. - Always clean CSVs in pandas before `.import`. SQLite expects rectangular, single-header-row data. - Dot-commands control the shell; SQL statements operate on data. Know which is which. - `pd.read_sql_query(sql, conn)` is the bridge — it returns a pandas `DataFrame`, so every pandas skill you built in Part II still applies. - The SQL query structure (`SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT`) appears in a fixed order. Memorize the order; the clauses inside each are flexible. ## Further Reading ::: {.callout-tip collapse="true"} ## 📚 Going deeper on SQL and SQLite - **SQLite documentation** — [sqlite.org/docs.html](https://www.sqlite.org/docs.html) is the canonical reference. Start with "SQL syntax" and "Command-line shell." - **Python `sqlite3` module** — [docs.python.org/3/library/sqlite3.html](https://docs.python.org/3/library/sqlite3.html) documents the Python driver. - **pandas: `read_sql_query`** — [pandas.pydata.org/docs/reference/api/pandas.read_sql_query.html](https://pandas.pydata.org/docs/reference/api/pandas.read_sql_query.html) is the API reference. - **SQLZoo** — [sqlzoo.net](https://sqlzoo.net/) is a free, interactive SQL tutorial that runs in the browser. Highly recommended for drilling query syntax. - **Mode's SQL Tutorial** — [mode.com/sql-tutorial/](https://mode.com/sql-tutorial/) walks through SQL with realistic business datasets. ::: ## Exercises ::: {.callout-note collapse="true"} ## Exercise 1: Create and load a second table Clean the Colorado population data in pandas, export it as a CSV, define a schema for a `population` table, and load it into the same database. Run the three post-load diagnostic checks. ::: {.callout-note collapse="true"} ## Solution ```{python} pop = pd.read_csv('sya-county.csv', skiprows=1, dtype={'countyfips': str}) pop = pop[pop['datatype'] == 'Estimate'] pop.to_csv('population_clean.csv', index=False) ``` ```sql CREATE TABLE IF NOT EXISTS population ( countyfips TEXT, county TEXT, year INTEGER, age INTEGER, malepopulation INTEGER, femalepopulation INTEGER, totalpopulation INTEGER, datatype TEXT ); ``` Then import using `.mode csv`, `.headers on`, and `.import population_clean.csv population`. ::: ::: ::: {.callout-note collapse="true"} ## Exercise 2: Diagnostic queries Write SQL queries to answer: (a) How many distinct counties are in the cannabis_sales table? (b) What is the year range? (c) How many rows have NULL retail_sales? (d) Which county has the most months of data? ::: {.callout-note collapse="true"} ## Solution ```{python} queries = [ "SELECT COUNT(DISTINCT county) AS n_counties FROM cannabis_sales;", "SELECT MIN(year) AS min_year, MAX(year) AS max_year FROM cannabis_sales;", "SELECT COUNT(*) AS null_retail FROM cannabis_sales WHERE retail_sales IS NULL;", """SELECT county, COUNT(*) AS n_months FROM cannabis_sales GROUP BY county ORDER BY n_months DESC LIMIT 5;""" ] for q in queries: print(pd.read_sql_query(q, conn)) print() ``` ::: :::

9.1 Learning Objectives

9.2 Why SQL

9.3 Terminal Orientation

9.3.1 Checking SQLite installation

9.4 The SQLite Shell

9.4.1 Dot-commands vs. SQL statements

9.4.2 Step 1: Create the database file

9.4.3 Step 2: Prepare the CSV

9.5 Public Interest Context: Enclosure by Interface

9.5.1 Step 3: Define the schema

9.5.2 Step 4: Load the CSV

9.5.3 Step 5: Post-load diagnostics

9.6 Connecting from Python

9.6.1 Low-level access with a cursor

9.6.2 The preferred workflow: pd.read_sql_query()

9.7 SQL Syntax Fundamentals

9.7.1 Keywords and case

9.7.2 Literals

9.7.3 The NULL gotcha

9.8 Exploratory Queries

9.9 SQL ↔︎ pandas Equivalents

9.10 Common Issues in This Chapter

9.11 Key Takeaways

9.12 Further Reading

9.13 Exercises

9.6.2 The preferred workflow: `pd.read_sql_query()`