PYMYSQL-1

import pymysql
# Connect to the database
conn = pymysql.connect(host='localhost',user='root',password='aaditya2007',)
# Create a cursor object
cur = conn.cursor()
#creating database
cur.execute('create database if not exists employees;')
cur.execute('use employees;')
#creating Table-1
cur.execute('create table if not exists emp(Emp_id int(3),Name varchar(25),E_mail varchar
(50),Profession varchar(20),Salary int(5));')
# Insert 15 records into the table
cur.execute('''insert into emp values
(234,'Rahul Sharma', 'rahul.sharma@example.com', 'Sales', 50000),
(453,'Priya Patel', 'priya.patel@example.com', 'Marketing', 60000),
(742,'Amit Kumar', 'amit.kumar@example.com', 'IT', 70000),
(657,'Neha Gupta', 'neha.gupta@example.com', 'HR', 55000),
(493,'Rajesh Singh', 'rajesh.singh@example.com', 'Finance', 65000),
(657,'Sonia Jain', 'sonia.jain@example.com', 'Sales', 45000),
(785,'Vikram Verma', 'vikram.verma@example.com', 'Marketing', 58000),
(578,'Riya Mehta', 'riya.mehta@example.com', 'IT', 62000),
(844,'Kunal Agarwal', 'kunal.agarwal@example.com', 'HR', 51000),
(879,'Tanvi Rao', 'tanvi.rao@example.com', 'Finance', 68000),
(659,'Siddharth Jain', 'siddharth.jain@example.com', 'Sales', 42000),
(789,'Anjali Desai', 'anjali.desai@example.com', 'Marketing', 59000),
(890,'Rohan Patel', 'rohan.patel@example.com', 'IT', 71000),
(478,'Isha Sharma', 'isha.sharma@example.com', 'HR', 53000),
(656,'Aditya Gupta', 'aditya.gupta@example.com', 'Finance', 66000) ;''')
#Functions for 'emp' table
def add_emp():
import random
x=random.randint(100,999)
print("Employee id is ",x)
emp_id=x
name=input("Enter employee name : ")
email=input("Enter e_mail id :")
prof=input("Enter profession : ")
sal=int(input("Enter salary : "))
sol="insert into emp values('%d','%s','%s','%s','%d')"%(emp_id,name,email,prof,sal)
cur.execute(sol)
conn.commit()
print('updated successfully')
g=cur.execute("select * from emp;")
h=cur.fetchall()
for i in h:
print(i)
def update_name():
emp_id=int(input("Enter employee id of 3 digits to update : "))
name=input("Enter updated name : ")
sol="update emp set name='%s' where emp_id='%d' "%(name,emp_id)
cur.execute(sol)
conn.commit()
print('updated successfully')
 query="select * from emp where emp_id='%d' "%(emp_id)
cur.execute(query)
h=cur.fetchall()
for i in h:
print(i)
def update_email():
emp_id=int(input("Enter employee id of 3 digits to update :"))
email=input("Enter new email id : ")
sol="update emp set e_mail='%s' where emp_id='%d' "%(email,emp_id)
cur.execute(sol)
conn.commit()
print('updated successfully')
query="select * from emp where emp_id='%d' "%(emp_id)
cur.execute(query)
h=cur.fetchall()
for i in h:
print(i)
def update_prof():
emp_id=int(input("Enter employee id of 3 digits to update :"))
prof=input("Enter new profession of employee :")
sol="update emp set profession='%s' where emp_id='%d' "%(prof,emp_id)
cur.execute(sol)
conn.commit()
print('updated successfully')
query="select * from emp where emp_id='%d' "%(emp_id)
cur.execute(query)
h=cur.fetchall()
for i in h:
 print(i)
def update_sal():
emp_id=int(input("Enter employee id of 3 digits to update :"))
sal=int(input("Enter new salary of employee :"))
sol="update emp set salary='%d' where emp_id='%d' "%(sal,emp_id)
cur.execute(sol)
conn.commit()
print('updated successfully')
query="select * from emp where emp_id='%d' "%(emp_id)
cur.execute(query)
h=cur.fetchall()
for i in h:
print(i)
def delete_emp():
emp_id=int(input("Enter employee id of 3 digits to update :"))
sol="delete from emp where emp_id='%d' "%(emp_id)
cur.execute(sol)
conn.commit()
print(' deleted successfully')
def search_emp():
emp_id=int(input("Enter employee id of 3 digits to search :"))
sol="select * from emp where emp_id='%d' "%(emp_id)
cur.execute(sol)
h=cur.fetchall()
k=0
for i in h:
k=1
if k==1:
 print("Yes this employee exists")
