Scribd
Upload
2 views3 pages

Populate SQLite Database

This Python script connects to an SQLite database and populates it with data from three CSV spreadsheets. It defines functions to insert product data from the first spreadsheet and shipment data from the second and third spreadsheets into the database. The script concludes by closing the database connection after the data insertion is complete.

Uploaded by

bnduniversal1
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
2 views3 pages

Populate SQLite Database

This Python script connects to an SQLite database and populates it with data from three CSV spreadsheets. It defines functions to insert product data from the first spreadsheet and shipment data from the second and third spreadsheets into the database. The script concludes by closing the database connection after the data insertion is complete.

Uploaded by

bnduniversal1
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 3

Python Script to Populate SQLite Database

import sqlite3

import pandas as pd

# Database setup

db_path = "walmart_shipping.db"

conn = sqlite3.connect(db_path)

cursor = conn.cursor()

def insert_spreadsheet_0(spreadsheet_path):

"""Insert data from spreadsheet 0 directly into the database."""

df = pd.read_csv(spreadsheet_path)

for _, row in df.iterrows():

cursor.execute(

"INSERT INTO products (product_name, manufacturer, weight, flavor,

target_health_condition) VALUES (?, ?, ?, ?, ?)",

(row['Name'], row['Manufacturer'], row['Weight'], row['Flavor'],

row['HealthCondition'])

conn.commit()

def insert_spreadsheet_1_and_2(spreadsheet_1_path, spreadsheet_2_path):

"""Process data from spreadsheets 1 and 2 and insert into the database."""

df1 = pd.read_csv(spreadsheet_1_path)

df2 = pd.read_csv(spreadsheet_2_path)
shipments = {}

for _, row in df2.iterrows():

shipments[row['ShippingID']] = (row['Origin'], row['Destination'])

for _, row in df1.iterrows():

shipping_id = row['ShippingID']

product = row['Product']

quantity = row['Quantity']

origin, destination = shipments[shipping_id]

cursor.execute(

"INSERT INTO shipments (origin, destination) VALUES (?, ?)",

(origin, destination)

shipment_id = cursor.lastrowid

cursor.execute(

"INSERT INTO shipment_products (shipment_id, product_name, quantity) VALUES

(?, ?, ?)",

(shipment_id, product, quantity)

conn.commit()

# Paths to spreadsheets

spreadsheet_0_path = "spreadsheet_0.csv"

spreadsheet_1_path = "spreadsheet_1.csv"

spreadsheet_2_path = "spreadsheet_2.csv"
# Insert data into the database

insert_spreadsheet_0(spreadsheet_0_path)

insert_spreadsheet_1_and_2(spreadsheet_1_path, spreadsheet_2_path)

# Close the database connection

conn.close()

You might also like