import pandas as pd
import sqlite3
# Connect to the SQLite database
conn = sqlite3.connect('walmart.db')
cursor = conn.cursor()
# Step 1: Load the spreadsheets
spreadsheet0 = pd.read_excel('spreadsheet0.xlsx')
spreadsheet1 = pd.read_excel('spreadsheet1.xlsx')
spreadsheet2 = pd.read_excel('spreadsheet2.xlsx')
# Step 2: Insert Spreadsheet 0 data into the database
def insert_spreadsheet0():
for index, row in spreadsheet0.iterrows():
cursor.execute("""
INSERT INTO products (product_name, manufacturer, weight, flavor,
target_health_condition)
VALUES (?, ?, ?, ?, ?)
""", (row['name'], row['manufacturer'], row['weight'], row['flavor'],
row['target_health_condition']))
conn.commit()
# Step 3: Combine data from Spreadsheet 1 and 2
def process_spreadsheet1_and_2():
# Merge spreadsheet1 and spreadsheet2 based on the shipping identifier
merged_data = pd.merge(spreadsheet1, spreadsheet2, on='shipping_id')
for index, row in merged_data.iterrows():
# Calculate quantity and prepare for insertion
quantity = row['quantity']
# Insert shipment data
cursor.execute("""
INSERT INTO shipments (product_name, origin, destination, quantity)
VALUES (?, ?, ?, ?)
""", (row['product_name'], row['origin'], row['destination'], quantity))
conn.commit()
# Step 4: Execute the functions
insert_spreadsheet0()
process_spreadsheet1_and_2()
# Close the database connection
conn.close()
print("Database population completed successfully.")
