A. Programming Concepts and Skills · Data Types, Files and Expressions

XML Files and Databases

A3.1 A1.2 A1.5About 75 minutes

Learning goals

I can describe the structure of an XML document: elements, attributes, text and nesting.
I can read data from an XML file and write a new XML file with xml.etree.ElementTree.
I can create an SQLite database table, insert rows and query them with SQL from Python.
I can explain SQL injection and prevent it with placeholders.

Words to know

XML
Extensible Markup Language: a text format that stores data as a tree of nested, named elements such as <stop>...</stop>.

element
One part of an XML tree: a start tag, content, and an end tag, e.g. <time>07:05</time>.

attribute
A name="value" pair inside a start tag, such as id="S1".

well-formed
XML that follows the syntax rules: one root element, every tag closed, properly nested, attribute values in quotes.

database
An organized, persistent collection of data managed by software that can search, sort and update it efficiently and safely.

table, row, column
In a relational database a table holds records: each row is a record, each column a field with a type.

SQL
Structured Query Language, used to create tables and to insert, update, delete and select data.

SQL injection
An attack in which user input is inserted into SQL text and changes what the query does. Prevented with placeholders.

Two more ways data is stored

The curriculum's list of external files ends with XML files and databases. You use both every day without seeing them: office documents (.docx, .xlsx) are zipped folders of XML files, RSS news feeds are XML, and your school's marks, your bank account and every online store live in databases.

In this lesson you will read and write XML with Python's standard library, and you will create and query a real database with SQLite, a database engine that is built into Python and stores a whole database in one file.

XML: data as a tree

An XML document is text, like CSV or JSON, but its structure is a tree of elements:

An element has a start tag <stop> and an end tag </stop>, with content between them.
Elements can contain other elements (children) and text.
A start tag can carry attributes: <stop id="S1" accessible="yes">.
There is exactly one root element that contains everything else.

XML is well-formed only if every tag is closed and properly nested, attribute values are in quotes, and there is a single root. A parser refuses badly formed XML with a ParseError that gives the line and column, which makes errors easy to locate.

<?xml version="1.0" encoding="UTF-8"?>

<name>Union Station</name>

</stop>

<name>Maple Ave</name>

</stop>

</stop>

</route>

The file route.xml used in this lesson. Save it with this exact text (UTF-8) next to your program.

