File size: 3,781 Bytes
9d36116 2cfa049 9d36116 2cfa049 9d36116 8fb661f 9d36116 2cfa049 9d36116 2cfa049 9d36116 2cfa049 9d36116 2cfa049 9d36116 2cfa049 9d36116 2cfa049 9d36116 2cfa049 9d36116 2cfa049 9d36116 2cfa049 9d36116 2cfa049 9d36116 2cfa049 9d36116 2cfa049 | 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 | import sqlite3
import os
DB_PATH = 'mindgap.db'
def init_db():
"""
Initialize the SQLite database with required tables:
- student_performance: stores quiz results
- weak_topics: tracks topics the student struggles with
- achievements: stores unlocked badges/achievements
"""
conn = sqlite3.connect(DB_PATH)
cursor = conn.cursor()
# Performance history table
cursor.execute('''
CREATE TABLE IF NOT EXISTS student_performance (
id INTEGER PRIMARY KEY AUTOINCREMENT,
topic TEXT NOT NULL,
score INTEGER,
total_questions INTEGER,
level TEXT,
timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
)
''')
# Weak topics tracking
cursor.execute('''
CREATE TABLE IF NOT EXISTS weak_topics (
id INTEGER PRIMARY KEY AUTOINCREMENT,
topic TEXT UNIQUE NOT NULL,
frequency INTEGER DEFAULT 1,
last_failed_at DATETIME DEFAULT CURRENT_TIMESTAMP
)
''')
# Achievements / gamification badges
cursor.execute('''
CREATE TABLE IF NOT EXISTS achievements (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
)
''')
conn.commit()
conn.close()
def save_score(topic, score, total, level):
"""
Save quiz result and update weak topics if performance is low (<70%)
"""
conn = sqlite3.connect(DB_PATH)
cursor = conn.cursor()
cursor.execute('''
INSERT INTO student_performance
(topic, score, total_questions, level)
VALUES (?, ?, ?, ?)
''', (topic, score, total, level))
# Mark as weak topic if score is below 70%
if total > 0 and (score / total) < 0.7:
cursor.execute('''
INSERT INTO weak_topics (topic)
VALUES (?)
ON CONFLICT(topic)
DO UPDATE SET
frequency = frequency + 1,
last_failed_at = CURRENT_TIMESTAMP
''', (topic,))
conn.commit()
conn.close()
def save_achievement(name):
"""
Award an achievement/badge to the student
"""
conn = sqlite3.connect(DB_PATH)
cursor = conn.cursor()
cursor.execute('INSERT INTO achievements (name) VALUES (?)', (name,))
conn.commit()
conn.close()
def get_weak_topics():
"""
Return list of topics the student has repeatedly struggled with
"""
conn = sqlite3.connect(DB_PATH)
cursor = conn.cursor()
cursor.execute('''
SELECT topic, frequency
FROM weak_topics
ORDER BY frequency DESC
LIMIT 10
''')
topics = cursor.fetchall()
conn.close()
return [{"topic": t[0], "frequency": t[1]} for t in topics]
def get_performance_history():
"""
Return recent quiz performance history (newest first)
"""
conn = sqlite3.connect(DB_PATH)
cursor = conn.cursor()
cursor.execute('''
SELECT topic, score, total_questions, level, timestamp
FROM student_performance
ORDER BY timestamp DESC
''')
history = cursor.fetchall()
conn.close()
return [
{
"topic": h[0],
"score": h[1],
"total": h[2],
"level": h[3],
"date": h[4]
}
for h in history
]
def get_achievements():
"""
Return list of earned achievements (newest first)
"""
conn = sqlite3.connect(DB_PATH)
cursor = conn.cursor()
cursor.execute('SELECT name, timestamp FROM achievements ORDER BY timestamp DESC')
rows = cursor.fetchall()
conn.close()
return [{"name": row[0], "date": row[1]} for row in rows]
|