Lecture 20 – Data 100, Fall 2024¶

Data 100, Fall 2024

Acknowledgments Page

Starting Up SQL¶

Before we look at SQL syntax in detail, let's first get ourselves set up to run SQL queries in Jupyter.

In [ ]:
pip install jupysql --upgrade

Approach #1: SQL Magic¶

1. Load the sql Module.

Load %%sql cell magic.

In [1]:
%load_ext sql

There's a new jupysql version available (0.10.14), you're running 0.8.0. To upgrade: pip install jupysql --upgrade
Deploy FastAPI apps for free on Ploomber Cloud! Learn more: https://ploomber.io/s/signup

2. Connect to a database.

Here, we connect to the SQLite database basic_examples.db and DuckDB database example_duck.db.

In [2]:
%sql sqlite:///data/basic_examples.db --alias sqlite
In [3]:
%sql duckdb:///data/example_duck.db --alias duckdb

If you were connecting to an "enterprise data platform"

from sqlalchemy import create_engine

snow_engine = create_engine(
    f"snowflake://{user}:{password}@{account_identifier}")
%sql snow_engine --alias snow

db_engine = create_engine(
  url = f"databricks://token:{access_token}@{server_hostname}?" +
        f"http_path={http_path}&catalog={catalog}&schema={schema}"
)
%sql db_engine --alias db


3. Run a simple SQL query.

Note the %%sql lets Jupyter parse the rest of the lines as a SQL command.

In [4]:
%%sql
SELECT * FROM Dragon;
Running query in 'duckdb'
Out[4]:
name year cute
hiccup 2010 10
drogon 2011 -100
dragon 2 2019 0
puff 2010 100
smaug 2011 None
ResultSet : to convert to pandas, call .DataFrame() or to polars, call .PolarsDataFrame()

Simple query, this time on two different lines.

In [5]:
%%sql
SELECT *
FROM Dragon;
Running query in 'duckdb'
Out[5]:
name year cute
hiccup 2010 10
drogon 2011 -100
dragon 2 2019 0
puff 2010 100
smaug 2011 None
ResultSet : to convert to pandas, call .DataFrame() or to polars, call .PolarsDataFrame()

Approach #3: pd.read_sql¶

It turns out that pandas has a special-purpose function to parse SQL queries. We can pass in a SQL query as a string to return a pandas DataFrame. To achieve the same result as we did using cell magic above, we can do the following.

1. Connect to a database

In [6]:
import sqlalchemy 
import pandas as pd

engine = sqlalchemy.create_engine("duckdb:///data/example_duck.db")

2. Run a simple SQL query

In [7]:
query = """
SELECT * 
FROM Dragon;
"""

df = pd.read_sql(query, engine)
df
Out[7]:
name year cute
0 hiccup 2010 10.0
1 drogon 2011 -100.0
2 dragon 2 2019 0.0
3 puff 2010 100.0
4 smaug 2011 NaN

Approach "3" -- Duck DB Special¶

Now that we are using DuckDB we can do something extra crazy:

In [8]:
import seaborn as sns
mpg = sns.load_dataset("mpg")
In [9]:
%%sql
SELECT * FROM mpg
Running query in 'duckdb'
Out[9]:
mpg cylinders displacement horsepower weight acceleration model_year origin name
18.0 8 307.0 130.0 3504 12.0 70 usa chevrolet chevelle malibu
15.0 8 350.0 165.0 3693 11.5 70 usa buick skylark 320
18.0 8 318.0 150.0 3436 11.0 70 usa plymouth satellite
16.0 8 304.0 150.0 3433 12.0 70 usa amc rebel sst
17.0 8 302.0 140.0 3449 10.5 70 usa ford torino
15.0 8 429.0 198.0 4341 10.0 70 usa ford galaxie 500
14.0 8 454.0 220.0 4354 9.0 70 usa chevrolet impala
14.0 8 440.0 215.0 4312 8.5 70 usa plymouth fury iii
14.0 8 455.0 225.0 4425 10.0 70 usa pontiac catalina
15.0 8 390.0 190.0 3850 8.5 70 usa amc ambassador dpl
ResultSet : to convert to pandas, call .DataFrame() or to polars, call .PolarsDataFrame()
Truncated to displaylimit of 10
If you want to see more, please visit displaylimit configuration

That is right!! DuckDB can also see my dataframes in the python environment allowing me to do dataframe manipulation in SQL!

Tables and Schema¶

