ecommerce.py

# Creating and Seeding ecommerce DB

import sqlite3

conn = sqlite3.connect('data/ecommerce.sqlite')
c = conn.cursor()

# Create customers table
c.execute("""
    CREATE TABLE customers(
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        first_name VARCHAR(20),
        last_name VARCHAR(20)
    )
""")
conn.commit()

# Create orders table
c.execute("""
    CREATE TABLE orders(
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        ordered_at TEXT,
        customer_id INTEGER,
        amount NUMERIC,
        FOREIGN KEY(customer_id) REFERENCES customers(id)
    )
""")
conn.commit()

# Seed
CUSTOMERS = [
    ('John', 'Doe'),
    ('Eric', 'Camron'),
    ('Emma', 'Dunstan'),
    ('Tobin', 'Holis'),
    ('Nyla', 'Carlin')
]
for customer in CUSTOMERS:
    c.execute("INSERT INTO customers (first_name, last_name) VALUES(?, ?)", customer)
    conn.commit()

ORDERS = [
    ('2019-01-01', 42.01, 1),
    ('2019-01-01', 12.14, 2),
    ('2019-01-02', 15.08, 1),
    ('2019-01-03', 123, 1),
    ('2019-01-04', 25.67, 3),
    ('2019-01-05', 76.12, 3),
    ('2019-01-09', 12.98, 1)
]
for order in ORDERS:
    c.execute("INSERT INTO orders (ordered_at, amount, customer_id) VALUES(?, ?, ?)", order)
    conn.commit()

conn.close()