Set Up the Project and the Question

Introduction

Learning functions one at a time is useful, but real skill comes from projects: starting with a question and finishing with an answer. In this course you will analyse a small library's book loans. It is made-up practice data, so you can experiment freely. The code is shown for reading. To run it, install Python and pandas (pip install pandas). Outputs shown came from real runs.

Start with a question

A vague goal such as "look at the library data" leads nowhere. A good question is specific, answerable with the data and useful for a decision:

• Which books are borrowed most?
• Which cities do our members come from, and how much do they borrow?
• How long do people keep books, and does it depend on the genre?
• Are loans growing month by month?

Write your question first. Our project question: "Which books and genres are most popular, and how long are books kept?"

The data

The library has three tables:

• books: id, title, genre, year
• members: id, name, city, joined
• loans: id, book_id, member_id, loaned_on, returned_on (empty if the book is still out)

Each loan links to one book and one member through ids. This is a small relational database.

Build it with Python and SQLite

SQLite stores a database in one file, and Python includes support for it:

import sqlite3, random, datetime as dt

random.seed(7)                      # makes the "random" loans repeatable
con = sqlite3.connect(":memory:")   # use "library.db" to save to a file
con.executescript("""
CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT NOT NULL, genre TEXT, year INTEGER);
CREATE TABLE members (id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT, joined TEXT);
CREATE TABLE loans (id INTEGER PRIMARY KEY,
                    book_id INTEGER REFERENCES books(id),
                    member_id INTEGER REFERENCES members(id),
                    loaned_on TEXT NOT NULL, returned_on TEXT);
""")

books = [(1,"Tidy Houses","Home",2019),(2,"Night Train","Crime",2021),
         (3,"Cooking Basics","Food",2018),(4,"The Quiet Sea","Fiction",2020),
         (5,"Garden Year","Home",2022),(6,"Cold Case","Crime",2017),
         (7,"Soup Stories","Food",2021),(8,"Paper Moons","Fiction",2023)]
members = [(1,"Ana","Leeds","2023-01-10"),(2,"Ben","York","2023-02-02"),
           (3,"Cy","Leeds","2023-03-15"),(4,"Dee","Hull","2023-04-01"),
           (5,"Eli","York","2023-05-20"),(6,"Fay","Leeds","2023-06-11")]
con.executemany("INSERT INTO books VALUES (?,?,?,?)", books)
con.executemany("INSERT INTO members VALUES (?,?,?,?)", members)

start = dt.date(2024, 1, 1)
loans = []
for i in range(1, 31):
    book, member = random.randint(1, 8), random.randint(1, 6)
    out = start + dt.timedelta(days=random.randint(0, 89))
    back = None if i in (28, 29, 30) else (out + dt.timedelta(days=random.randint(5, 30))).isoformat()
    loans.append((i, book, member, out.isoformat(), back))
con.executemany("INSERT INTO loans VALUES (?,?,?,?,?)", loans)
con.commit()

• The ? marks are placeholders, so values are passed safely and never glued into the SQL text.
• Loans 28, 29 and 30 have no return date, which means those books are still out.
• Because we set random.seed(7), the same data is created every time, so your results should match the ones in this course.

A data dictionary

A data dictionary explains every column, so you (and others) never have to guess:

Write one for every project. Include units, allowed values and known problems.

Check the load

Always check the data arrived correctly:

print(con.execute("SELECT COUNT(*) FROM loans").fetchone())

(30,)

Thirty loans, as intended.

Try it yourself

Create the database, then write your own data dictionary entry for every column of members. List three other questions the data could answer.

Summary

• Start with a specific, answerable question, and write it down.
• SQLite and Python let you build a small relational database with tables linked by ids.
• A data dictionary and a quick count check prevent confusion later.