Spaces:
Sleeping
Sleeping
File size: 4,680 Bytes
61fd574 | 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 | 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()
|