SQL: two tables and changing data

Queries across two tables; INSERT, UPDATE and DELETE, and the robot logging its own runs.

F7.4Files and databasesGCSE20 min

Do this lesson in the simulator

Two things are left: asking questions that need data from two tables at once, and changing the data, adding new records, correcting old ones and removing mistakes. By the end of this lesson the robot writes its own runs into the database.

# 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)

Questions across two tables

The runs table holds robot_id, not names. To list runs with the robot's name, the query uses both tables, and matches the foreign key in runs to the primary key in robots:

# 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 runs.run_id, robots.name, runs.task, runs.cm
                    FROM runs, robots
                    WHERE runs.robot_id = robots.robot_id
                    ORDER BY runs.run_id""")

Run this in the simulator

runs.cm means "the cm field of the runs table", which matters when both tables could have a field with the same name. The condition runs.robot_id = robots.robot_id is what links each run to its robot. Leave it out and every run is paired with every robot, which is almost never what you want: try it.

A triple-quoted string lets a long query go over several lines, which is easier to read.

More conditions can be added with AND:

# 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 robots.name, runs.cm
                    FROM runs, robots
                    WHERE runs.robot_id = robots.robot_id AND robots.colour = 'green'
                    ORDER BY runs.cm DESC""")

Run this in the simulator

INSERT: adding a record

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

import logbook
db = logbook.open_db()

db.execute("INSERT INTO runs (run_id, robot_id, task, cm, seconds) VALUES (7, 3, 'wall', 44.0, 3.3)")
logbook.show(db, "SELECT * FROM runs WHERE run_id = 7")

Run this in the simulator

INSERT INTO names the table and the fields, and VALUES gives a value for each, in the same order.

Values from the program

The robot's own measurements go into the database with ? placeholders. The values are passed separately, and the database fills them in safely:

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

import logbook
db = logbook.open_db()

start = clock()
forward(50, distance=30)
x, y = position()
seconds = round(clock() - start, 1)
db.execute("INSERT INTO runs VALUES (?, ?, ?, ?, ?)", (7, 1, "wall", round(y, 1), seconds))
logbook.show(db, "SELECT * FROM runs WHERE robot_id = 1")

Run this in the simulator

Never build a query by joining text a user typed into the SQL string. A user who types SQL of their own could change what the query does, an attack called SQL injection. Placeholders stop it.

UPDATE and DELETE

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

import logbook
db = logbook.open_db()

db.execute("UPDATE robots SET colour = 'purple' WHERE name = 'Ada'")
db.execute("DELETE FROM runs WHERE cm < 40")
logbook.show(db, "SELECT * FROM robots")
print("---")
logbook.show(db, "SELECT * FROM runs")

Run this in the simulator

UPDATE ... SET ... WHERE changes fields in the records that match. DELETE FROM ... WHERE removes records. The WHERE is vital: DELETE FROM runs with no condition deletes every run. Because Ada's colour is stored once, one UPDATE changes it everywhere, which is the point of splitting the tables.

Task: log and correct

Using logbook.open_db(): drive forward 35 cm and insert it as run 7 for robot 2 (Bolt), task wall, with the distance the robot actually covered (position(), rounded to 1 decimal place) and its time. Update run 3's task to ramp. Delete run 5. Then, with one query across both tables, print every run by Bolt, in run order, as run 3 ramp 38.0.

# 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 the total distance each robot has driven, by writing a query for each robot inside a Python loop over the robots table.
  2. Delete every run of a robot, then the robot itself. Why does the order matter?
  3. Ask the user for a colour and show that robot's runs, using a ? placeholder.