SQL: SELECT

SELECT, FROM, WHERE and ORDER BY on the class's robot runs.

F7.3Files and databasesGCSE15 min

Do this lesson in the simulator

SQL, Structured Query Language, is how you ask a database a question. It reads almost like English, and one line of it can do what a loop and an if would take ten lines to do in Python. This lesson asks questions of the class's robot runs.

# logbook.py: the class's robot runs as a relational database
import sqlite3

ROBOTS = [(1, "Ada", "green"), (2, "Bolt", "red"), (3, "Cog", "blue")]
RUNS = [(1, 1, "wall", 42.0, 3.1), (2, 1, "square", 80.0, 9.4), (3, 2, "wall", 38.0, 2.7),
        (4, 1, "wall", 45.5, 3.0), (5, 3, "square", 76.5, 8.8), (6, 2, "square", 81.0, 10.2)]

def open_db():
    """A fresh database with the robots and runs tables."""
    db = sqlite3.connect(":memory:")
    db.execute("CREATE TABLE robots (robot_id INTEGER PRIMARY KEY, name TEXT, colour TEXT)")
    db.execute("CREATE TABLE runs (run_id INTEGER PRIMARY KEY, robot_id INTEGER REFERENCES robots(robot_id), "
               "task TEXT, cm REAL, seconds REAL)")
    db.executemany("INSERT INTO robots VALUES (?, ?, ?)", ROBOTS)
    db.executemany("INSERT INTO runs VALUES (?, ?, ?, ?, ?)", RUNS)
    return db

def show(db, sql):
    """Run a query and print each record on its own line."""
    for row in db.execute(sql):
        print(*row)

show(db, sql) runs a query and prints each record, so the SQL is all you need to write.

SELECT and FROM

# the two lines every program starts with: the commands, then the robot
from bugbot import *
connect()

import logbook
db = logbook.open_db()

logbook.show(db, "SELECT * FROM runs")
print("---")
logbook.show(db, "SELECT task, cm FROM runs")

Run this in the simulator

SELECT says which fields you want, FROM says which table. * means every field. Change the field list and run again: the query returns exactly the columns you name, in that order.

SQL keywords are written in capitals by convention, which makes them easy to tell apart from table and field names. The query itself is just a string in Python.

WHERE

WHERE keeps only the records that match a condition:

# the two lines every program starts with: the commands, then the robot
from bugbot import *
connect()

import logbook
db = logbook.open_db()

logbook.show(db, "SELECT run_id, cm FROM runs WHERE cm > 50")
print("---")
logbook.show(db, "SELECT run_id, cm FROM runs WHERE task = 'wall'")
print("---")
logbook.show(db, "SELECT run_id, task, cm FROM runs WHERE task = 'square' AND cm > 78")

Run this in the simulator

Conditions use = (a single equals, unlike Python), <> or != for not equal, <, >, <=, >=, and join with AND and OR. Text values go in single quotes, so they do not clash with the Python string around the query.

ORDER BY

ORDER BY sorts the results: ASC for ascending (the default), DESC for descending.

# the two lines every program starts with: the commands, then the robot
from bugbot import *
connect()

import logbook
db = logbook.open_db()

logbook.show(db, "SELECT run_id, cm FROM runs ORDER BY cm DESC")
print("---")
logbook.show(db, "SELECT run_id, seconds FROM runs WHERE task = 'wall' ORDER BY seconds ASC")

Run this in the simulator

The clauses always come in this order: SELECT, FROM, WHERE, ORDER BY. The second query answers a real question in one line: which wall runs were fastest? The sorting algorithms of module F5 are hidden inside the database.

Using the answers in Python

A query's results are a list of tuples, so the rest of the program can use them. Here the robot replays the longest run:

# the two lines every program starts with: the commands, then the robot
from bugbot import *
connect()

import logbook
db = logbook.open_db()

best = db.execute("SELECT run_id, cm FROM runs WHERE task = 'wall' ORDER BY cm DESC").fetchall()[0]
print("longest wall run:", best)
forward(50, distance=best[1])

Run this in the simulator

fetchall() gives every matching record as a list; [0] is the first, and here that is the longest.

Task: the longest square runs

Write one SQL query that finds the run_id and cm of every square run longer than 77 cm, longest first, and print each result as run 6: 81.0 cm. Then drive forward the distance of the shortest of those runs, divided by 4.

# the two lines every program starts with: the commands, then the robot
from bugbot import *
connect()

import logbook
db = logbook.open_db()

Challenges

  1. Find every run that took less than 5 seconds, fastest first.
  2. Find the robots whose colour is not green.
  3. Write the Python loop that does the same job as SELECT run_id FROM runs WHERE cm > 50. How many lines is it?