bot / setup_olist_db.py
SuhasGholkar's picture
Create setup_olist_db.py
cbbbf95 verified
Raw History Blame Contribute Delete
1.56 kB
import os
import sqlite3
import pandas as pd
import yaml
DB_PATH = "olist.sqlite"
def initialize_db():
if os.path.exists(DB_PATH):
print("✅ DB already exists, skipping rebuild.")
return
conn = sqlite3.connect(DB_PATH)
csv_files = {
"orders": "data/olist_orders_dataset.csv",
"order_items": "data/olist_order_items_dataset.csv",
"order_payments": "data/olist_order_payments_dataset.csv",
"order_reviews": "data/olist_order_reviews_dataset.csv",
"customers": "data/olist_customers_dataset.csv",
"products": "data/olist_products_dataset.csv",
"sellers": "data/olist_sellers_dataset.csv",
"geolocation": "data/olist_geolocation_dataset.csv",
"product_category_name_translation": "data/product_category_name_translation.csv"
}
for table, path in csv_files.items():
df = pd.read_csv(path)
df.to_sql(table, conn, if_exists="replace", index=False)
# Users table
conn.execute("""
CREATE TABLE IF NOT EXISTS users (
user_id TEXT PRIMARY KEY,
email TEXT UNIQUE NOT NULL,
password_hash TEXT NOT NULL,
customer_id TEXT,
role TEXT CHECK(role IN ('user','admin')) NOT NULL
);
""")
conn.execute("INSERT OR IGNORE INTO users VALUES ('U001','alice@example.com','hashed_pw1','CUST12345','user');")
conn.execute("INSERT OR IGNORE INTO users VALUES ('A001','admin@example.com','hashed_pw_admin',NULL,'admin');")
conn.commit()
conn.close()
print("✅ DB initialized")