Spaces:
Sleeping
Sleeping
File size: 4,933 Bytes
bc620e9 |
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 |
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()
|