Employee Management System, which is basically a CRUD application based on a 3-tier architecture.

 3_tier_architecture.txt

THREE TIER ARCHITECTURE:
Three Tier Architecture is a structured approach to software design. In this architecture, a developer divides the project into three separate layers: Presentation Layer (UI), Business Logic Layer (BLL), and Database Access Layer (DBAL).

Presentation Layer (UI): This is the user interface layer where the user interacts with the application.

Business Logic Layer (BLL): This layer contains the core logic of your application.
--> It defines how the data should be processed.
--> This layer acts as a "bridge" between the UI and DBAL.

Database Access Layer (DBAL): This layer interacts directly with the database.
--> It executes SQL queries.
--> In DBAL, we establish a connection with the database and write functions that      perform CRUD operations.

By keeping these layers separate, developers can easily update or change one part without affecting the others. This leads to better organization and flexibility in the application.

pyodbc_learning.txt

PYODBC:
 
pyodbc is a python libraray that lets us establish conncetion with database

Installation of pyodbc (python library)
installation command: pip install pyodbc

 ------------------------------------------------------------------------------------------------

To connect to a database and retrieve data using the Python, you only need a few functions:

1. connect() function to create a connection to the database;
2. cursor() function to create a cursor from the connection;
3. execute() function to execute a select statement;
4. fetchall() function to retrieve all rows from the query.
5. fetchone() function returns a single row as a tuple.
6. commit() function is used to commit the changes made to the database. It is used while performing operations like insert, update or delete.
7. cursor.close() is used to close the cursor.
8. connection.close() is used to close the database connection.


 ------------------------------------------------------------------------------------------------

import pyodbc as p

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

print(connection)


 ---------------------------------------------------------------------------------------------

cursor = connection.cursor()
print(cursor)

 -----------------------------------------------------------------------------------------------

cursor.execute("select * from EMPLOYEE")
row = cursor.fetchone()
rows = cursor.fetchall()
print(rows)


for row in rows:
    print(row)

print(rows[3][1])


 ------------------------------------------------------------------------------------------------

cursor.execute("select * from Employee where empNo = 2")
row = cursor.fetchone()
print(row)


 -----------------------------------------------------------------------------------------------

Inserting data:

cursor.execute("INSERT INTO EMPLOYEE VALUES (10, 'Mantha', 'Data Scientist', '2022-01-15', 60000, 1, 'Koh-e-fiza')")
cursor.commit()
print("row added successfully")

DBAL.py

import pyodbc as p
from BLL import Employee

def getConnection():
    connection = p.connect(r'Driver=SQL Server; Server=DESKTOP-21MU0SK\SQLEXPRESS;Database=EMS_2024;')
    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")
    

BLL.py

class Employee:
    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

UI.py

import DBAL
from BLL import Employee

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 = DBAL.getEmployees()
    print(rows)

elif user_input == 'ADD':
    objEmp = Employee()
    
    objEmp.empNo = input("enter a employee number: ")
    objEmp.empName = input("enter employee name: ")
    objEmp.Designation = input("enter Designation: ")
    objEmp.join_date = input("enter join date: ")
    objEmp.Salary = input("enter Salary:")
    objEmp.DeptNo = input("enter DeptNo: ")
    objEmp.empAddress = input("enter address empAddress: ")

    DBAL.addEmployee(objEmp)


elif user_input == 'UPDATE':
    ID = input("enter the id of the row you want to update: ")
    row = DBAL.getEmployeeById(ID)
    print(row)

    print("ENTER UPDATION DETAILS: ")

    objEmp = Employee()
    
    objEmp.empNo = ID
    objEmp.empName = input("enter employee name: ")
    objEmp.Designation = input("enter Designation: ")
    objEmp.join_date = input("enter join date: ")
    objEmp.Salary = input("enter Salary:")
    objEmp.DeptNo = input("enter DeptNo: ")
    objEmp.empAddress = input("enter address empAddress: ")

    DBAL.updateEmployee(objEmp)

elif user_input == 'DELETE':
    ID = input("enter the id of the row you want to delete: ")
    DBAL.deleteEmployee(ID)

else:
    print("please enter valid command.")