Project File

The document contains a Python script for managing an employee database using MySQL. It includes functions for entering, displaying, updating, searching, and deleting employee records, as well as generating various reports based on employee data. The script also provides a user interface for interacting with these functionalities through a command-line menu.

Uploaded by

Gaurav Sharma

We take content rights seriously. If you suspect this is your content, claim it here.

Available Formats

Download as PDF, TXT or read online on Scribd

0% found this document useful (0 votes)

5 views61 pages

Project File

Uploaded by

Gaurav Sharma

We take content rights seriously. If you suspect this is your content, claim it here.

Available Formats

Download as PDF, TXT or read online on Scribd

import mysql.

connector

con =
[Link](host="localhost",user="root",p
assword="1111")
cursor=[Link]()
[Link]("CREATE DATABASE IF NOT EXISTS
emdatabase")
[Link]("USE emdatabase")

[Link]("""
CREATE TABLE IF NOT EXISTS employees (
Eid char(4) ,
name VARCHAR(30) ,
dob date ,
area varchar(20),
salary float
)
""")
def EnteremployeesDetails():
print("\n============================")
print("Enter employees Details:")
print("\n============================")
mEid = int(input("EnterEmployees Id: "))
mName = input("Enter Name: ")
mDOB = input("Enter Date of Birth: ")
mArea = input("Enter Area: ")
msalary = float(input("Enter Salary: "))

Query="insert into employees

values('{}','{}','{}','{}',{})".format(mEid,mName,mDOB,mAr
ea,msalary)
[Link](Query)
[Link]()

def DisplayAll():
Query="select * from employees"

[Link](Query)
L=[Link]()
for rec in L:
print(rec)

def DeleteEmployees():
mEid= int(input("Enter Employyes Id to Delete:"))
Query="select * from Employees where Eid='{}'
".format(mEid)
[Link](Query)
L = [Link]()
if L:
print(L)
else:
print("Employees not found.\n")
return
Query="delete from Employees where Eid='{}'
".format(mEid)
[Link](Query)
[Link]()
print("Employees deleted successfully.\n")

def updateEmployeesDetails():
mEid = int(input("Enter Employees ID to update: "))
Query="SELECT * FROM Employees WHERE Eid =
'{}'".format(mEid)
[Link](Query)
L=[Link]()
print(L)

if not L:
print("User not found.\n")
return
Query="UPDATE Employees set "
ans=input("Change Name(Y/N)?")
if [Link]()=="Y":
mName=input("Enter New Name:")
Query=Query+"Name='"+mName+"',"

ans=input("Change Date of Birth(Y/N)?")

if [Link]()=="Y":
mDOB=input("Enter Date of Birth:")
Query=Query+"DOB='"+mDOB+"',"

ans=input("Change Area(Y/N)?")
if [Link]()=="Y":
mArea=input("Enter New Area:")
Query=Query+"Area='"+mArea+"',"

ans=input("Change Salary (Y/N)?")

if [Link]()=="Y":
msalary =input("Enter New Salary:")
Query=Query+"Salary='"+msalary+"',"
Query=[Link](",")
Query=Query+" where Eid='{}'".format(mEid)
print(Query)
[Link](Query)
[Link]()
print("Employees Details updated successfully.\n")

def SearchEmployees():
mEid = int (input("Enter Employees ID to search: "))
Query="select * from Employees where Eid='{}'
".format(mEid)
[Link](Query)
L = [Link]()
if L:
print(L)
else:
print("Employees not found.\n")
def exit_system():
print("Exiting the system. Goodbye!")
[Link]()
exit()
def Reports():
while True:
print("1. Employees in the Ascending order of
Names")
print("2. Employees of specific Area ")
print("3. List of Areas")
print("4. No of Employees in specific Area")
print("5. Area wise No of Employees")
print("6. Area wise Total Salary ")
print("7. Maximum Salary from Each Area")
print("8. Minimum Salary From Each area ")
print("9. Average Salary From Each Area")
print("[Link] in Descending order of Date of
Birth ")
print("11. Exit Reports")
choice = input("Enter your choice: ")

if choice == '1':
Query="select * from Employees order by name
asc"
[Link](Query)
L=[Link]()
for rec in L:
print(rec)

elif choice == '2':

mArea = input("Enter Area: ")
Query="SELECT * FROM Employees WHERE Area =
'{}'".format(mArea)
[Link](Query)
L=[Link]()
for rec in L:
print(rec)
elif choice == '3':
Query="select Distinct Area from Employees"
[Link](Query)
L=[Link]()
for rec in L:
print(rec)
elif choice == '4':
mArea = input("Enter Area: ")
Query="SELECT Area,count(*) FROM Employees
WHERE Area = '{}'".format(mArea)
[Link](Query)
L=[Link]()
for rec in L:
print(rec)

elif choice == '5':

Query="select Area,count(*) from Employees
group by area"
[Link](Query)
L=[Link]()
for rec in L:
print(rec)
elif choice == '6':
Query="select Area,sum(Salary) from Employees
group by area"
[Link](Query)
L=[Link]()
for rec in L:
print(rec)
elif choice == '7':
Query="select Area,Max(salary) from Employees
group by area"
[Link](Query)
L=[Link]()
for rec in L:
print(rec)
elif choice == '8':
Query="select Area,min(salary) from Employees
group by area"
[Link](Query)
L=[Link]()
for rec in L:
print(rec)
elif choice == '9':
Query="select Area,avg(salary) from Employees
group by area"
[Link](Query)
L=[Link]()
for rec in L:
print(rec)

elif choice == '10':

Query="select * from Employees order by dob
desc"
[Link](Query)
L=[Link]()
for rec in L:
print(rec)

elif choice == '11':

break
else:
print("Invalid choice.\n")

while True:
print("1. Enter Employees Details")
print("2. Display All Employees")
print("3. Update Employees")
print("4. Search Employees")
print("5. Delete Employees")
print("6. Reports")
print("7. Exit")
choice = input("Enter your choice: ")
if choice == '1':
EnteremployeesDetails()
elif choice == '2':
DisplayAll()
elif choice == '3':
updateEmployeesDetails()
elif choice == '4':
SearchEmployees()
elif choice == '5':
DeleteEmployees()
elif choice == '6':
Reports()
elif choice == '7':
break
else:
print("Invalid choice.\n")
1. Enter Employees Details
2. Display All Employees
3. Update Employees
4. Search Employees
5. Delete Employees
6. Reports
7. Exit
Enter your choice: 1