A database contains a collection of SQL tables. Let's connect to our "toy" database example_duck.db and explore the tables it stores.

In [10]:
%%sql
SELECT * FROM information_schema.tables
Running query in 'duckdb'
Out[10]:
table_catalog table_schema table_name table_type self_referencing_column_name reference_generation user_defined_type_catalog user_defined_type_schema user_defined_type_name is_insertable_into is_typed commit_action TABLE_COMMENT
example_duck main dish BASE TABLE None None None None None YES NO None None
example_duck main dragon BASE TABLE None None None None None YES NO None None
example_duck main scene BASE TABLE None None None None None YES NO None None
ResultSet : to convert to pandas, call .DataFrame() or to polars, call .PolarsDataFrame()
In [11]:
%%sql
SELECT * FROM information_schema.columns
Running query in 'duckdb'
Out[11]:
table_catalog table_schema table_name column_name ordinal_position column_default is_nullable data_type character_maximum_length character_octet_length numeric_precision numeric_precision_radix numeric_scale datetime_precision interval_type interval_precision character_set_catalog character_set_schema character_set_name collation_catalog collation_schema collation_name domain_catalog domain_schema domain_name udt_catalog udt_schema udt_name scope_catalog scope_schema scope_name maximum_cardinality dtd_identifier is_self_referencing is_identity identity_generation identity_start identity_increment identity_maximum identity_minimum identity_cycle is_generated generation_expression is_updatable COLUMN_COMMENT
example_duck main dish name 1 None NO VARCHAR None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None
example_duck main dish type 2 None YES VARCHAR None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None
example_duck main dish cost 3 None YES INTEGER None None 32 2 0 None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None
example_duck main dragon name 1 None NO VARCHAR None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None
example_duck main dragon year 2 None YES INTEGER None None 32 2 0 None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None
example_duck main dragon cute 3 None YES INTEGER None None 32 2 0 None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None
example_duck main scene id 1 None NO INTEGER None None 32 2 0 None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None
example_duck main scene biome 2 None NO VARCHAR None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None
example_duck main scene city 3 None NO VARCHAR None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None
example_duck main scene visitors 4 None YES INTEGER None None 32 2 0 None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None None
ResultSet : to convert to pandas, call .DataFrame() or to polars, call .PolarsDataFrame()
Truncated to displaylimit of 10
If you want to see more, please visit displaylimit configuration

Getting Schema information with SQLAlchemy¶

The way you list the tables varies across database platforms. For example, the statement:

SELECT * FROM information_schema.columns

It only works on Postgres-compatible databases.

For example, if we wanted to get the schema for tables in DuckDB, we would need the following:

In [12]:
pd.options.display.max_colwidth = None
pd.options.display.max_rows = None
pd.read_sql("SELECT * FROM sqlite_schema", "duckdb:///data/example_duck.db")
Out[12]:
type name tbl_name rootpage sql
0 table dish dish 0 CREATE TABLE dish("name" VARCHAR PRIMARY KEY, "type" VARCHAR, "cost" INTEGER, CHECK(("cost" >= 0)));
1 table dragon dragon 0 CREATE TABLE dragon("name" VARCHAR PRIMARY KEY, "year" INTEGER, cute INTEGER, CHECK(("year" >= 2000)));
2 table scene scene 0 CREATE TABLE scene(id INTEGER PRIMARY KEY, biome VARCHAR NOT NULL, city VARCHAR NOT NULL, visitors INTEGER, created_at TIMESTAMP DEFAULT(current_date()), CHECK((visitors >= 0)));

Fortunately, SQLAlchemy has some generic tools that will be helpful regardless of what database platform you use.

In [13]:
from sqlalchemy import inspect
inspector = inspect(engine)
inspector.get_table_names()
Out[13]:
['dish', 'dragon', 'scene']
In [14]:
inspector.get_columns('scene')
Out[14]:
[{'name': 'id',
  'type': INTEGER(),
  'nullable': False,
  'default': None,
  'autoincrement': False,
  'comment': None},
 {'name': 'biome',
  'type': VARCHAR(),
  'nullable': False,
  'default': None,
  'autoincrement': False,
  'comment': None},
 {'name': 'city',
  'type': VARCHAR(),
  'nullable': False,
  'default': None,
  'autoincrement': False,
  'comment': None},
 {'name': 'visitors',
  'type': INTEGER(),
  'nullable': True,
  'default': None,
  'autoincrement': False,
  'comment': None},
 {'name': 'created_at',
  'type': TIMESTAMP(),
  'nullable': True,
  'default': 'current_date()',
  'autoincrement': False,
  'comment': None}]

