Spaces:
Sleeping
Sleeping
| 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/CSV', 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() | |