Database initialization

The workshop includes a db_init.py script that loads the CSV data into SQLite. Let's understand how this works.

CSV to SQLite pipeline

How raw CSV data becomes queryable database tables

db_init.py
python
import sqlite3
import pandas as pd
import os

DB_PATH = "ecommerce.db"
DATA_DIR = "data"

def init_database():
    """Initialize the SQLite database from CSV files."""
    # Remove existing database
    if os.path.exists(DB_PATH):
        os.remove(DB_PATH)

    conn = sqlite3.connect(DB_PATH)

    # Load each CSV file into a table
    csv_files = [
        "olist_customers_dataset.csv",
        "olist_orders_dataset.csv",
        "olist_order_items_dataset.csv",
        "olist_order_payments_dataset.csv",
        "olist_order_reviews_dataset.csv",
        "olist_products_dataset.csv",
        "olist_sellers_dataset.csv",
        "olist_geolocation_dataset.csv",
        "product_category_name_translation.csv"
    ]

    for csv_file in csv_files:
        table_name = csv_file.replace("olist_", "").replace("_dataset", "").replace(".csv", "")
        df = pd.read_csv(os.path.join(DATA_DIR, csv_file))
        df.to_sql(table_name, conn, index=False, if_exists="replace")
        print(f"Loaded {len(df)} rows into {table_name}")

    conn.close()
    print(f"Database initialized: {DB_PATH}")

if __name__ == "__main__":
    init_database()

The database initialization script loads CSV files into SQLite tables.

It strips the "olist_" prefix and "_dataset.csv" suffix. So "olist_orders_dataset.csv" becomes the "orders" table. This gives us clean, readable table names that the SQL Agent can reference easily.

Run this script once to create your ecommerce.db file: uv run python db_init.py

Validation checklist: Database setup checklist

Loading practice…

Timed quiz: Quick review

Loading practice…

Your database is ready with real e-commerce data. The knowledge base is complete.

Validation checklist: Database knowledge checklist

Loading practice…