Same with SQLite.

In [15]:
sqlite_engine = sqlalchemy.create_engine("sqlite:///data/basic_examples.db")
inspect(sqlite_engine).get_columns("scene")
Out[15]:
[{'name': 'id',
  'type': INTEGER(),
  'nullable': True,
  'default': None,
  'primary_key': 1},
 {'name': 'biome',
  'type': TEXT(),
  'nullable': False,
  'default': None,
  'primary_key': 0},
 {'name': 'city',
  'type': TEXT(),
  'nullable': False,
  'default': None,
  'primary_key': 0},
 {'name': 'visitors',
  'type': INTEGER(),
  'nullable': True,
  'default': None,
  'primary_key': 0},
 {'name': 'created_at',
  'type': DATETIME(),
  'nullable': True,
  'default': "DATETIME('now')",
  'primary_key': 0}]

Example of table creation with interesting constraints¶

More advanced example of creating tables with primary and foreign key constraints:

In [16]:
%%sql

DROP TABLE IF EXISTS grade;
DROP TABLE IF EXISTS assignment;
DROP TABLE IF EXISTS student;


CREATE TABLE student (
    student_id INTEGER PRIMARY KEY,
    name VARCHAR,
    email VARCHAR
);

CREATE TABLE assignment (
    assignment_id INTEGER PRIMARY KEY,
    description VARCHAR
);

CREATE TABLE grade (
    student_id INTEGER,
    assignment_id INTEGER,
    score REAL CHECK (score > 0 AND score <= 100),
    FOREIGN KEY (student_id) REFERENCES student(student_id),
    FOREIGN KEY (assignment_id) REFERENCES assignment(assignment_id)
);

INSERT INTO student VALUES
(123, 'JoeyG', 'jegonzal@berkeley.edu'),
(456, 'NargesN', 'norouzi@berkeley.edu');

INSERT INTO assignment VALUES
(1, 'easy assignment'),
(2, 'hard assignment');
Running query in 'duckdb'
Out[16]:
Count
2
ResultSet : to convert to pandas, call .DataFrame() or to polars, call .PolarsDataFrame()
In [17]:
%%sql 
INSERT INTO grade VALUES
(123, 1, 80),
(123, 2, 42),
(456, 2, 100);
Running query in 'duckdb'
Out[17]:
Count
3
ResultSet : to convert to pandas, call .DataFrame() or to polars, call .PolarsDataFrame()
In [18]:
%sql SELECT * FROM grade;
Running query in 'duckdb'
Out[18]:
student_id assignment_id score
123 1 80.0
123 2 42.0
456 2 100.0
ResultSet : to convert to pandas, call .DataFrame() or to polars, call .PolarsDataFrame()


Basic Queries¶

SELECT and FROM¶

Every SQL query must contain a SELECT and FROM clause.

  • SELECT: Specifies the column(s) to return in the output.
  • FROM: Specifies the database table from which to extract data.
In [19]:
%%sql
SELECT * FROM Dragon;
Running query in 'duckdb'
Out[19]:
name year cute
hiccup 2010 10
drogon 2011 -100
dragon 2 2019 0
puff 2010 100
smaug 2011 None
ResultSet : to convert to pandas, call .DataFrame() or to polars, call .PolarsDataFrame()
In [20]:
%%sql
SELECT cute, year FROM Dragon;
Running query in 'duckdb'
Out[20]:
cute year
10 2010
-100 2011
0 2019
100 2010
None 2011
ResultSet : to convert to pandas, call .DataFrame() or to polars, call .PolarsDataFrame()

Aliasing with AS¶

In [21]:
%%sql
SELECT cute AS cuteness,
       year AS "birth year"
FROM Dragon;
Running query in 'duckdb'
Out[21]:
cuteness birth year
10 2010
-100 2011
0 2019
100 2010
None 2011
ResultSet : to convert to pandas, call .DataFrame() or to polars, call .PolarsDataFrame()

Uniqueness with DISTINCT¶

In [22]:
%%sql
SELECT DISTINCT year
FROM Dragon;
Running query in 'duckdb'
Out[22]:
year
2010
2019
2011
ResultSet : to convert to pandas, call .DataFrame() or to polars, call .PolarsDataFrame()

Filtering with WHERE¶

