Scenario

A SQLite database contains product data that needs to be exported for analysis. You need to query the database and save the results to a CSV file.

Task

Complete the Python script at /home/interview/export_products.py to execute a SELECT query on the products table in /home/interview/products.db, fetch all results, and export them to /home/interview/products_export.csv.

Database Schema

Table: products

• id (INTEGER)
• name (TEXT)
• category (TEXT)
• price (REAL)
• stock (INTEGER)

Step 1: Examine the database structure

sqlite3 /home/interview/products.db "SELECT * FROM products LIMIT 5;"

Review the table structure and sample data.

Step 2: Complete the Python script

nano /home/interview/export_products.py

Replace the TODO comments with working code:

import sqlite3
import csv

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

# Execute SELECT query to fetch all products
cursor.execute('SELECT * FROM products')

# Fetch all results
results = cursor.fetchall()

# Write results to CSV file
with open('/home/interview/products_export.csv', 'w', newline='') as csvfile:
    writer = csv.writer(csvfile)
    
    # Write header
    writer.writerow(['id', 'name', 'category', 'price', 'stock'])
    
    # Write data rows
    writer.writerows(results)

# Close the database connection
conn.close()

print(f"Exported {len(results)} products to CSV")

Step 3: Run the script

python3 /home/interview/export_products.py

Step 4: Verify the output

head /home/interview/products_export.csv
wc -l /home/interview/products_export.csv

Should show 201 lines (1 header + 200 data rows).