-------------------------------------------------DBAL.py--------------------------------------------------------
import pyodbc as p
from BLL import Employee
def getConnection():
connection=p.connect(r'Driver=SQL Server; Server=IBRAT786\SQLEXPRESS;Database=EMS-crud application;')
cursor=connection.cursor()
return cursor
print(getConnection())
def getEmployees():
cursor = getConnection()
cursor.execute("select * from EMPLOYEE")
rows = cursor.fetchall()
emp_list = []
for row in rows:
objEmp = Employee(empNo= row[0], empName = row[1], Designation = row[2],join_date = row[3],Salary= row[4],DeptNo = row[5],empAddress = row[6])
objDict = objEmp.__dict__
emp_list.append(objDict)
return emp_list
print(getEmployees())
def addEmployee(obj):
cursor = getConnection()
cursor.execute(f"insert into Employee values({obj.empNo},'{obj.empName}','{obj.Designation}',{obj.join_date},{obj.Salary},{obj.DeptNo},'{obj.empAddress}')")
cursor.commit()
print("row added successfully!")
def getEmployeeById(n):
cursor = getConnection()
cursor.execute(f"select * from EMPLOYEE where empNo = {n}")
row = cursor.fetchone()
## print(row)
objEmp = Employee(empNo= row[0], empName = row[1], Designation = row[2],join_date = row[3],Salary= row[4],DeptNo = row[5],empAddress = row[6])
objDict = objEmp.__dict__
return objDict
def updateEmployee(obj):
cursor = getConnection()
cursor.execute(f"update EMPLOYEE set empName = '{obj.empName}', Designation = '{obj.Designation}', join_date = {obj.join_date}, Salary = {obj.Salary}, DeptNo = {obj.DeptNo}, empAddress = '{obj.empAddress}' where empNo = {obj.empNo}")
cursor.commit()
print("row updated sucessfully!")
def deleteEmployee(pk):
cursor = getConnection()
cursor.execute(f"delete from EMPLOYEE where empNo = {pk}")
cursor.commit()
print("row deleted successfully")
-------------------------------------------models.py---------------------------------------------------------------
import ORM
class Employee(ORM.Model):
def __init__(self,empNo = 200,empName= 'Sara',Designation = 'IT Expert',join_date = '2009-09-09',Salary= 7000,DeptNo = 1,empAddress= 'Idgah'):
self.empNo = empNo
self.empName = empName
self.Designation = Designation
self.join_date = join_date
self.Salary = Salary
self.DeptNo = DeptNo
self.empAddress = empAddress
---------------------------------------------------ORM.py---------------------------------------------------------------
import pyodbc as p
def getConnection():
connection=p.connect(r'Driver=SQL Server; Server=IBRAT786\SQLEXPRESS;Database=EMS-crud application;')
cursor=connection.cursor()
return cursor
##print(getConnection())
class Model:
def fetchall(self):
cursor = getConnection()
cursor.execute("select * from EMPLOYEE")
rows = cursor.fetchall()
## emp_list=[]
## for row in rows:
## objEmp = Employee(empNo=row[0],empName=row[1],Designation = row[2],join_date = row[3],Salary= row[4],DeptNo = row[5],empAddress = row[6])
## objDict = objEmp.__dict__
## emp_list.append(objDict)
## return emp_list
##
def save(self):
cursor = getConnection()
cursor.execute(f"insert into Employee values({self.empNo},'{self.empName}','{self.Designation}','{self.join_date}',{self.Salary},{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 sucessfully...")
def delete(self):
cursor = getConnection()
cursor.execute(f"delete from EMPLOYEE where empNo = {self.empNo}")
cursor.commit()
print("row deleted successfully...")
##print(Model())
------------------------------------------------------templates.py--------------------------------------------------------
import views
user_input = input("enter 'GET' to fetch all rows, enter 'ADD' to insert a new row, enter 'UPDATE' to update an existing row and 'DELETE' to delete a row: ")
if user_input == 'GET':
rows = views.getEmployee()
print(rows)
elif user_input == 'ADD':
empNo = input("enter a employee number: ")
empName = input("enter employee name: ")
Designation = input("enter Designation: ")
join_date = input("enter join date: ")
Salary = input("enter Salary:")
DeptNo = input("enter DeptNo: ")
empAddress = input("enter address empAddress: ")
views.addEmployee(empNo,empName,Designation,join_date,Salary,DeptNo,empAddress)
elif user_input == 'UPDATE':
empNo = input("enter empNo :")
empName = input("enter employee name: ")
Designation = input("enter Designation: ")
join_date = input("enter join date: ")
Salary = input("enter Salary:")
DeptNo = input("enter DeptNo: ")
empAddress = input("enter address empAddress: ")
views.updateEmployee(empNo,empName,Designation,join_date,Salary,DeptNo,empAddress)
##elif user_input == 'UPDATE':
## ID = input("enter the id of the row you want to update: ")
##
## empNo = ID
## empName = input("enter employee name: ")
## Designation = input("enter Designation: ")
## join_date = input("enter join date: ")
## Salary = input("enter Salary:")
## DeptNo = input("enter DeptNo: ")
## empAddress = input("enter address empAddress: ")
##
## views.updateEmployee(empNo,empName,Designation,join_date,Salary,DeptNo,empAddress)
elif user_input == 'DELETE':
ID = input("enter the id of the row you want to delete: ")
views.deleteEmployee(ID)
--------------------------------------------------------views.py--------------------------------------------------
from models import Employee
def getEmployee():
objEmp=Employee()
rows=objEmp.fetchall()
emp_list=[]
for row in rows:
objEmp = Employee(empNo=row[0],empName=row[1],Designation = row[2],join_date = row[3],Salary= row[4],DeptNo = row[5],empAddress = row[6])
objDict = objEmp.__dict__
emp_list.append(objDict)
return emp_list
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()
Comments
Post a Comment