Scribd
Upload
3 views3 pages

Populate Database Script

The document contains a Python script that populates a SQLite database using data from three CSV spreadsheets. It processes the first spreadsheet to insert product information and merges the second and third spreadsheets based on 'shipping_id' to create shipment-product mappings. The script then inserts shipment details and product details into the database before committing the changes and closing the connection.

Uploaded by

Krushna Hari Mahajan
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
3 views3 pages

Populate Database Script

The document contains a Python script that populates a SQLite database using data from three CSV spreadsheets. It processes the first spreadsheet to insert product information and merges the second and third spreadsheets based on 'shipping_id' to create shipment-product mappings. The script then inserts shipment details and product details into the database before committing the changes and closing the connection.

Uploaded by

Krushna Hari Mahajan
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

import sqlite3

import pandas as pd

# Paths to the provided spreadsheets and database

SPREADSHEET_0 = "spreadsheet_0.csv"

SPREADSHEET_1 = "spreadsheet_1.csv"

SPREADSHEET_2 = "spreadsheet_2.csv"

DATABASE = "[Link]"

def populate_database():

# Connect to the SQLite database

conn = [Link](DATABASE)

cursor = [Link]()

# Process Spreadsheet 0

print("Processing Spreadsheet 0...")

df0 = pd.read_csv(SPREADSHEET_0)

df0.to_sql("Products", conn, if_exists="append", index=False)

# Process Spreadsheet 1 and 2

print("Processing Spreadsheet 1 and 2...")

df1 = pd.read_csv(SPREADSHEET_1)

df2 = pd.read_csv(SPREADSHEET_2)

# Merge Spreadsheet 1 and 2 based on 'shipping_id'

merged = [Link](df1, df2, on="shipping_id")


# Create a shipment-product mapping and insert into the database

for _, row in [Link]():

shipment_id = row['shipping_id']

origin = row['origin']

destination = row['destination']

product_id = row['product_id']

quantity = row['quantity']

# Insert shipment details

[Link]("""

INSERT INTO Shipments (ShipmentID, Origin, Destination)

VALUES (?, ?, ?)

""", (shipment_id, origin, destination))

# Insert product details for the shipment

[Link]("""

INSERT INTO ShipmentProducts (ShipmentID, ProductID, Quantity)

VALUES (?, ?, ?)

""", (shipment_id, product_id, quantity))

# Commit changes and close the connection

[Link]()

[Link]()

print("Database populated successfully.")

if __name__ == "__main__":
populate_database()

You might also like