Step 1: Setting Up the Northwind Database

To begin, install DB Browser for SQLite and download the Northwind SQL schema. Open the SQL tab in DB Browser, paste the SQL script, and click Execute. This will generate a local database named Northwinds.db.

Step 2: Read Records from the Database Using Python

The following Python script connects to the SQLite file, lists all tables, and allows the user to view records from any selected table:

# read_northwind.py

import sqlite3

def connect_to_database(db_path):
    try:
        return sqlite3.connect(db_path)
    except sqlite3.Error as e:
        print(f"Connection error: {e}")
        return None

def get_table_names(conn):
    cursor = conn.cursor()
    cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
    return [row[0] for row in cursor.fetchall()]

def display_table_records(conn, table_name):
    cursor = conn.cursor()
    cursor.execute(f"SELECT * FROM {table_name}")
    records = cursor.fetchall()
    field_names = [description[0] for description in cursor.description]

    print(" | ".join(field_names))
    for i, row in enumerate(records, start=1):
        print(f"{i:3} | " + " | ".join(str(v) for v in row))

def main():
    db_path = input("Enter DB path: ")
    conn = connect_to_database(db_path)
    if not conn:
        return

    tables = get_table_names(conn)
    for i, t in enumerate(tables, 1):
        print(f"{i}. {t}")

    choice = int(input("Select table number: "))
    if 1 <= choice <= len(tables):
        display_table_records(conn, tables[choice - 1])
    conn.close()

if __name__ == "__main__":
    main()

This script uses functions to modularize tasks — including connection handling, table retrieval, and row display. It avoids global variables and ensures user input is validated.

Step 3: Modify Database Records (Insert, Update, Delete)

The next Python script adds full CRUD functionality — allowing users to insert, update, and delete records. The user first selects a table, then chooses the type of modification to perform:

# modify_northwind.py (snippet)

def insert_record(conn, table_name):
    fields = get_field_names(conn, table_name)
    values = [input(f"{f}: ").strip() for f in fields]
    placeholders = ", ".join("?" for _ in fields)
    sql = f"INSERT INTO {table_name} ({', '.join(fields)}) VALUES ({placeholders})"
    conn.cursor().execute(sql, values)
    conn.commit()
    print("Record inserted.")

def update_record(conn, table_name):
    display_table_records(conn, table_name)
    row_id = int(input("Row to update: ")) - 1
    field = input("Field to update: ").strip()
    new_value = input("New value: ").strip()
    cursor = conn.cursor()
    cursor.execute(f"SELECT rowid FROM {table_name}")
    rows = cursor.fetchall()
    record_id = rows[row_id][0]
    cursor.execute(f"UPDATE {table_name} SET {field} = ? WHERE rowid = ?", (new_value, record_id))
    conn.commit()

def delete_record(conn, table_name):
    display_table_records(conn, table_name)
    row_id = int(input("Row to delete: ")) - 1
    cursor = conn.cursor()
    cursor.execute(f"SELECT rowid FROM {table_name}")
    rows = cursor.fetchall()
    record_id = rows[row_id][0]
    cursor.execute(f"DELETE FROM {table_name} WHERE rowid = ?", (record_id,))
    conn.commit()

This script enables full interaction with SQLite content through the terminal. It's designed to be intuitive, modular, and safe for educational experimentation.