Spaces:
Sleeping
Sleeping
Download backend/app/database.py from tryprogram/offer-puplayer: direct link, hf CLI and curl.
- Browser
- Download file 4.68 kB
-
https://huggingface.co/spaces/tryprogram/offer-puplayer/resolve/main/backend/app/database.py
- Command line
-
hf download hf://spaces/tryprogram/offer-puplayer/backend/app/database.py
-
curl -L -o database.py https://huggingface.co/spaces/tryprogram/offer-puplayer/resolve/main/backend/app/database.py
4.68 kB
| import sqlite3 | |
| import json | |
| import os | |
| import logging | |
| logger = logging.getLogger(__name__) | |
| DB_PATH = os.path.join(os.path.dirname(os.path.dirname(os.path.abspath(__file__))), 'data', 'offer.db') | |
| def get_conn(): | |
| conn = sqlite3.connect(DB_PATH) | |
| conn.row_factory = sqlite3.Row | |
| return conn | |
| def init_db(): | |
| conn = get_conn() | |
| conn.executescript(""" | |
| CREATE TABLE IF NOT EXISTS users ( | |
| id INTEGER PRIMARY KEY AUTOINCREMENT, | |
| name TEXT DEFAULT '', | |
| school TEXT DEFAULT '', | |
| major TEXT DEFAULT '', | |
| grade TEXT DEFAULT '', | |
| skills TEXT DEFAULT '[]', | |
| projects TEXT DEFAULT '[]', | |
| summary TEXT DEFAULT '', | |
| experience TEXT DEFAULT '', | |
| education TEXT DEFAULT '', | |
| created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP | |
| ); | |
| CREATE TABLE IF NOT EXISTS jobs ( | |
| id INTEGER PRIMARY KEY AUTOINCREMENT, | |
| job_id INTEGER UNIQUE, | |
| name TEXT NOT NULL, | |
| company TEXT DEFAULT '', | |
| city TEXT DEFAULT '', | |
| salary TEXT DEFAULT '', | |
| tags TEXT DEFAULT '[]', | |
| description TEXT DEFAULT '', | |
| requirements TEXT DEFAULT '', | |
| expectation_signals TEXT DEFAULT '[]', | |
| embedding TEXT DEFAULT '', | |
| created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP | |
| ); | |
| CREATE TABLE IF NOT EXISTS diagnoses ( | |
| id INTEGER PRIMARY KEY AUTOINCREMENT, | |
| user_id INTEGER REFERENCES users(id), | |
| job_id INTEGER REFERENCES jobs(job_id), | |
| match_report TEXT DEFAULT '{}', | |
| advice TEXT DEFAULT '{}', | |
| created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP | |
| ); | |
| """) | |
| count = conn.execute("SELECT COUNT(*) FROM jobs").fetchone()[0] | |
| if count == 0: | |
| jobs_path = os.path.join(os.path.dirname(DB_PATH), 'jobs.json') | |
| jobs = json.load(open(jobs_path, encoding='utf-8')) | |
| for j in jobs: | |
| conn.execute( | |
| "INSERT INTO jobs (job_id, name, company, city, salary, tags, description, requirements, expectation_signals) VALUES (?,?,?,?,?,?,?,?,?)", | |
| (int(j['id'].split('_')[-1]) if isinstance(j['id'], str) else j['id'], | |
| j['name'], j.get('company',''), j.get('city',''), j.get('salary',''), | |
| json.dumps(j.get('tags',[]), ensure_ascii=False), | |
| j.get('description',''), j.get('requirements',''), | |
| json.dumps(j.get('expectation_signals',[]), ensure_ascii=False)) | |
| ) | |
| conn.commit() | |
| _compute_job_embeddings(conn) | |
| def _compute_job_embeddings(conn): | |
| try: | |
| from app.services.embedding_service import compute_embedding | |
| rows = conn.execute("SELECT job_id, name, description, requirements, tags FROM jobs").fetchall() | |
| for r in rows: | |
| text = " ".join([ | |
| r["name"] or "", | |
| r["description"] or "", | |
| r["requirements"] or "", | |
| " ".join(json.loads(r["tags"] or "[]")), | |
| ]) | |
| vec = compute_embedding(text) | |
| conn.execute( | |
| "UPDATE jobs SET embedding=? WHERE job_id=?", | |
| (json.dumps(vec, ensure_ascii=False), r["job_id"]) | |
| ) | |
| conn.commit() | |
| logger.info("Computed embeddings for %d jobs", len(rows)) | |
| except Exception as e: | |
| logger.warning("Failed to compute job embeddings: %s", e) | |
| count = conn.execute("SELECT COUNT(*) FROM users").fetchone()[0] | |
| if count == 0: | |
| conn.execute( | |
| "INSERT INTO users (name, school, major, grade, skills, projects, summary) VALUES (?,?,?,?,?,?,?)", | |
| ('张三', '华南理工大学', '计算机科学与技术', '研二', | |
| json.dumps(['Python','Java','SQL','PyTorch','Flask'], ensure_ascii=False), | |
| json.dumps(['基于协同过滤的推荐系统','在线商城后端开发'], ensure_ascii=False), | |
| '技术栈集中在Python/Java后端开发,有推荐系统和Web开发项目经验') | |
| ) | |
| conn.commit() | |
| conn.close() | |
| def migrate_db(): | |
| conn = get_conn() | |
| try: | |
| conn.execute("SELECT embedding FROM jobs LIMIT 1") | |
| except: | |
| conn.execute("ALTER TABLE jobs ADD COLUMN embedding TEXT DEFAULT ''") | |
| conn.commit() | |
| empty = conn.execute("SELECT COUNT(*) FROM jobs WHERE embedding IS NULL OR embedding = ''").fetchone()[0] | |
| if empty > 0: | |
| try: | |
| _compute_job_embeddings(conn) | |
| except Exception as e: | |
| logger.warning("Embedding computation failed: %s", e) | |
| conn.close() | |