EMS code, a CRUD application based on MVT architectures.

 ORM.py

import pyodbc as p

def getConnection():
    connection = p.connect(r'Driver=SQL Server; Server=DESKTOP-21MU0SK\SQLEXPRESS;Database=master_2;')
    cursor = connection.cursor()
    return cursor

class Model:

    def fetch(self):
        cursor = getConnection()
        cursor.execute("select * from EMPLOYEE")
        rows = cursor.fetchall()
        return rows

    def save(self):
        cursor = getConnection()
        cursor.execute(f"Insert into Employee values({self.empNo},'{self.empName}','{self.Designation}',{self.Salary},{self.join_date},{self.DeptNo},'{self.empAddress}')")
        cursor.commit()
        print("row inserted successfully")

    def update(self):
        cursor = getConnection()
        cursor.execute(f"update EMPLOYEE set empName = '{self.empName}', Designation = '{self.Designation}', join_date = {self.join_date}, Salary = {self.Salary}, DeptNo = {self.DeptNo}, empAddress = '{self.empAddress}' where empNo = {self.empNo}")
        cursor.commit()
        print("Employee Updated Successfully!")

    
    def delete(self):
        cursor = getConnection()
        cursor.execute(f"delete from EMPLOYEE where empNo = {self.empNo}")
        cursor.commit()
        print("row deleted successfully")

models.py

import ORM

class Employee(ORM.Model):
    def __init__(self, empNo= 100, empName= 'Harry', Designation= 'IT Expert', join_date = '2020-09-09', Salary= 3000, DeptNo= 3, empAddress = 'VIP Road'):
        self.empNo = empNo
        self.empName = empName
        self.Designation = Designation
        self.join_date = join_date
        self.Salary = Salary
        self.DeptNo = DeptNo
        self.empAddress = empAddress

    def __str__(self):
        return (f"Employee No: {self.empNo}\n"
                f"Name: {self.empName}\n"
                f"Designation: {self.Designation}\n"
                f"Join Date: {self.join_date}\n"
                f"Salary: ${self.Salary}\n"
                f"Department No: {self.DeptNo}\n"
                f"Address: {self.empAddress}")

views.py

from models import Employee

def getEmployees():
    objEmp = Employee()
    rows = objEmp.fetch()
    empList = []
    for row in rows:
        employee = Employee(empNo= row[0], empName = row[1], Designation= row[2],join_date = row[3], Salary= row[4], DeptNo= row[5], empAddress = row[6])
        empDict = employee.__dict__
        empList.append(empDict)
    return empList

def addEmployee(empNo, empName, Designation, join_date, Salary, DeptNo, empAddress):
    objEmp = Employee(empNo, empName, Designation, join_date, Salary, DeptNo, empAddress)
    objEmp.save()

def updateEmployee(empNo, empName, Designation, join_date, Salary, DeptNo, empAddress):
    objEmp = Employee(empNo, empName, Designation, join_date, Salary, DeptNo, empAddress)
    objEmp.update()

def deleteEmployee(empNo):
    objEmp = Employee(empNo)
    objEmp.delete()

templates.py

import views
from models import Employee

user_input= input("enter 'GET' to fetch data, 'ADD' to insert data, 'UPDATE' to update data & 'DELETE' to Delete data: ")

if user_input == 'GET':
    rows = views.getEmployees()
    print(rows)
    
elif user_input == 'ADD':

    empNo =  input("Enter Employee no: ")
    empName = input("Enter your Name: ")
    Designation = input("Enter Desgnation: ")
    join_date = input("Enter join date: ")
    Salary = input("Enter Salary: ")
    DeptNo = input("Enter department no: ")
    empAddress = input("Enter Address: ")

    views.addEmployee(empNo, empName, Designation, join_date, Salary, DeptNo, empAddress)


elif user_input == 'UPDATE':

    empNo =  input("Enter Employee no: ")
    empName = input("Enter your Name: ")
    Designation = input("Enter Desgnation: ")
    join_date = input("Enter join date: ")
    Salary = input("Enter Salary: ")
    DeptNo = input("Enter department no: ")
    empAddress = input("Enter Address: ")

    views.updateEmployee(empNo, empName, Designation, join_date, Salary, DeptNo, empAddress)

elif user_input == 'DELETE':
    empNo = input("Enter Employee no: ")
    views.deleteEmployee(empNo)

else:
    print("please enter a valid commandx")