print(i)
if k==0:
print("No this employee id doesn't exist")
#FUNCTIONS DEFINED TO UPDATE DATA OF EMPLOYEES
def update_data():
while True:
print("What do you want to update sir")
print("1.Update employee name")
print("2.Update employee email ")
print("3.Update employee profession")
print("4.Update employee salary")
print("5.Get Back")
z=int(input("May I know your need SIR : "))
if z==1:
update_name()
elif z==2:
update_email()
elif z==3:
update_prof()
elif z==4:
update_sal()
elif z==5 :
print("Thank you coming sir")
break
else :
print("invalid choice sir , please try again")
continue
#USER INTERFACE
a=input("Enter your name : ")
print("Welcome",a,"to our program [EMPLOYEES]")
while True:
print("Kindly provide us your reason to visit by telling the serial number of following things ")
print("1.View the data of employees ")
print("2.Add the data of employees")
print("3.Update the data of employees")
print("4.Search the data of employees")
print("5.Delete a data of employees")
print("6.Exit")
y=int(input("Enter serial number : "))
if y==1:
g=cur.execute("select * from emp;")
h=cur.fetchall()
for i in h:
print(i)
elif y==2:
add_emp()
elif y==3:
update_data()
elif y==4:
search_emp()
elif y==5:
delete_emp()
elif y==6:
print("Thank you for coming")
break
 else:
print("Invalid choice")
continue

