Capturing, managing and exchanging data

How data gets in, how it is selected and managed, and how systems swap it as CSV, JSON and XML. The robot exports its own readings.

A11.8Databases and big dataA level55 min

A database is only as good as the data that goes into it, and data rarely stays in one system. The class's run times are typed in by students, the robot's sensor readings arrive by themselves, the scoreboard reads them, and the competition organisers want the results in their own system. This lesson follows data through its life: how it is captured, selected, managed and exchanged. At GCSE (F7.1) you read and wrote CSV files; at A level you compare the standard formats and choose between them.

Capturing data

Choosing a method is a trade-off between accuracy, speed, cost and volume. Manual entry is cheap to set up but slow and error-prone; sensors capture huge volumes accurately but only of what they can measure.

Captured data should be checked on the way in. Validation checks that data is reasonable and follows the rules (a run time between 0 and 60 seconds, a date in the right format); it cannot tell whether the data is actually true. Verification checks that data matches its source, for example by typing it twice (double entry) or by reading it back to the person who gave it.

Selecting data

Selecting means choosing only the data that is relevant. At capture, a system should collect only what it needs: the less personal data it holds, the less there is to protect, and data protection law requires personal data to be limited to what is necessary for its purpose (F12.2). Later, queries select the records and fields a job needs, and a very large data set may be sampled, a smaller selection that represents the whole.

Managing data

A database management system (DBMS) is the software that manages the data for every program and user that needs it. It:

• stores, retrieves and updates data, hiding how it is physically stored, so programs do not depend on the file layout (data independence);
• keeps a data dictionary: data about the data, such as each table's fields, types, lengths, keys and validation rules;
• controls access, giving each user permission to see or change only certain tables or fields;
• enforces integrity: keys, referential integrity and constraints;
• controls concurrent access and transactions (lessons A11.6 and A11.7);
• takes backups and recovers the database after a failure.

Exchanging data

When two systems exchange data, both must agree on a format. There are three common text formats. Here are the same three sensor readings in each:

import csv, io, json
import xml.etree.ElementTree as ET

readings = [{"step": 0, "y": 0.0, "cm": 61.0},
            {"step": 1, "y": 10.0, "cm": 51.0},
            {"step": 2, "y": 20.0, "cm": 41.0}]

out = io.StringIO()
writer = csv.DictWriter(out, fieldnames=["step", "y", "cm"], lineterminator="\n")
writer.writeheader()
writer.writerows(readings)
as_csv = out.getvalue()

as_json = json.dumps(readings)

root = ET.Element("readings")
for r in readings:
    ET.SubElement(root, "reading", {k: str(v) for k, v in r.items()})
as_xml = ET.tostring(root, encoding="unicode")

for name, text in [("CSV", as_csv), ("JSON", as_json), ("XML", as_xml)]:
    print(f"{name}, {len(text)} characters:")
    print(text)
    print()

Run this in the simulator

CSV (comma-separated values) is a table: a header line, then one line per record, fields separated by commas. It is compact and every spreadsheet opens it. But it has no data types (every value is text until the receiver converts it), no nesting, and a comma inside a value must be quoted.

JSON (JavaScript Object Notation) writes objects in braces, as name and value pairs, and arrays in square brackets. Values can be strings, numbers, true, false, null, or further objects and arrays, so data can be nested. It maps directly onto Python dictionaries and lists, which is why web APIs mostly use it.

XML (Extensible Markup Language) marks data up with named tags, <reading> and </reading>, which may carry attributes and contain further elements. It is self-describing and very widely supported, and an XML document can be checked against a schema that sets out exactly which elements and types are allowed. It is also the most verbose of the three.

The receiving side parses the text back into data:

import csv, io, json
import xml.etree.ElementTree as ET

as_csv = "step,y,cm\n0,0.0,61.0\n1,10.0,51.0\n"
as_json = '[{"step": 0, "y": 0.0, "cm": 61.0}, {"step": 1, "y": 10.0, "cm": 51.0}]'
as_xml = '<readings><reading step="0" y="0.0" cm="61.0" /><reading step="1" y="10.0" cm="51.0" /></readings>'

rows = list(csv.DictReader(io.StringIO(as_csv)))
print("CSV: ", rows[1], "cm is", type(rows[1]["cm"]).__name__)

data = json.loads(as_json)
print("JSON:", data[1], "cm is", type(data[1]["cm"]).__name__)

root = ET.fromstring(as_xml)
second = root.findall("reading")[1]
print("XML: ", second.attrib, "cm is", type(second.get("cm")).__name__)

Run this in the simulator

Only JSON brings back a number as a number; CSV and XML attributes hand back text that the receiver must convert.

Businesses exchanging orders and invoices between their systems also use EDI (electronic data interchange): agreed, standard formats for business documents, sent automatically from one organisation's system to another's with no person retyping them.

Task: three formats, one set of readings

The robot starts facing a wall. Take four readings, numbered step 0 to 3. For each: record step (a whole number), y (the second value of position(), rounded to 1 decimal place) and cm (the value of distance()), then drive forward 10 cm at speed 40 before the next reading (no drive after step 3).

Then, from that one list of readings:

1. Write readings.csv: the header line step,y,cm, then one line per reading, such as 1,10.0,51.0.
2. Write readings.json with json.dump: a list of four objects, each with the keys step, y and cm.
3. Read readings.json back with json.load and print json readings: <n>, the number of objects in it.
4. Print the readings as XML: a line <readings>, then one line per reading exactly in the form <reading step="1" y="10.0" cm="51.0"/> (indenting is allowed), then </readings>.

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

import json

readings = []

Challenges

1. Add a seconds field, the time of each reading from clock(). Which of your three outputs needed changing, and how?
2. Nest the readings inside a JSON object that also records the robot's name and the date. Could CSV hold the same thing?
3. A robot's name is Ada, the second. What goes wrong in a CSV file written by hand, and how does the csv module deal with it?