In [23]:
%%sql
SELECT name, year
FROM Dragon
WHERE cute > 0;
Running query in 'duckdb'
Out[23]:
name year
hiccup 2010
puff 2010
ResultSet : to convert to pandas, call .DataFrame() or to polars, call .PolarsDataFrame()
In [24]:
%%sql
SELECT name, cute, year
FROM Dragon
WHERE cute > 0 OR year > 2013;
Running query in 'duckdb'
Out[24]:
name cute year
hiccup 10 2010
puff 100 2010
dragon 2 0 2019
ResultSet : to convert to pandas, call .DataFrame() or to polars, call .PolarsDataFrame()
In [25]:
%%sql
SELECT name, year
FROM Dragon 
WHERE name IN ('puff', 'hiccup');
Running query in 'duckdb'
Out[25]:
name year
puff 2010
hiccup 2010
ResultSet : to convert to pandas, call .DataFrame() or to polars, call .PolarsDataFrame()

Checking against NULL¶

In [26]:
%%sql
SELECT name, cute
FROM Dragon
WHERE cute IS NOT NULL;
Running query in 'duckdb'
Out[26]:
name cute
hiccup 10
drogon -100
dragon 2 0
puff 100
ResultSet : to convert to pandas, call .DataFrame() or to polars, call .PolarsDataFrame()

Ordering data using ORDER BY¶

In [27]:
%%sql
SELECT *
FROM Dragon
ORDER BY cute DESC;
Running query in 'duckdb'
Out[27]:
name year cute
puff 2010 100
hiccup 2010 10
dragon 2 2019 0
drogon 2011 -100
smaug 2011 None
ResultSet : to convert to pandas, call .DataFrame() or to polars, call .PolarsDataFrame()

Restricting output with LIMIT and OFFSET¶

In [28]:
%%sql
SELECT *
FROM Dragon
LIMIT 2;
Running query in 'duckdb'
Out[28]:
name year cute
hiccup 2010 10
drogon 2011 -100
ResultSet : to convert to pandas, call .DataFrame() or to polars, call .PolarsDataFrame()
In [29]:
%%sql
SELECT *
FROM Dragon
LIMIT 2
OFFSET 1;
Running query in 'duckdb'
Out[29]:
name year cute
drogon 2011 -100
dragon 2 2019 0
ResultSet : to convert to pandas, call .DataFrame() or to polars, call .PolarsDataFrame()

Sampling¶

What if we wanted a random sample:

In [30]:
%%sql
SELECT *
FROM Dragon
ORDER BY RANDOM() 
LIMIT 2
Running query in 'duckdb'
Out[30]:
name year cute
smaug 2011 None
dragon 2 2019 0
ResultSet : to convert to pandas, call .DataFrame() or to polars, call .PolarsDataFrame()
In [31]:
%%sql
SELECT * 
FROM Dragon USING SAMPLE reservoir(2 ROWS) REPEATABLE (100);
Running query in 'duckdb'
Out[31]:
name year cute
puff 2010 100
drogon 2011 -100
ResultSet : to convert to pandas, call .DataFrame() or to polars, call .PolarsDataFrame()

Grouping Data with GROUP BY¶

In [32]:
%%sql
SELECT *
FROM Dish;
Running query in 'duckdb'
Out[32]:
name type cost
ravioli entree 10
ramen entree 13
taco entree 7
edamame appetizer 4
fries appetizer 4
potsticker appetizer 4
ice cream dessert 5
ResultSet : to convert to pandas, call .DataFrame() or to polars, call .PolarsDataFrame()

A small note: the fact that type is highlighted in green below is a consequence of Jupyter assuming that we are writing Python code (where type is a built-in keyword). type does not have a special meaning in SQL, so the color below does not indicate any special functionality. When we run the cell, Jupyter realizes it should recognize the code as SQL.

In [33]:
%%sql
SELECT type
FROM Dish;
Running query in 'duckdb'
Out[33]:
type
entree
entree
entree
appetizer
appetizer
appetizer
dessert
ResultSet : to convert to pandas, call .DataFrame() or to polars, call .PolarsDataFrame()
In [34]:
%%sql
SELECT type
FROM Dish
GROUP BY type;
Running query in 'duckdb'
Out[34]:
type
entree
dessert
appetizer
ResultSet : to convert to pandas, call .DataFrame() or to polars, call .PolarsDataFrame()
In [35]:
%%sql
SELECT type, SUM(cost)
FROM Dish
GROUP BY type;
Running query in 'duckdb'
Out[35]:
type sum("cost")
entree 30
dessert 5
appetizer 12
ResultSet : to convert to pandas, call .DataFrame() or to polars, call .PolarsDataFrame()
In [36]:
%%sql
SELECT type, 
       SUM(cost), 
       MIN(cost),
       MAX(name)
