SQLite relational databases

1 · Learn and explore

Study this example, identify inputs and outputs, then trace the key variables. Run and adapt it in Python 3.

import sqlite3
with sqlite3.connect(':memory:') as connection:
    connection.execute('CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT NOT NULL, author TEXT NOT NULL)')
    books = [('Algorithms Made Clear', 'A. Writer'), ('Learning Python', 'B. Author')]
    connection.executemany('INSERT INTO books(title, author) VALUES (?, ?)', books)
    rows = connection.execute('SELECT title FROM books ORDER BY title').fetchall()
    

2 · Independent programming challenge

Create a table, insert records using parameterised SQL and query sorted results. Download the starter, design your solution, and add tests before revealing the answer.

Download starter + solution ZIP ↓

Stretch task: investigate time/space complexity or data validation, then write an evaluation of your approach.

import sqlite3
with sqlite3.connect(':memory:') as connection:
    connection.execute('CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT NOT NULL, author TEXT NOT NULL)')
    books = [('Algorithms Made Clear', 'A. Writer'), ('Learning Python', 'B. Author')]
    connection.executemany('INSERT INTO books(title, author) VALUES (?, ?)', books)
    rows = connection.execute('SELECT title FROM books ORDER BY title').fetchall()
    assert rows == [('Algorithms Made Clear',), ('Learning Python',)]
    print(rows)

3 · Debug and explain

Change one boundary condition in the starter or reference program. Predict which test fails, then repair it and document why.

Concept check: Why use SQL placeholders instead of string concatenation?

4 · Assess your learning

Describe the algorithm or design in your own words; include a normal test, a boundary test and an invalid input. Compare your answers with the reference solution and record one improvement.

Progress is stored only in this browser.