Scenario

A REST API provides product inventory data in JSON format with nested structure and pagination. You need to fetch all data, transform it into a flat structure, and store it in a database.

Task

Write a Python script at /home/interview/fetch_inventory.py that fetches all paginated product data from http://api.inventory.local/products, flattens the nested JSON structure, creates a SQLite database table named products, and inserts all records into /home/interview/inventory.db.

The table should contain the following columns: id, name, category, price, stock_quantity, supplier_name.

Example

API response structure (nested):

{
  "page": 1,
  "total_pages": 4,
  "next": "http://api.inventory.local/products?page=2",
  "products": [
    {
      "id": 1,
      "name": "Laptop",
      "details": {
        "category": "Electronics",
        "price": 999.99,
        "stock_quantity": 25
      },
      "supplier": {
        "name": "Tech Supply Co",
        "country": "USA"
      }
    }
  ]
}

Expected database record (flattened):

id=1, name="Laptop", category="Electronics", price=999.99, stock_quantity=25, supplier_name="Tech Supply Co"

Step 1: Test the API endpoint

curl http://api.inventory.local/products

Review the JSON structure to understand pagination and nested data format.

Step 2: Create the Python script

nano /home/interview/fetch_inventory.py

Write a script that fetches all pages, flattens the data, and inserts into SQLite:

import requests
import sqlite3

# Connect to SQLite database (creates if doesn't exist)
conn = sqlite3.connect('/home/interview/inventory.db')
cursor = conn.cursor()

# Create products table
cursor.execute('''
    CREATE TABLE IF NOT EXISTS products (
        id INTEGER PRIMARY KEY,
        name TEXT,
        category TEXT,
        price REAL,
        stock_quantity INTEGER,
        supplier_name TEXT
    )
''')

# Fetch all pages using next URL
url = 'http://api.inventory.local/products?page=1'
total_inserted = 0

while url:
    response = requests.get(url)
    data = response.json()
    
    # Extract and flatten products
    for product in data['products']:
        cursor.execute('''
            INSERT INTO products (id, name, category, price, stock_quantity, supplier_name)
            VALUES (?, ?, ?, ?, ?, ?)
        ''', (
            product['id'],
            product['name'],
            product['details']['category'],
            product['details']['price'],
            product['details']['stock_quantity'],
            product['supplier']['name']
        ))
        total_inserted += 1
    
    # Get next page URL
    url = data.get('next')

# Commit and close
conn.commit()
conn.close()

print(f"Inserted {total_inserted} products into database")

Step 3: Run the script

python3 /home/interview/fetch_inventory.py

Step 4: Verify the database

sqlite3 /home/interview/inventory.db "SELECT COUNT(*) FROM products;"
sqlite3 /home/interview/inventory.db "SELECT * FROM products LIMIT 5;"

Should show 200 total records with flattened structure.