Military Technical College – Database Management Console

CREATE DATABASE MilitaryTechCollege
ON PRIMARY (
    NAME = MTC_Data,
    FILENAME = 'C:\SQLData\MilitaryTechCollege.mdf',
    SIZE = 100MB,
    FILEGROWTH = 20MB
)
LOG ON (
    NAME = MTC_Log,
    FILENAME = 'C:\SQLData\MilitaryTechCollege_log.ldf',
    SIZE = 50MB,
    FILEGROWTH = 10MB
);
GO
            

USE MilitaryTechCollege;
GO

CREATE TABLE Departments (
    DepartmentID INT IDENTITY PRIMARY KEY,
    DepartmentCode NVARCHAR(20) UNIQUE,
    DepartmentName NVARCHAR(200),
    Branch NVARCHAR(50),
    CreatedOn DATETIME DEFAULT GETDATE()
);

CREATE TABLE Programs (
    ProgramID INT IDENTITY PRIMARY KEY,
    DepartmentID INT NOT NULL,
    ProgramCode NVARCHAR(20) UNIQUE,
    ProgramName NVARCHAR(200),
    Level NVARCHAR(50),
    DurationYears INT,
    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);

CREATE TABLE Cadets (
    CadetID INT IDENTITY PRIMARY KEY,
    ServiceNumber NVARCHAR(50) UNIQUE,
    FirstName NVARCHAR(100),
    LastName NVARCHAR(100),
    Rank NVARCHAR(50),
    ProgramID INT NOT NULL,
    IntakeYear INT,
    Status NVARCHAR(50),
    FOREIGN KEY (ProgramID) REFERENCES Programs(ProgramID)
);
            

# db_config.py
CONN_STR = (
    "DRIVER={ODBC Driver 17 for SQL Server};"
    "SERVER=localhost;"
    "DATABASE=MilitaryTechCollege;"
    "Trusted_Connection=yes;"
)

# run_sql.py
import pyodbc
from db_config import CONN_STR

def run_sql(sql_text):
    with pyodbc.connect(CONN_STR, autocommit=True) as conn:
        conn.cursor().execute(sql_text)

# import_csv.py
import pandas as pd
import pyodbc
from db_config import CONN_STR

def import_csv(path, table):
    df = pd.read_csv(path)
    with pyodbc.connect(CONN_STR) as conn:
        cur = conn.cursor()
        for _, row in df.iterrows():
            cols = ",".join(df.columns)
            vals = ",".join("?" for _ in df.columns)
            cur.execute(f"INSERT INTO {table} ({cols}) VALUES ({vals})", *row)
        conn.commit()

# export_html.py
import pandas as pd
import pyodbc

def export_report(query, output):
    df = pd.read_sql(query, pyodbc.connect(CONN_STR))
    html = df.to_html(index=False)
    open(output, "w").write(html)