An XML document is a tree. The root element route contains three stop elements; each stop has attributes (id, accessible) and child elements (name, time) whose text holds the data. (Union Station's name is shortened in the drawing.)

import xml.etree.ElementTree as ET

tree = ET.parse("route.xml")

root = tree.getroot()

print(root.tag, root.get("number"), root.get("name"))

for stop in root.findall("stop"):

name = stop.find("name").text

time = stop.find("time").text

mark = "" if stop.get("accessible") == "yes" else " (not accessible)"

print(time, name + mark)

Reading XML with ElementTree. findall returns the child elements with a tag, find returns the first one, .text is the text inside an element and .get reads an attribute. All values come back as strings. (The route and its timetable are invented for the example.) Output, line by line: route 44 Lakeshore | 07:05 Union Station | 07:12 Maple Ave (not accessible) | 07:20 Côté Park

Worked example

Example 1: From XML to records

Turn the stops in route.xml into a list of Stop dataclass records with fields ident: str, name: str, minutes: int (minutes after midnight) and accessible: bool, then print the travel time from the first stop to the last.

Every value in XML is text, so each field needs a conversion (Lesson 3).
ident = stop.get("id") and name = stop.find("name").text are already strings.
Time "07:20": h, m = stop.find("time").text.split(":") then minutes = int(h) * 60 + int(m). For 07:20 that is 440.
Accessible: stop.get("accessible") == "yes" gives a real Boolean. (Do not use bool("no"), which is True because the string is not empty.)
Build the list with a comprehension, then travel time = stops[-1].minutes - stops[0].minutes = 440 − 425 = 15 minutes. Convert back for display with divmod(minutes, 60) (Lesson 1).

A list of 3 Stop records; travel time 15 minutes.

import xml.etree.ElementTree as ET

books = [("Kiss of the Fur Queen", "Tomson Highway", 1998),

("Anne of Green Gables", "L. M. Montgomery", 1908)]

root = ET.Element("library")

for title, author, year in books:

b = ET.SubElement(root, "book", year=str(year))

ET.SubElement(b, "title").text = title

ET.SubElement(b, "author").text = author

ET.indent(root)

ET.ElementTree(root).write("library.xml", encoding="utf-8", xml_declaration=True)

print(ET.tostring(root, encoding="unicode"))

Writing XML. Attribute values must be strings, so the year is converted with str(). ET.indent (Python 3.9 and later) adds the line breaks and spaces. Kiss of the Fur Queen is a novel by Cree author Tomson Highway; Anne of Green Gables is by L. M. Montgomery. Output, line by line: <library> | <book year="1998"> | <title>Kiss of the Fur Queen</title> | <author>Tomson Highway</author> | </book> | <book year="1908"> | <title>Anne of Green Gables</title> | <author>L. M. Montgomery</author> | </book> | </library>

XML from unknown sources

The Python documentation warns that the standard XML modules are not secure against some deliberately malicious XML data. For files you create yourself (as in this course) they are fine. For XML from strangers on the internet, professionals use hardened libraries and limits.

Databases: tables you can ask questions

A file holds data; a database also manages it. A database system can find matching rows quickly even among millions, lets several programs use the data safely at the same time, and makes sure a change either happens completely or not at all.

In a relational database, data lives in tables. A table is exactly the "list of records" from Lesson 7, with a fixed set of typed columns. You talk to the database in SQL:

CREATE TABLE members (id INTEGER PRIMARY KEY, name TEXT NOT NULL, ...) defines a table.
INSERT INTO members (name, grade, club) VALUES (?, ?, ?) adds a row.
SELECT name FROM members WHERE club = ? ORDER BY name returns matching rows.
UPDATE members SET grade = 12 WHERE id = 4 and DELETE FROM members WHERE id = 2 change data.

In Python: sqlite3.connect("file.db") opens (or creates) the database, execute runs SQL, fetchall() or a for loop reads the results as tuples, and commit() saves changes.

A database table is like a list of records: each row is one record and each column is one field with a fixed type. The SQL query underneath picks out the highlighted rows (Ben and Chloé).

import sqlite3

con = sqlite3.connect("clubs.db") # creates the file if needed

cur = con.cursor()

cur.execute("DROP TABLE IF EXISTS members")

cur.execute("""CREATE TABLE members (

id INTEGER PRIMARY KEY,

name TEXT NOT NULL,

grade INTEGER,

club TEXT)""")

people = [("Amara", 12, "Robotics"), ("Ben", 11, "Chess"),

("Chloé", 12, "Chess"), ("Dev", 10, "Robotics")]

cur.executemany("INSERT INTO members (name, grade, club) VALUES (?, ?, ?)", people)

con.commit()

club = "Chess"

for (name,) in cur.execute("SELECT name FROM members WHERE club = ? ORDER BY name", (club,)):

print(name)

print(cur.execute("SELECT club, COUNT(*), AVG(grade) FROM members GROUP BY club").fetchall())

con.close()

A small SQLite database, built into Python. CREATE TABLE defines columns and types, INSERT adds rows, commit saves them to the file, and SELECT asks questions. The ? placeholders pass values safely (see Example 2). Output, line by line: Ben | Chloé | [('Chess', 2, 11.5), ('Robotics', 2, 11.0)]

Worked example

Example 2: Why the question marks? (SQL injection)

A student builds a query by joining strings: "SELECT * FROM members WHERE name = '" + typed + "'". What happens if someone types x' OR '1'='1? How do placeholders fix it?

Joining gives the SQL text SELECT * FROM members WHERE name = 'x' OR '1'='1'.
'1'='1' is always true, so the condition matches every row: the user sees everyone's data. Cleverer inputs can even change or delete tables. This attack is called SQL injection.
With a placeholder, cur.execute("SELECT * FROM members WHERE name = ?", (typed,)), the database receives the SQL and the value separately. The typed text is only ever treated as a name to look for, never as SQL, so it simply matches no rows.
Rule: never build SQL by joining or formatting strings with user input. Always use placeholders.

String joining lets the input rewrite the query and return all rows. Placeholders keep data and code separate.

Just for fun

A SQL query walks into a bar, goes up to two tables and asks,

"May I JOIN you?" (Joining tables is a topic for a databases course in university.)

Hands-on activity

Your household library

You need: Python and 10 to 15 books, games or recipes from your home.

Step 1. Write library.xml by hand (in a text editor) with a book element for each item, using an attribute for the year and child elements for title and author. Copy titles and authors exactly as printed.

Step 2. Write a program that reads the XML, converts each book into a dataclass record, and prints them sorted by year.

Step 3. Write a second program that loads the same records into an SQLite table books, then asks three questions in SQL: all books before 2000, the number of books per author (GROUP BY), and the newest book (ORDER BY year DESC LIMIT 1).

Step 4. Deliberately break the XML (delete one closing tag) and read the ParseError message. Use its line and column to find the problem.

What to look for: well-formed XML, conversions from text to int, placeholders in every INSERT, commit() before closing, and a ParseError traced to the right line.

Your turn

In <stop id="S2" accessible="no"><name>Maple Ave</name></stop>, name the element tags, the attributes and the text.

Is <a><b>hi</a></b> well-formed XML? Why?

Write the Python that prints the id of every stop in root.

Why does stop.get("accessible") == "yes" work but bool(stop.get("accessible")) does not?

Write the SQL to list all members in grade 12, alphabetically.

What does con.commit() do, and what happens if you forget it?

Rewrite safely: cur.execute(f"DELETE FROM members WHERE name = '{n}'")

More practice: warm-up

What does XML stand for?

How many root elements can an XML document have?

Which Python module provides SQLite?

In a database table, what is a row?

Which SQL keyword chooses which rows to return?

More practice: on your own

Write the ElementTree code that adds a new stop S4 (Birch St, 07:31, accessible) to root and saves the file.

Compare XML and JSON for the same bus route. Give one advantage of each.

Write SQL that gives each club and its number of members, largest club first.

Convert the rows from SELECT name, grade, club FROM members into a list of Member dataclass records.

Why might a store use a database instead of a CSV file for its orders?

Challenge

Explain why id INTEGER PRIMARY KEY is useful, and what goes wrong if records are identified only by name.

A program inserts 10 000 rows, one execute and one commit per row, and it is very slow. Suggest two improvements.

Write a function route_to_db(xml_path, db_path) outline that copies every stop from the XML file into a stops table. Which conversions happen on the way?

Check your understanding

Quiz

In <time>07:05</time>, what is 07:05?

Which XML is well-formed?

What type is stop.get("id") in ElementTree?

Which SQL returns the names of members in the Chess club?

Why use ? placeholders in cur.execute?

What happens to inserted rows if the program closes the connection without commit()?

For parents and teachers

Big idea: A3.1 lists XML files and databases among the external files students should be able to read and write. Both rely on converting text to proper types (A1.2) and on lists of records (A1.5).

How to help: the household-library activity is a good conversation: ask your teen to show you an SQL question answered by their database, and to explain why the ? placeholders matter. Make sure book titles and authors are copied exactly from the items.

Common difficulties:

Forgetting that XML values are strings (and that bool("no") is True).
Overlapping tags in hand-written XML.
Forgetting commit().
Building SQL with f-strings and user input. Always use placeholders.