Why applications need databases

01The problem

Tallybook's invoicing service from the Software Engineering course still reads CSV exports. That works for a month-end report. It doesn't work for an app: two staff recording payments at the same moment can overwrite each other's changes, nothing stops a duplicate invoice or a discount of 50%, and finding one customer's invoices means reading every row in the file.

Applications keep their data in a database. In this course you'll design Tallybook's database, protect it with rules, change it safely, and put a real API in front of it, with every query and test run in Colab.

02The concept

What a relational database provides

SQLite

A complete relational database in a single file (or in memory), built into Python as sqlite3. Production systems often use PostgreSQL or MySQL; the ideas and almost all the SQL in this course carry over directly.

From Python

import sqlite3
conn = sqlite3.connect("tallybook.db")     # or ":memory:" for a throwaway database
conn.execute("SELECT ...", (value,))
conn.commit()

03Example

Create an in-memory database and load Tallybook's customers into a proper table:

import sqlite3

import pandas as pd

base = "https://academy.cloudtechanalytics.com/datasets/invoicing/"
customers = pd.read_csv(base + "customers.csv")

conn = sqlite3.connect(":memory:")
conn.execute("""
    CREATE TABLE customers (
        customer_id        TEXT PRIMARY KEY,
        business_name      TEXT NOT NULL,
        city               TEXT NOT NULL,
        vat_exempt         INTEGER NOT NULL,
        payment_terms_days INTEGER NOT NULL
    )
""")
conn.executemany("INSERT INTO customers VALUES (?, ?, ?, ?, ?)", customers.itertuples(index=False))
conn.commit()

print(conn.execute("SELECT COUNT(*) FROM customers").fetchone()[0], "customers")
for row in conn.execute("SELECT city, COUNT(*) AS n FROM customers GROUP BY city ORDER BY n DESC LIMIT 3"):
    print(row)

300 customers
('Lagos', 105)
('Kano', 52)
('Ibadan', 40)

Now try to add a customer whose ID already exists. A CSV file would accept it silently; the database refuses:

try:
    conn.execute("INSERT INTO customers VALUES ('C0001', 'Duplicate Stores', 'Lagos', 0, 30)")
except sqlite3.IntegrityError as error:
    print("Refused:", error)

Refused: UNIQUE constraint failed: customers.customer_id

That refusal is the database doing a job your application code would otherwise have to remember to do, everywhere, forever.

04Walkthrough

1. Run the cells in Colab. Query the number of VAT-exempt customers.
2. Change ":memory:" to "tallybook.db", run again, and look for the file in Colab's file panel.
3. Why is customer_id the primary key rather than business_name?
4. List three things Tallybook's app needs that a CSV file can't provide safely.

05Practice

06Check your understanding