Spaces:
Build error
Build error
File size: 3,056 Bytes
685f1b5 9c525f8 685f1b5 e2b7d1d 9c525f8 685f1b5 9c525f8 685f1b5 e2b7d1d 685f1b5 9c525f8 685f1b5 e2b7d1d 685f1b5 9c525f8 685f1b5 e2b7d1d 685f1b5 9c525f8 685f1b5 e2b7d1d 685f1b5 9c525f8 685f1b5 e2b7d1d 685f1b5 e2b7d1d 685f1b5 e2b7d1d 685f1b5 e2b7d1d 685f1b5 e2b7d1d 685f1b5 e2b7d1d 685f1b5 e2b7d1d |
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 |
import sqlite3
import os
def initialize_database(clear_data=False):
db_path = os.path.abspath(os.path.join(os.path.dirname(__file__), "../college.db"))
print(f"Initializing database at: {db_path}")
conn = sqlite3.connect(db_path)
cursor = conn.cursor()
# Create tables
cursor.execute("""
CREATE TABLE IF NOT EXISTS student (
student_id INTEGER PRIMARY KEY AUTOINCREMENT,
first_name TEXT,
last_name TEXT,
date_of_birth DATE,
email TEXT,
phone_number TEXT,
major TEXT,
year_of_enrollment INTEGER
);""")
cursor.execute("""
CREATE TABLE IF NOT EXISTS employee (
employee_id INTEGER PRIMARY KEY AUTOINCREMENT,
first_name TEXT,
last_name TEXT,
email TEXT,
department TEXT,
position TEXT,
salary REAL,
date_of_joining DATE
);""")
cursor.execute("""
CREATE TABLE IF NOT EXISTS course (
course_id INTEGER PRIMARY KEY AUTOINCREMENT,
course_name TEXT,
course_code TEXT,
instructor_id INTEGER,
department TEXT,
credits INTEGER,
semester TEXT,
FOREIGN KEY (instructor_id) REFERENCES employee(employee_id)
);""")
if clear_data:
cursor.execute("DELETE FROM course;")
cursor.execute("DELETE FROM student;")
cursor.execute("DELETE FROM employee;")
# Insert sample data
students = [
("Alice", "Johnson", "2001-06-15", "alice@example.com", "1234567890", "Computer Science", 2019),
("Bob", "Smith", "2000-04-22", "bob@example.com", "0987654321", "Mechanical Engineering", 2018),
("Charlie", "Brown", "2002-01-10", "charlie@example.com", "1112223333", "ECE", 2020),
("Daisy", "Miller", "2001-12-12", "daisy@example.com", "2223334444", "Civil", 2019),
("Ethan", "Lee", "2000-05-05", "ethan@example.com", "3334445555", "IT", 2018)
]
cursor.executemany("INSERT INTO student (first_name, last_name, date_of_birth, email, phone_number, major, year_of_enrollment) VALUES (?, ?, ?, ?, ?, ?, ?);", students)
employees = [
("Drake", "Miller", "drake@example.com", "CSE", "Professor", 75000.00, "2015-08-01"),
("Laura", "White", "laura@example.com", "ECE", "Assistant Professor", 65000.00, "2018-07-10"),
("James", "Hall", "james@example.com", "IT", "Lecturer", 55000.00, "2019-01-15")
]
cursor.executemany("INSERT INTO employee (first_name, last_name, email, department, position, salary, date_of_joining) VALUES (?, ?, ?, ?, ?, ?, ?);", employees)
courses = [
("Database Systems", "CS301", 1, "CSE", 4, "Fall"),
("Digital Electronics", "EC202", 2, "ECE", 3, "Spring"),
("Web Technologies", "IT301", 3, "IT", 4, "Spring")
]
cursor.executemany("INSERT INTO course (course_name, course_code, instructor_id, department, credits, semester) VALUES (?, ?, ?, ?, ?, ?);", courses)
conn.commit()
conn.close()
print("✅ Database initialized and sample data inserted.")
|