Spaces:
Sleeping
Sleeping
| import os | |
| import json | |
| import sqlite3 | |
| # --- Configuration --- | |
| # Ensures the DB is created inside the 'backend' folder | |
| DATABASE_FILE = os.path.join("backend", "portfolio.sqlite") | |
| DATA_DIR = "data" | |
| def create_connection(): | |
| # Ensure the backend directory exists | |
| os.makedirs(os.path.dirname(DATABASE_FILE), exist_ok=True) | |
| return sqlite3.connect(DATABASE_FILE) | |
| def setup_database(): | |
| conn = create_connection() | |
| cursor = conn.cursor() | |
| print("🛠️ Building Modular Database...") | |
| # --- 1. Create Tables --- | |
| # Projects | |
| cursor.execute('''CREATE TABLE IF NOT EXISTS projects ( | |
| id TEXT PRIMARY KEY, | |
| title TEXT, | |
| type TEXT, | |
| description TEXT, | |
| technologies TEXT, | |
| github_url TEXT, | |
| demo_url TEXT, | |
| image_path TEXT | |
| )''') | |
| # Articles | |
| cursor.execute('''CREATE TABLE IF NOT EXISTS articles ( | |
| title TEXT, description TEXT, url TEXT, type TEXT | |
| )''') | |
| # Videos | |
| cursor.execute('''CREATE TABLE IF NOT EXISTS videos ( | |
| id TEXT, title TEXT, description TEXT, thumbnail_url TEXT, url TEXT | |
| )''') | |
| # Research | |
| cursor.execute('''CREATE TABLE IF NOT EXISTS research ( | |
| title TEXT, description TEXT, url TEXT, type TEXT | |
| )''') | |
| # Skills (Updated for modular JSON with 3 columns) | |
| cursor.execute('''CREATE TABLE IF NOT EXISTS skills ( | |
| id TEXT PRIMARY KEY, | |
| category TEXT, | |
| skill_list TEXT | |
| )''') | |
| # Experience (New Table) | |
| cursor.execute('''CREATE TABLE IF NOT EXISTS experience ( | |
| id TEXT PRIMARY KEY, | |
| role TEXT, | |
| company TEXT, | |
| duration TEXT, | |
| description TEXT | |
| )''') | |
| # Education (New Table) | |
| cursor.execute('''CREATE TABLE IF NOT EXISTS education ( | |
| id TEXT PRIMARY KEY, | |
| degree TEXT, | |
| institution TEXT, | |
| details TEXT | |
| )''') | |
| # Certifications | |
| cursor.execute('''CREATE TABLE IF NOT EXISTS certifications ( | |
| name TEXT PRIMARY KEY | |
| )''') | |
| # --- 2. Generic Insert Function --- | |
| def insert_from_json(filename, table, columns): | |
| filepath = os.path.join(DATA_DIR, filename) | |
| if not os.path.exists(filepath): | |
| print(f" ⚠️ Warning: {filename} not found. Skipping.") | |
| return | |
| with open(filepath, 'r', encoding='utf-8') as f: | |
| data = json.load(f) | |
| count = 0 | |
| for item in data: | |
| # --- FIX: Pre-process lists into strings --- | |
| # 1. Handle 'technologies' list (for projects) | |
| if 'technologies' in item and isinstance(item['technologies'], list): | |
| item['technologies'] = json.dumps(item['technologies']) | |
| # 2. Handle 'list' -> 'skill_list' mapping (for skills) | |
| # We look for the key 'list' (from JSON) and convert it to 'skill_list' (for DB) | |
| if 'list' in item and isinstance(item['list'], list): | |
| item['skill_list'] = json.dumps(item['list']) | |
| # --- FIX: Collect values --- | |
| values = [] | |
| for col in columns: | |
| # If the column is 'skill_list', we expect the data might still be in 'list' | |
| # if we didn't map it above, but we did map it, so we just grab 'skill_list'. | |
| # We use .get() to avoid errors if a field is missing. | |
| values.append(item.get(col)) | |
| placeholders = ",".join(["?"] * len(columns)) | |
| cursor.execute( | |
| f"INSERT OR REPLACE INTO {table} VALUES ({placeholders})", values) | |
| count += 1 | |
| print(f" ✅ Loaded {count} items into '{table}'") | |
| # --- 3. Run Inserts --- | |
| print("📥 Inserting modular data...") | |
| # Projects | |
| insert_from_json("projects.json", "projects", [ | |
| "id", "title", "type", "description", "technologies", "github_url", "demo_url", "image_path"]) | |
| # Standard Content | |
| insert_from_json("articles.json", "articles", [ | |
| "title", "description", "url", "type"]) | |
| insert_from_json("videos.json", "videos", [ | |
| "id", "title", "description", "thumbnail_url", "url"]) | |
| insert_from_json("research.json", "research", [ | |
| "title", "description", "url", "type"]) | |
| # New Modular Files | |
| # Note: 'skill_list' maps to the 'list' key in the json via the fix above | |
| insert_from_json("skills.json", "skills", ["id", "category", "skill_list"]) | |
| insert_from_json("experience.json", "experience", [ | |
| "id", "role", "company", "duration", "description"]) | |
| insert_from_json("education.json", "education", [ | |
| "id", "degree", "institution", "details"]) | |
| insert_from_json("certifications.json", "certifications", ["name"]) | |
| conn.commit() | |
| conn.close() | |
| print(f"🎉 Database updated at {DATABASE_FILE}") | |
| if __name__ == "__main__": | |
| setup_database() | |