FROM Dish
GROUP BY type;
Running query in 'duckdb'
Out[36]:
type sum("cost") min("cost") max("name")
entree 30 7 taco
dessert 5 5 ice cream
appetizer 12 4 potsticker
ResultSet : to convert to pandas, call .DataFrame() or to polars, call .PolarsDataFrame()
In [37]:
%%sql
SELECT year, COUNT(cute)
FROM Dragon
GROUP BY year;
Running query in 'duckdb'
Out[37]:
year count(cute)
2010 2
2011 1
2019 1
ResultSet : to convert to pandas, call .DataFrame() or to polars, call .PolarsDataFrame()
In [38]:
%%sql
SELECT year, COUNT(*)
FROM Dragon
GROUP BY year;
Running query in 'duckdb'
Out[38]:
year count_star()
2010 2
2011 2
2019 1
ResultSet : to convert to pandas, call .DataFrame() or to polars, call .PolarsDataFrame()

Working with the sql results as Python variables¶

By default, executing a query with a magic command produces output but doesn't save it into any Python variable:

In [39]:
%sql SELECT * FROM Dragon
Running query in 'duckdb'
Out[39]:
name year cute
hiccup 2010 10
drogon 2011 -100
dragon 2 2019 0
puff 2010 100
smaug 2011 None
ResultSet : to convert to pandas, call .DataFrame() or to polars, call .PolarsDataFrame()

This can be inconvenient if you later want to do further processing of these data in Python.

Storing one-line %sql queries¶

For simple one-line queries, you can use IPython's ability to store the result of a magic command like %sql as if it were any other Python statement and save the output to a variable:

In [40]:
dragon_table = %sql SELECT * FROM Dragon
dragon_table
Running query in 'duckdb'
Out[40]:
name year cute
hiccup 2010 10
drogon 2011 -100
dragon 2 2019 0
puff 2010 100
smaug 2011 None
ResultSet : to convert to pandas, call .DataFrame() or to polars, call .PolarsDataFrame()

As noted above, the result of the query is a Python variable of type ResultSet, more specifically:

In [41]:
type(dragon_table)
Out[41]:
sql.run.ResultSet

You need to manually convert it to a pandas DataFrame if you want to do pandas-things with its content:

In [42]:
dragon_df = dragon_table.DataFrame()
dragon_df
Out[42]:
name year cute
0 hiccup 2010 10.0
1 drogon 2011 -100.0
2 dragon 2 2019 0.0
3 puff 2010 100.0
4 smaug 2011 NaN

You can configure jupysql to automatically convert all outputs to Pandas DataFrames. This can be handy if you intend all your Python-side work to be done with Pandas, as it saves you from manually having to call .DataFrame() first on all outputs. On the other hand, you don't get access to the original SQL ResultSet object, which has a number of interesting properties and capabilities. You can learn more about those in the jupysql documentation.

For now, let's turn this on so you can see how this simplified "pandas all the way" workflow looks like:

In [43]:
%config SqlMagic.autopandas = True
In [44]:
dragon_df = %sql SELECT * FROM Dragon
dragon_df
Running query in 'duckdb'
Out[44]:
name year cute
0 hiccup 2010 10.0
1 drogon 2011 -100.0
2 dragon 2 2019 0.0
3 puff 2010 100.0
4 smaug 2011 NaN
In [45]:
type(dragon_df)
Out[45]:
pandas.core.frame.DataFrame

Storing multi-line %%sql queries¶

For a more complex query that won't fit in one line, such as for example:

In [46]:
%%sql
SELECT year, COUNT(*)
FROM Dragon
GROUP BY year;
Running query in 'duckdb'
Out[46]:
year count_star()
0 2010 2
1 2011 2
2 2019 1

You can use the variable << syntax in jupysql to store its output (this will honor your autopandas state and store either a sql.run.ResultState or a Pandas DataFrame):

In [47]:
%%sql dragon_years <<
SELECT year, COUNT(*)
FROM Dragon
GROUP BY year;
Running query in 'duckdb'
In [48]:
dragon_years
Out[48]:
year count_star()
0 2010 2
1 2011 2
2 2019 1
Closing sqlite
Closing duckdb