SQL

Python and the shell are procedural: you spell out how to do something, one step at a time. HTML describes a page’s structure, and CSS describes its presentation. JSON, YAML, and CSV represent data. CSS selectors, regular expressions, and globs are query languages: you describe what to find.

SQL (Structured Query Language, usually said “sequel”) is declarative: you state what result you want, and the database chooses how to compute it.

The boss in this comic asks for SQL without knowing what a database is:

Where SQL is used

Many interactive websites store data in a SQL database. When you log in, post a comment, or like a photo, the backend may run SQL to read or change a row.

The database can choose an index and query plan based on the requested result. For data stored in a database, this is usually faster than loading every row into Python and filtering it in a loop.

The bell-curve meme’s beginners and experts give the same advice:

SQL is also common in data-science and backend technical interviews. This course covers the everyday commands, a two-table join, running SQL from Python, and serving query results on the web. Window functions, subqueries, and other advanced features belong to a later database course. The final project uses these tools to build a Twitter clone.

A database in a file

We will use SQLite, which stores an entire SQL database in one file and requires no database server. Python includes SQLite through the sqlite3 module, and many systems provide a sqlite3 command-line program. You can also run the same SQL in a browser at sqlite.org/fiddle. The interactive sessions below use the sqlite3 command; its prompt is sqlite>.

A database is a set of tables, and a table is a grid of rows and columns, like one sheet of a spreadsheet with named, typed columns. We will build the skeleton of a social network: a table of users, and a table of the messages they post. We create a table with the CREATE TABLE command, naming each column and its type:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    username TEXT NOT NULL UNIQUE,
    password TEXT NOT NULL,
    age INTEGER
);

id is an integer that is the primary key, the column that uniquely identifies each row, and SQLite fills it in automatically as 1, 2, 3, and so on. username is text that is NOT NULL (every user must have one) and UNIQUE (no two users may share it). password is required text, and age is an integer with no constraints, so it is allowed to be missing. On success the command prints nothing at all, the same “silence means success” you saw with passing doctests.

The second table stores messages, and it needs a way to say who sent each one:

CREATE TABLE messages (
    id INTEGER PRIMARY KEY,
    sender_id INTEGER NOT NULL REFERENCES users(id),
    message TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT current_timestamp
);

The sender_id column holds the id of the user who wrote the message, and REFERENCES users(id) records that it points into the users table. In a relational database, each fact lives in one place and other tables point to it by key. We therefore store the sender’s id instead of copying the username into every message. The created_at column defaults to current_timestamp, so every message is automatically stamped with the time it was inserted.

Putting data in

INSERT adds a row. It lists the columns to fill and their values:

INSERT INTO users (username, password, age) VALUES ('Trump', 'TRUMP', 76);
INSERT INTO users (username, password, age) VALUES ('Evan', 'correct horse battery staple', 7);
INSERT INTO users (username, password) VALUES ('Kristen', 'Possible-Rich-Absolute-Battle');

Each statement creates one new row, and again SQLite prints nothing on success. We left age out of the last row, so Kristen’s age is stored as NULL, SQL’s word for a missing value. We did not set id on any of them, because the primary key fills itself in.

These examples store passwords as plain readable text, which is a serious security mistake that real applications should not make. A real site stores a scrambled hash of the password so that a stolen database does not hand over everyone’s login. We are keeping them plain only so the examples stay readable, and Evan’s password is the famous xkcd passphrase, included here entirely for our own amusement.

Asking questions

SELECT, the main command on the quiz, retrieves rows. Its shape is select these columns, from this table, where this condition holds. The simplest version uses * to request every column of every row:

SELECT * FROM users;

SQLite prints the matching rows as a table:

id  username  password                          age
--  --------  --------------------------------  ---
1   Trump     TRUMP                             76
2   Biden     12345                             79
3   Evan      correct horse battery staple      7
4   Isaac     soccer                            4
5   Aaron     guaguagua                         3
6   Aurelia                                     2
7   Mike      524euTjrWm6uK2C5iw8mC6aNgX1JI78o  38
8   Kristen   Possible-Rich-Absolute-Battle

Each row is one user, each column is one field, and the id values are the primary keys SQLite assigned as we inserted. Kristen’s age is blank because we never gave it a value.

Usually you do not want every column or every row. You pick columns by naming them, and you keep only the rows you want with a WHERE clause:

SELECT username, age FROM users WHERE age >= 18;

username  age
--------  ---
Trump     76
Biden     79
Mike      38

The result contains two columns and only the three rows whose age is at least 18. To count matching rows rather than list them, wrap the selection in count(*):

SELECT count(*) FROM messages WHERE sender_id = 6;

count(*)
--------
3

The result says that user 6 sent three messages. LIKE matches text patterns, with % standing for any run of characters:

SELECT id, username FROM users WHERE username LIKE 'A%';

id  username
--  --------
5   Aaron
6   Aurelia