OUTPUT
Enter your name : sahil
Welcome sahil to our program [EMPLOYEES]
Kindly provide us your reason to visit by telling the serial number of following things
1.View the data of employees
2.Add the data of employees
3.Update the data of employees
4.Search the data of employees
5.Delete a data of employees
6.Exit
Enter serial number : 1
(234, 'Rahul Sharma', 'rahul.sharma@example.com', 'Sales', 50000)
(453, 'Priya Patel', 'priya.patel@example.com', 'Marketing', 60000)
(742, 'Amit Kumar', 'amit.kumar@example.com', 'IT', 70000)
(657, 'Neha Gupta', 'neha.gupta@example.com', 'HR', 55000)
(493, 'Rajesh Singh', 'rajesh.singh@example.com', 'Finance', 65000)
(657, 'Sonia Jain', 'sonia.jain@example.com', 'Sales', 45000)
(785, 'Vikram Verma', 'vikram.verma@example.com', 'Marketing', 58000)
(578, 'Riya Mehta', 'riya.mehta@example.com', 'IT', 62000)
(844, 'Kunal Agarwal', 'kunal.agarwal@example.com', 'HR', 51000)
(879, 'Tanvi Rao', 'tanvi.rao@example.com', 'Finance', 68000)
(659, 'Siddharth Jain', 'siddharth.jain@example.com', 'Sales', 42000)
(789, 'Anjali Desai', 'anjali.desai@example.com', 'Marketing', 59000)
(890, 'Rohan Patel', 'rohan.patel@example.com', 'IT', 71000)
(478, 'Isha Sharma', 'isha.sharma@example.com', 'HR', 53000)
(656, 'Aditya Gupta', 'aditya.gupta@example.com', 'Finance', 66000)
Kindly provide us your reason to visit by telling the serial number of following things
1.View the data of employees
2.Add the data of employees
3.Update the data of employees
4.Search the data of employees
5.Delete a data of employees
6.Exit
Enter serial number : 2
Employee id is 390
Enter employee name : Sahil Mishra
Enter e_mail id :sahilmishra1890@gmail.com
Enter profession : IT
Enter salary : 50000
updated successfully
(234, 'Rahul Sharma', 'rahul.sharma@example.com', 'Sales', 50000)
(453, 'Priya Patel', 'priya.patel@example.com', 'Marketing', 60000)
(742, 'Amit Kumar', 'amit.kumar@example.com', 'IT', 70000)
(657, 'Neha Gupta', 'neha.gupta@example.com', 'HR', 55000)
(493, 'Rajesh Singh', 'rajesh.singh@example.com', 'Finance', 65000)
(657, 'Sonia Jain', 'sonia.jain@example.com', 'Sales', 45000)
(785, 'Vikram Verma', 'vikram.verma@example.com', 'Marketing', 58000)
(578, 'Riya Mehta', 'riya.mehta@example.com', 'IT', 62000)
(844, 'Kunal Agarwal', 'kunal.agarwal@example.com', 'HR', 51000)
(879, 'Tanvi Rao', 'tanvi.rao@example.com', 'Finance', 68000)
(659, 'Siddharth Jain', 'siddharth.jain@example.com', 'Sales', 42000)
(789, 'Anjali Desai', 'anjali.desai@example.com', 'Marketing', 59000)
(890, 'Rohan Patel', 'rohan.patel@example.com', 'IT', 71000)
(478, 'Isha Sharma', 'isha.sharma@example.com', 'HR', 53000)
(656, 'Aditya Gupta', 'aditya.gupta@example.com', 'Finance', 66000)
(390, 'Sahil Mishra', 'sahilmishra1890@gmail.com', 'IT', 50000)
Kindly provide us your reason to visit by telling the serial number of following things
1.View the data of employees
2.Add the data of employees
3.Update the data of employees
4.Search the data of employees
5.Delete a data of employees
6.Exit
Enter serial number : 3
What do you want to update sir
1.Update employee name
2.Update employee email
3.Update employee profession
4.Update employee salary
5.Get Back
May I know your need SIR : 1
Enter employee id of 3 digits to update : 390
Enter updated name : Sahil Malhotra
updated successfully
(390, 'Sahil Malhotra', 'sahilmishra1890@gmail.com', 'IT', 50000)
What do you want to update sir
1.Update employee name
2.Update employee email
3.Update employee profession
4.Update employee salary
5.Get Back
May I know your need SIR : 2
Enter employee id of 3 digits to update :390
Enter new email id : sahilmalhotra1890@gmail.com
updated successfully
(390, 'Sahil Malhotra', 'sahilmalhotra1890@gmail.com', 'IT', 50000)
What do you want to update sir
1.Update employee name
2.Update employee email
3.Update employee profession
4.Update employee salary
5.Get Back
May I know your need SIR : 3
Enter employee id of 3 digits to update :390
Enter new profession of employee :Finance
updated successfully
(390, 'Sahil Malhotra', 'sahilmalhotra1890@gmail.com', 'Finance', 50000)
What do you want to update sir
1.Update employee name
2.Update employee email
3.Update employee profession
4.Update employee salary
5.Get Back
May I know your need SIR : 4
Enter employee id of 3 digits to update :390
Enter new salary of employee :52000
updated successfully
(390, 'Sahil Malhotra', 'sahilmalhotra1890@gmail.com', 'Finance', 52000)
What do you want to update sir
1.Update employee name
2.Update employee email
3.Update employee profession
4.Update employee salary
5.Get Back
May I know your need SIR : 5
Thank you coming sir
Kindly provide us your reason to visit by telling the serial number of following things
1.View the data of employees
2.Add the data of employees
3.Update the data of employees
4.Search the data of employees
5.Delete a data of employees
6.Exit
Enter serial number : 4
Enter employee id of 3 digits to search :390
Yes this employee exists
(390, 'Sahil Malhotra', 'sahilmalhotra1890@gmail.com', 'Finance', 52000)
Kindly provide us your reason to visit by telling the serial number of following things
1.View the data of employees
2.Add the data of employees
3.Update the data of employees
4.Search the data of employees
5.Delete a data of employees
6.Exit
Enter serial number : 4
Enter employee id of 3 digits to search :490
No this employee id doesn't exist
Kindly provide us your reason to visit by telling the serial number of following things
1.View the data of employees
2.Add the data of employees
3.Update the data of employees
4.Search the data of employees
5.Delete a data of employees
6.Exit
Enter serial number : 5
Enter employee id of 3 digits to update :390
deleted successfully
Kindly provide us your reason to visit by telling the serial number of following things
1.View the data of employees
2.Add the data of employees
3.Update the data of employees
4.Search the data of employees
5.Delete a data of employees
6.Exit
Enter serial number : 6
Thank you for coming