Spaces:
Runtime error
Runtime error
File size: 1,790 Bytes
77e09a2 | 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 | 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")
|