'A%' matches any username that starts with a capital A. % plays the same role in LIKE that * plays in a shell glob and .* plays in a regular expression. ORDER BY age DESC sorts results by age from highest to lowest. Because NULL is missing rather than a value, test for it with WHERE age IS NULL rather than = NULL.

The quiz gives you data and a SELECT statement and asks you to write down its output on paper using open notes. Practice by running the statements in quiz_practice_problems.sql against the sample database in quiz_practice_schema.sql, either with sqlite3 locally or by pasting the schema into sqlite.org/fiddle. Predict each result set before you run it, then compare with the answers. Reload the schema before any UPDATE or DELETE problem, since those change the data and would otherwise throw off the counts in the problems after them.

Joining two tables

Every SELECT so far read from one table. A join follows messages.sender_id to users.id, pairing each message with its user in one query:

SELECT users.username, users.age, messages.message
FROM messages
JOIN users ON messages.sender_id = users.id
WHERE users.age >= 18;

username  age  message
--------  ---  --------------------------------------------------
Trump     76   I'm a baby
Biden     79   I'm a baby
Mike      38   I'm an adult
Mike      38   WTF is SQL?!  I thought you liked the snake thing.

JOIN users ON messages.sender_id = users.id attaches each message to the user whose id matches that message’s sender_id. A result row can then contain columns from both tables: users.username, users.age, and messages.message. Because a column name like id lives in both tables, we write table.column to say which one we mean. Your final project’s home page uses this join without the WHERE, plus ORDER BY messages.created_at DESC, to show every message next to the account that posted it.

Changing and removing

UPDATE edits existing rows, setting columns to new values wherever a condition holds:

UPDATE users SET password = 'hunter2' WHERE username = 'Aurelia';

The statement finds every row where the username is Aurelia and overwrites its password column. A following SELECT confirms the change. DELETE removes whole rows that match a condition:

DELETE FROM messages WHERE message = 'I''m a baby';

(The doubled quote '' is how you put a literal apostrophe inside a SQL string.) Checking the count before and after shows the effect:

sqlite> SELECT count(*) FROM messages;
10
sqlite> DELETE FROM messages WHERE message = 'I''m a baby';
sqlite> SELECT count(*) FROM messages;
6

Four rows matched the message text, so the count dropped from ten to six in one command. Leaving off the WHERE clause applies the change to every row. DELETE FROM messages; with no WHERE empties the entire table, and UPDATE users SET age = 0; resets everyone at once. SQLite gives no confirmation prompt, so check the WHERE clause before running an UPDATE or DELETE.

SQL from Python

Python’s sqlite3 module runs SQL from a program:

import sqlite3

connection = sqlite3.connect('social.db')
cursor = connection.cursor()
cursor.execute("SELECT username, age FROM users WHERE age >= 18")
for row in cursor.fetchall():
    print(row)

connect opens the database file, cursor provides a handle for commands, and execute runs a SQL statement passed as a Python string. fetchall hands back the results as a list of rows, and each row is a plain tuple, so we can loop over them like any Python list:

('Trump', 76)
('Biden', 79)
('Mike', 38)

Serving data on the web

FastAPI maps web addresses to Python functions. A minimal server answers one address with one message:

from fastapi import FastAPI
from fastapi.responses import HTMLResponse
import uvicorn

app = FastAPI()

@app.get('/', response_class=HTMLResponse)
async def index():
    return 'hello <b>world</b>'

if __name__ == '__main__':
    uvicorn.run("webserver:app", host='127.0.0.1', port=8080, reload=True)

The @app.get('/') line maps the URL path / to the function beneath it, so whatever that function returns becomes the page at that address. The function returns the HTML string hello <b>world</b>, so a browser pointed at http://127.0.0.1:8080 shows hello world, with “world” bold. The last line hands the app to uvicorn, the program that actually listens for browser requests, on port 8080 of your own machine.

A route can run a SELECT instead of returning a fixed greeting:

import sqlite3
from fastapi import FastAPI
from fastapi.responses import HTMLResponse
import uvicorn

app = FastAPI()

@app.get('/', response_class=HTMLResponse)
async def timeline():
    connection = sqlite3.connect('social.db')
    cursor = connection.cursor()
    cursor.execute("SELECT message FROM messages ORDER BY created_at DESC")
    rows = cursor.fetchall()
    connection.close()
    html = ''
    for row in rows:
        html = html + '<p>' + row[0] + '</p>'
    return html

Every time a browser visits /, the function opens the database, selects all messages newest-first, and wraps each one in a <p> tag. The returned HTML displays a feed with the newest message at the top.

The line html + '<p>' + row[0] + '</p>' pastes database text straight into the page. If a user puts HTML or a <script> tag in a message, the browser will run it. Jinja2 templates escape inserted text automatically; hand-built strings do not, so never paste untrusted text straight into a project page.

Form submissions

Our page reads the database but does not let a visitor add a row. Form submissions and POST requests let a visitor create a message. This week’s APIs and Web Interfaces lab asks you to build a FastAPI server with these tools.

The final project uses the users and messages tables, SELECT and INSERT, the sqlite3 module, and FastAPI routes.