Scenario

A product catalog needs to be synchronized with updates from a CSV file. New products must be inserted and existing products with price changes must be updated in the database.

Task

Write a Python script at /home/interview/sync_catalog.py that reads /home/interview/catalog_updates.csv, compares it against the SQLite database at /home/interview/products.db, inserts new products, and updates prices for existing products that have changed. Use a transaction to ensure all changes are applied atomically.

Note: The database has a table named products with columns: id (INTEGER PRIMARY KEY), name (TEXT), price (REAL).

Example

# Database before sync
id=1, name="Product A", price=10.99

# CSV contains
1,Product A,12.99  (price changed)
2,Product B,15.99  (new product)

# Database after sync
id=1, name="Product A", price=12.99  (updated)
id=2, name="Product B", price=15.99  (inserted)

Step 1: Examine the database and CSV

sqlite3 /home/interview/products.db "SELECT COUNT(*) FROM products;"
head /home/interview/catalog_updates.csv

Shows initial product count and CSV structure.

Step 2: Create the Python script

nano /home/interview/sync_catalog.py

Write a script that syncs the catalog using transactions:

import sqlite3
import csv

# Connect to database
conn = sqlite3.connect('/home/interview/products.db')
cursor = conn.cursor()

# Read CSV file
csv_products = {}
with open('/home/interview/catalog_updates.csv', 'r') as csvfile:
    reader = csv.DictReader(csvfile)
    for row in reader:
        csv_products[int(row['id'])] = {
            'name': row['name'],
            'price': float(row['price'])
        }

# Get existing products from database
cursor.execute('SELECT id, price FROM products')
db_products = {row[0]: row[1] for row in cursor.fetchall()}

insert_count = 0
update_count = 0

try:
    # Start transaction
    for product_id, product_data in csv_products.items():
        if product_id not in db_products:
            # Insert new product
            cursor.execute('INSERT INTO products (id, name, price) VALUES (?, ?, ?)',
                         (product_id, product_data['name'], product_data['price']))
            insert_count += 1
        elif db_products[product_id] != product_data['price']:
            # Update price if changed
            cursor.execute('UPDATE products SET price = ? WHERE id = ?',
                         (product_data['price'], product_id))
            update_count += 1
    
    # Commit transaction
    conn.commit()
    print(f"Sync completed: {insert_count} inserted, {update_count} updated")
    
except Exception as e:
    # Rollback on error
    conn.rollback()
    print(f"Sync failed: {e}")
    
finally:
    conn.close()

Step 3: Run the script

python3 /home/interview/sync_catalog.py

Step 4: Verify the results

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

Should show 150 products total (100 original + 50 new).