Spaces:
Runtime error
Runtime error
| import os | |
| import pandas as pd | |
| import sqlite3 | |
| from pathlib import Path | |
| BASE_DIR = Path(__file__).resolve().parent | |
| DATA_DIR = BASE_DIR / "data" | |
| DATA_DIR.mkdir(exist_ok=True) | |
| excel_path = DATA_DIR / "TaskbookExport_20251001_050840202.xlsx" | |
| db_path = DATA_DIR / "nasa_data.db" | |
| if not excel_path.exists(): | |
| raise FileNotFoundError(f"excel file not found at: {excel_path}\n" | |
| f"uploaded it or placed it in the /data folder") | |
| conn = sqlite3.connect(db_path) | |
| print(f"connected to SQLite DB: {db_path}") | |
| df = pd.read_excel(excel_path) | |
| print(f"total records loaded: {len(df)}") | |
| print(f"shape: {df.shape}") | |
| # print(df.columns.tolist()) | |
| cols_to_keep = [ | |
| "Task_id", | |
| "Project Title", | |
| "Task Abstract/Description", | |
| "Research Impact/Earth Benefit", | |
| "Fiscal Year", | |
| "PI First Name", | |
| "PI Last Name" | |
| ] | |
| missing = [c for c in cols_to_keep if c not in df.columns] | |
| if missing: | |
| raise ValueError(f"missing expected columns in Excel: {missing}") | |
| copy_df = df[cols_to_keep].copy() | |
| #fname lname to name | |
| copy_df["PI_Name"] = copy_df["PI First Name"].fillna("") + " " + copy_df["PI Last Name"].fillna("") | |
| copy_df.drop(["PI First Name", "PI Last Name"], axis=1, inplace=True) | |
| print("sample cleaned rows:") | |
| print(copy_df.head(3)) | |
| copy_df.to_sql("projects", conn, if_exists="replace", index=False) | |
| print("'projects' table stored in DB") | |
| cursor = conn.cursor() | |
| cursor.execute("SELECT COUNT(*) FROM projects") | |
| total_rows = cursor.fetchone()[0] | |
| print(f"total projects in DB: {total_rows}") | |
| cursor.execute("SELECT * FROM projects LIMIT 5") | |
| for row in cursor.fetchall(): | |
| print("--->", row) | |
| cursor.execute("SELECT name FROM sqlite_master WHERE type='table';") | |
| print("tables in db:", cursor.fetchall()) | |
| conn.close() | |
| print("db connection closed") | |