Adeeb's DBMS project for Class XII - School Management System

adeeb_dummy_data.sql

INSERT INTO teachers VALUES
(0, "Salma", "Anjum", "[email protected]", "1969-10-26", "1993-10-26", "WORKING"),
(0, "Rauf", "Khan", "", "1965-10-26", "1992-10-26", "WORKING");

adeeb_project.py

import sys

# from adeeb_sql import *

# c1 = mysql_connect()
# create_db(c1)

# c2 = mysql_connect("school_db")
# create_tables(c2)
# add_dummy_data(c2)

# drop_db(c2)

def teachers():
    while True:
        print("1. Add new teacher")
        print("2. Update teacher's information")
        print("3. Go back to previous menu")
        print()

        op = int(input("Select option: "))

        if op == 1:
            print("Add teacher's information")
            fname = input("First Name: ")
            lname = input("Last Name: ")
            email = input("Email: ")
            dob = input("Date of Birth: ")
            doj = input("Date of Joining: ")
            status = input("Status: ")

            print("Teacher added")
            # add_teacher(c2, fname, lname, email, dob, doj, status)

        elif op == 2:
            tid = input("Enter ID of teacher to update")

            fname, lname, email, dob, doj, status = get_teacher(c2, tid)

            print("Current information:")
            print("First Name: " + fname)
            print("Last Name: " + lname)
            print("Email: " + email)
            print("Date of Birth: " + dob)
            print("Date of Joining: " + doj)
            print("Status: " + status)

        elif op == 3:
            break

def classes():
    pass

def students():
    pass

def attendance():
    pass

def marks():
    pass


def main():

    while True:
        print("Welcome to School management system")
        print("1. Teachers")
        print("2. Classes")
        print("3. Students")
        print("4. Student Attendance")
        print("5. Student Marks")
        print("6. Exit")
        print()

        op = int(input("Select table to operate on: "))

        if op == 1:
            teachers()
        elif op == 2:
            classes()
        elif op == 3:
            students()
        elif op == 4:
            attendance()
        elif op == 5:
            marks()
        elif op == 6:
            sys.exit()

main()

adeeb_schema.sql

CREATE TABLE IF NOT EXISTS teachers (
	id		INT PRIMARY KEY AUTO_INCREMENT,
	fname	VARCHAR(50) NOT NULL,
  	lname	VARCHAR(50),
  	email	VARCHAR(100),
  	dob		DATE NOT NULL,
  	doj		DATE NOT NULL,
  	status VARCHAR(25) NOT NULL
);

CREATE TABLE IF NOT EXISTS students (
	id		INT PRIMARY KEY AUTO_INCREMENT,
	fname	VARCHAR(50) NOT NULL,
  	lname	VARCHAR(50),
  	email	VARCHAR(100),
  	dob		DATE NOT NULL,
  	doj		DATE NOT NULL,
  	status VARCHAR(25) NOT NULL
);


CREATE TABLE IF NOT EXISTS classes (
	id		INT PRIMARY KEY AUTO_INCREMENT,
	class	VARCHAR(50) NOT NULL,
  	section	VARCHAR(50) NOT NULL,
  	teacher_id INT NOT NULL,

    FOREIGN KEY (teacher_id) REFERENCES teachers (id)
);


CREATE TABLE IF NOT EXISTS attendance (
	doa DATE NOT NULL,
	class_id	INT NOT NULL,
  	student_id INT NOT NULL,

   	FOREIGN KEY (class_id) REFERENCES classes(id),
    FOREIGN KEY (student_id) REFERENCES students(id)
);


CREATE TABLE IF NOT EXISTS marks (
	class_id	INT NOT NULL,
  	student_id INT NOT NULL,
  	exam		VARCHAR(50),
  	subject 	VARCHAR(50),
  	marks		INT,

   	FOREIGN KEY (class_id) REFERENCES classes(id),
    FOREIGN KEY (student_id) REFERENCES students(id)
);

adeeb_sql.py

import mysql.connector

def mysql_connect(dbname=None):
    if dbname:
        conn = mysql.connector.connect(
            host="localhost",
            user="root",
            password="yourpassword",
            database=dbname
        )
    else:
        conn = mysql.connector.connect(
            host="localhost",
            user="root",
            password="yourpassword",
        )

    return conn

def drop_db(conn):
    cur = conn.cursor()
    cur.execute("DROP DATABASE school_db;")
    conn.commit()

def create_db(conn):
    cur = conn.cursor()
    cur.execute("CREATE DATABASE school_db;")
    conn.commit()

def create_tables(conn):
    cur = conn.cursor()
    with open('adeeb_schema.sql') as f:
        cur.execute(f.read().decode('utf-8'), multi=True)
    conn.commit()

def add_dummy_data(conn):
    cur = conn.cursor()
    with open('adeeb_dummy_data.sql') as f:
        cur.execute(f.read().decode('utf-8'), multi=True)
    conn.commit()

def get_teachers(conn):
    cur = conn.cursor()
    query = "SELECT * FROM teachers;"
    cur.execute(query)
    results = cur.fetchall()
    return results

def get_teacher(conn, tid):
    cur = conn.cursor()
    query = "SELECT * FROM teachers WHERE id = %s;"
    values = (tid,)
    cur.execute(query, values)
    results = cur.fetchall()
    return results

def add_teacher(conn, fname, lname, email, dob, doj, status):
    cur = conn.cursor()
    query = "INSERT INTO teachers VALUES (0, %s, %s, %s, %s, %s, %s);"
    values = (fname, lname, email, dob, doj, status)
    cur.execute(query, values)
    conn.commit()