Project: the logbook

Log every run to a file, load the file into a table, and find the best run with SQL.

F7.5Files and databasesGCSE25 min

A real robot team keeps a logbook: every run goes into a file that survives between sessions, and when the team wants answers, the log is loaded into a database and queried. In this project BugBot does both. It drives a distance you choose, logs the run to a file, loads the whole log into a database table, and uses SQL to report the best run so far.

The brief

The robot asks How far? and drives that far forward, timing the run. It appends the run to logbook.csv, numbering it one after the last run in the file. Then it loads every line of the file into a database table, and uses SQL to print how many runs are logged and the run with the highest speed (distance divided by time).

The logbook file

run,cm,seconds
1,42,3.1
2,38,2.7
3,80,9.4
4,45.5,3.0

Plan

Step 1: read the log and find the next run number

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

with open("logbook.csv") as f:
    lines = f.read().splitlines()
records = lines[1:]                      # everything after the header
next_run = len(records) + 1
print(len(records), "runs so far; the next is run", next_run)

Run this in the simulator

Step 2: load the file into a table

The table has to be made before rows can go into it. CREATE TABLE names the fields and their types; a GCSE exam will not ask you to write one, so it is given here:

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

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE runs (run INTEGER PRIMARY KEY, cm REAL, seconds REAL)")

with open("logbook.csv") as f:
    f.readline()
    for line in f:
        run, cm, seconds = line.strip().split(",")
        db.execute("INSERT INTO runs VALUES (?, ?, ?)", (int(run), float(cm), float(seconds)))

for row in db.execute("SELECT run, cm, seconds, cm / seconds FROM runs ORDER BY cm / seconds DESC"):
    print(row)

Run this in the simulator

A query can calculate: cm / seconds is worked out for every record, and sorting by it puts the fastest first.

Step 3: put it together

Ask, drive, append, load, query. Test with a distance of your own, then look at logbook.csv above to see your run in it. Run again: the next run number goes up, because the file remembers. Press Reset file when you want to start the log again.

Task: the logbook

Build the program from the brief. Print runs logged: <n> using SELECT COUNT(*), and fastest: run <n> at <speed> cm/s with the speed rounded to 1 decimal place, using ORDER BY. The task types 28, and starts from the four-run logbook above every time.

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

import sqlite3

Challenges

1. Add a task name to each run, and report the fastest run of each task.
2. Refuse to log a run if the robot stopped more than 2 cm short of the distance asked for.
3. Store the log in two files, runs.csv and robots.csv, and load them into two linked tables.