Spaces:
Sleeping
Sleeping
File size: 7,126 Bytes
f61f637 e71effe f61f637 ec25389 f61f637 ec25389 f61f637 ec25389 f61f637 ec25389 f61f637 ec25389 f61f637 ec25389 f61f637 ec25389 f61f637 ec25389 f61f637 ec25389 e71effe f61f637 ec25389 f61f637 ec25389 f61f637 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 |
import sqlite3
import pandas as pd
from datasets import load_dataset
def create_connection():
""" Create a database connection to the SQLite database """
try:
conn = sqlite3.connect('bike_store.db')
print("Connection established: Database is connected")
return conn
except Exception as e:
print("Error connecting to database:", e)
return None
def create_table(conn, create_table_sql):
""" Create a table from the create_table_sql statement """
try:
c = conn.cursor()
c.execute(create_table_sql)
conn.commit()
print("Table created successfully or already exists.")
except Exception as e:
print("Error creating table:", e)
def import_data_to_table(csv_file, table_name, conn):
""" Load data from a CSV file and insert it into the specified table """
try:
df = pd.read_csv(csv_file)
print(df)
df.to_sql(table_name, conn, if_exists='replace', index=False)
print(f"Data imported successfully into {table_name}.")
except Exception as e:
print(f"Error importing data into {table_name}: {e}")
def main():
# Create a database connection
conn = create_connection()
print(conn)
if conn is not None:
# SQL table creation statements
tables_sql = {
"customers": """ CREATE TABLE IF NOT EXISTS customers (
customer_id INTEGER PRIMARY KEY AUTOINCREMENT,
first_name TEXT NOT NULL,
last_name TEXT NOT NULL,
phone TEXT,
email TEXT,
street TEXT,
city TEXT,
state TEXT,
zip_code INTEGER
); """,
"staffs": """ CREATE TABLE IF NOT EXISTS staffs (
staff_id INTEGER PRIMARY KEY AUTOINCREMENT,
first_name TEXT NOT NULL,
last_name TEXT NOT NULL,
email TEXT,
phone TEXT,
active INTEGER,
store_id INTEGER,
manager_id INTEGER,
FOREIGN KEY (store_id) REFERENCES stores(store_id),
FOREIGN KEY (manager_id) REFERENCES staffs(staff_id)
); """,
"stores": """ CREATE TABLE IF NOT EXISTS stores (
store_id INTEGER PRIMARY KEY AUTOINCREMENT,
store_name TEXT NOT NULL,
phone TEXT,
email TEXT,
street TEXT,
city TEXT,
state TEXT,
zip_code TEXT
); """,
"categories": """ CREATE TABLE IF NOT EXISTS categories (
category_id INTEGER PRIMARY KEY AUTOINCREMENT,
category_name TEXT NOT NULL
); """,
"products": """ CREATE TABLE IF NOT EXISTS products (
product_id INTEGER PRIMARY KEY AUTOINCREMENT,
product_name TEXT NOT NULL,
category_id INTEGER,
brand_id INTEGER,
model_year INTEGER,
list_price REAL,
FOREIGN KEY (category_id) REFERENCES categories(category_id),
FOREIGN KEY (brand_id) REFERENCES brands(brand_id)
); """,
"brands": """ CREATE TABLE IF NOT EXISTS brands (
brand_id INTEGER PRIMARY KEY AUTOINCREMENT,
brand_name TEXT NOT NULL
); """,
"stocks": """ CREATE TABLE IF NOT EXISTS stocks (
store_id INTEGER,
product_id INTEGER,
quantity INTEGER,
PRIMARY KEY (store_id, product_id),
FOREIGN KEY (store_id) REFERENCES stores(store_id),
FOREIGN KEY (product_id) REFERENCES products(product_id)
); """,
"orders": """ CREATE TABLE IF NOT EXISTS orders (
order_id INTEGER PRIMARY KEY AUTOINCREMENT,
customer_id INTEGER,
order_status TEXT,
order_date TEXT,
required_date TEXT,
shipped_date TEXT,
store_id INTEGER,
staff_id INTEGER,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
FOREIGN KEY (store_id) REFERENCES stores(store_id),
FOREIGN KEY (staff_id) REFERENCES staffs(staff_id)
); """,
"order_items": """ CREATE TABLE IF NOT EXISTS order_items (
order_id INTEGER,
item_id INTEGER PRIMARY KEY AUTOINCREMENT,
product_id INTEGER,
quantity INTEGER,
list_price REAL,
discount REAL,
FOREIGN KEY (order_id) REFERENCES orders(order_id),
FOREIGN KEY (product_id) REFERENCES products(product_id)
); """
}
# Create tables
for table_name, sql_command in tables_sql.items():
create_table(conn, sql_command)
# Data import paths
data_paths = {
"customers": "CSV/customers.csv",
"staffs": "CSV/staffs.csv",
"products": "CSV/products.csv",
"categories": "CSV/categories.csv",
"stores": "CSV/stores.csv",
"brands": "CSV/brands.csv",
"stocks": "CSV/stocks.csv",
"orders": "CSV/orders.csv",
"order_items": "CSV/order_items.csv",
}
dataset = load_dataset('VicGerardoPR/InteractiveDatabseApp', data_files=data_paths)
# Import data to tables
for table_name, csv_path in data_paths.items():
import_data_to_table(csv_path, table_name, conn)
# Close the connection
conn.close()
else:
print("Failed to create database connection.")
if __name__ == '__main__':
main()
|