Spaces:
Runtime error
Runtime error
File size: 14,599 Bytes
cb049d7 |
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 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 |
"""
Database models for ThutoAI School Integration
"""
from datetime import datetime
import sqlite3
import os
class DatabaseManager:
def __init__(self, db_path="thutoai_school.db"):
self.db_path = db_path
self.init_database()
def get_connection(self):
return sqlite3.connect(self.db_path)
def init_database(self):
"""Initialize the database with all required tables"""
conn = self.get_connection()
cursor = conn.cursor()
# Students table
cursor.execute('''
CREATE TABLE IF NOT EXISTS students (
id INTEGER PRIMARY KEY AUTOINCREMENT,
student_id TEXT UNIQUE NOT NULL,
name TEXT NOT NULL,
email TEXT,
grade_level TEXT,
class_section TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
''')
# Announcements table
cursor.execute('''
CREATE TABLE IF NOT EXISTS announcements (
id INTEGER PRIMARY KEY AUTOINCREMENT,
title TEXT NOT NULL,
content TEXT NOT NULL,
priority TEXT DEFAULT 'normal',
target_audience TEXT DEFAULT 'all',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
expires_at TIMESTAMP,
is_active BOOLEAN DEFAULT 1
)
''')
# Examinations table
cursor.execute('''
CREATE TABLE IF NOT EXISTS examinations (
id INTEGER PRIMARY KEY AUTOINCREMENT,
exam_name TEXT NOT NULL,
subject TEXT NOT NULL,
exam_date DATE NOT NULL,
exam_time TIME,
duration_minutes INTEGER,
location TEXT,
grade_level TEXT,
instructions TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
''')
# Tests/Assignments table
cursor.execute('''
CREATE TABLE IF NOT EXISTS tests_assignments (
id INTEGER PRIMARY KEY AUTOINCREMENT,
title TEXT NOT NULL,
subject TEXT NOT NULL,
type TEXT NOT NULL, -- 'test', 'assignment', 'quiz', 'project'
due_date DATE NOT NULL,
due_time TIME,
description TEXT,
grade_level TEXT,
total_marks INTEGER,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
''')
# Grades table
cursor.execute('''
CREATE TABLE IF NOT EXISTS grades (
id INTEGER PRIMARY KEY AUTOINCREMENT,
student_id TEXT NOT NULL,
subject TEXT NOT NULL,
assessment_type TEXT NOT NULL, -- 'test', 'exam', 'assignment', 'quiz'
assessment_name TEXT NOT NULL,
marks_obtained REAL NOT NULL,
total_marks REAL NOT NULL,
percentage REAL NOT NULL,
grade_letter TEXT,
date_recorded DATE NOT NULL,
teacher_comments TEXT,
FOREIGN KEY (student_id) REFERENCES students (student_id)
)
''')
# School Events table
cursor.execute('''
CREATE TABLE IF NOT EXISTS school_events (
id INTEGER PRIMARY KEY AUTOINCREMENT,
event_name TEXT NOT NULL,
event_date DATE NOT NULL,
event_time TIME,
location TEXT,
description TEXT,
event_type TEXT, -- 'holiday', 'sports', 'cultural', 'academic'
is_holiday BOOLEAN DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
''')
# Admin Users table
cursor.execute('''
CREATE TABLE IF NOT EXISTS admin_users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
username TEXT UNIQUE NOT NULL,
password_hash TEXT NOT NULL,
full_name TEXT NOT NULL,
email TEXT,
role TEXT DEFAULT 'admin', -- 'admin', 'teacher', 'principal'
is_active BOOLEAN DEFAULT 1,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
last_login TIMESTAMP
)
''')
# Syllabus table
cursor.execute('''
CREATE TABLE IF NOT EXISTS syllabus (
id INTEGER PRIMARY KEY AUTOINCREMENT,
subject TEXT NOT NULL,
grade_level TEXT NOT NULL,
chapter_number INTEGER,
chapter_title TEXT NOT NULL,
topics TEXT NOT NULL, -- JSON array of topics
learning_objectives TEXT,
duration_weeks INTEGER,
resources TEXT, -- JSON array of resources/books
assessment_methods TEXT,
created_by TEXT NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
is_active BOOLEAN DEFAULT 1
)
''')
# Timetable table
cursor.execute('''
CREATE TABLE IF NOT EXISTS timetable (
id INTEGER PRIMARY KEY AUTOINCREMENT,
class_section TEXT NOT NULL, -- e.g., "10-A", "11-B"
day_of_week TEXT NOT NULL, -- 'Monday', 'Tuesday', etc.
period_number INTEGER NOT NULL,
start_time TIME NOT NULL,
end_time TIME NOT NULL,
subject TEXT NOT NULL,
teacher_name TEXT,
room_number TEXT,
created_by TEXT NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
is_active BOOLEAN DEFAULT 1
)
''')
# File Uploads table (for syllabus documents, etc.)
cursor.execute('''
CREATE TABLE IF NOT EXISTS file_uploads (
id INTEGER PRIMARY KEY AUTOINCREMENT,
filename TEXT NOT NULL,
original_filename TEXT NOT NULL,
file_path TEXT NOT NULL,
file_type TEXT NOT NULL, -- 'syllabus', 'timetable', 'announcement'
file_size INTEGER,
uploaded_by TEXT NOT NULL,
related_id INTEGER, -- ID of related record (syllabus, announcement, etc.)
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
''')
conn.commit()
conn.close()
print("Database initialized successfully!")
def add_sample_data(self):
"""Add sample data for testing"""
conn = self.get_connection()
cursor = conn.cursor()
# Sample announcements
announcements = [
("Important: Mid-term Exam Schedule Released",
"The mid-term examination schedule has been posted. Please check your exam dates and prepare accordingly. All exams will be held in the main examination hall.",
"high", "all"),
("Library Hours Extended",
"Due to upcoming exams, library hours have been extended until 8 PM on weekdays.",
"normal", "all"),
("Sports Day Registration Open",
"Registration for annual sports day is now open. Last date for registration: March 15th.",
"normal", "all")
]
cursor.executemany('''
INSERT OR IGNORE INTO announcements (title, content, priority, target_audience)
VALUES (?, ?, ?, ?)
''', announcements)
# Sample examinations
examinations = [
("Mid-term Mathematics", "Mathematics", "2024-03-20", "09:00", 180, "Hall A", "Grade 10", "Bring calculator and geometry box"),
("Mid-term Physics", "Physics", "2024-03-22", "09:00", 180, "Hall B", "Grade 10", "Formula sheet will be provided"),
("Mid-term Chemistry", "Chemistry", "2024-03-25", "09:00", 180, "Hall A", "Grade 10", "Periodic table will be provided")
]
cursor.executemany('''
INSERT OR IGNORE INTO examinations (exam_name, subject, exam_date, exam_time, duration_minutes, location, grade_level, instructions)
VALUES (?, ?, ?, ?, ?, ?, ?, ?)
''', examinations)
# Sample tests/assignments
tests = [
("Chapter 5 Quiz", "Mathematics", "quiz", "2024-03-15", "10:00", "Quadratic equations quiz", "Grade 10", 20),
("Physics Lab Report", "Physics", "assignment", "2024-03-18", "23:59", "Submit pendulum experiment report", "Grade 10", 25),
("Chemistry Unit Test", "Chemistry", "test", "2024-03-16", "11:00", "Atomic structure and bonding", "Grade 10", 50)
]
cursor.executemany('''
INSERT OR IGNORE INTO tests_assignments (title, subject, type, due_date, due_time, description, grade_level, total_marks)
VALUES (?, ?, ?, ?, ?, ?, ?, ?)
''', tests)
# Sample student
cursor.execute('''
INSERT OR IGNORE INTO students (student_id, name, email, grade_level, class_section)
VALUES (?, ?, ?, ?, ?)
''', ("STU001", "John Doe", "john.doe@school.edu", "Grade 10", "10-A"))
# Sample grades
grades = [
("STU001", "Mathematics", "test", "Algebra Test", 85, 100, 85.0, "A", "2024-02-15", "Excellent work!"),
("STU001", "Physics", "quiz", "Motion Quiz", 18, 20, 90.0, "A+", "2024-02-20", "Great understanding"),
("STU001", "Chemistry", "assignment", "Lab Report 1", 22, 25, 88.0, "A", "2024-02-25", "Good analysis")
]
cursor.executemany('''
INSERT OR IGNORE INTO grades (student_id, subject, assessment_type, assessment_name, marks_obtained, total_marks, percentage, grade_letter, date_recorded, teacher_comments)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
''', grades)
# Sample events
events = [
("Spring Break", "2024-03-30", "00:00", "School", "Spring holidays begin", "holiday", 1),
("Science Fair", "2024-04-15", "09:00", "School Auditorium", "Annual science exhibition", "academic", 0),
("Sports Day", "2024-04-20", "08:00", "School Ground", "Annual sports competition", "sports", 0)
]
cursor.executemany('''
INSERT OR IGNORE INTO school_events (event_name, event_date, event_time, location, description, event_type, is_holiday)
VALUES (?, ?, ?, ?, ?, ?, ?)
''', events)
# Sample admin user (password: admin123)
import hashlib
password_hash = hashlib.sha256("admin123".encode()).hexdigest()
cursor.execute('''
INSERT OR IGNORE INTO admin_users (username, password_hash, full_name, email, role)
VALUES (?, ?, ?, ?, ?)
''', ("admin", password_hash, "School Administrator", "admin@school.edu", "admin"))
# Sample syllabus data
syllabus_data = [
("Mathematics", "Grade 10", 1, "Algebra Fundamentals",
'["Linear equations", "Quadratic equations", "Polynomials", "Factoring"]',
"Students will understand and solve algebraic equations", 4,
'["NCERT Mathematics Grade 10", "RD Sharma", "Online Khan Academy"]',
"Tests, Assignments, Projects", "admin"),
("Physics", "Grade 10", 1, "Light and Reflection",
'["Laws of reflection", "Mirrors", "Refraction", "Lenses"]',
"Understanding light behavior and optical instruments", 3,
'["NCERT Physics Grade 10", "HC Verma", "Lab experiments"]',
"Practical exams, Theory tests", "admin"),
("Chemistry", "Grade 10", 1, "Acids, Bases and Salts",
'["Properties of acids", "Properties of bases", "pH scale", "Salt formation"]',
"Chemical properties and reactions of acids and bases", 3,
'["NCERT Chemistry Grade 10", "Lab manual"]',
"Lab reports, Unit tests", "admin")
]
cursor.executemany('''
INSERT OR IGNORE INTO syllabus (subject, grade_level, chapter_number, chapter_title, topics, learning_objectives, duration_weeks, resources, assessment_methods, created_by)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
''', syllabus_data)
# Sample timetable data for Grade 10-A
timetable_data = [
("10-A", "Monday", 1, "09:00", "09:45", "Mathematics", "Mr. Smith", "Room 101", "admin"),
("10-A", "Monday", 2, "09:45", "10:30", "Physics", "Ms. Johnson", "Room 201", "admin"),
("10-A", "Monday", 3, "10:45", "11:30", "Chemistry", "Dr. Brown", "Lab 1", "admin"),
("10-A", "Monday", 4, "11:30", "12:15", "English", "Mrs. Davis", "Room 102", "admin"),
("10-A", "Monday", 5, "13:00", "13:45", "History", "Mr. Wilson", "Room 103", "admin"),
("10-A", "Monday", 6, "13:45", "14:30", "Physical Education", "Coach Miller", "Gym", "admin"),
("10-A", "Tuesday", 1, "09:00", "09:45", "Physics", "Ms. Johnson", "Room 201", "admin"),
("10-A", "Tuesday", 2, "09:45", "10:30", "Mathematics", "Mr. Smith", "Room 101", "admin"),
("10-A", "Tuesday", 3, "10:45", "11:30", "English", "Mrs. Davis", "Room 102", "admin"),
("10-A", "Tuesday", 4, "11:30", "12:15", "Chemistry", "Dr. Brown", "Lab 1", "admin"),
("10-A", "Tuesday", 5, "13:00", "13:45", "Computer Science", "Mr. Tech", "Computer Lab", "admin"),
("10-A", "Tuesday", 6, "13:45", "14:30", "Art", "Ms. Creative", "Art Room", "admin"),
]
cursor.executemany('''
INSERT OR IGNORE INTO timetable (class_section, day_of_week, period_number, start_time, end_time, subject, teacher_name, room_number, created_by)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
''', timetable_data)
conn.commit()
conn.close()
print("Sample data added successfully!")
# Initialize database when module is imported
if __name__ == "__main__":
db = DatabaseManager()
db.add_sample_data() |