Python Interface with MySQL
30. Program to connect with database and store record of employee and display
records.
Code:-
import [Link] as mycon
con = [Link](host='localhost',user='root',password="admn")
cur = [Link]()
[Link]("create database if not exists company")
[Link]("use company")
[Link]("create table if not exists employee(empno int, name
varchar(20), dept varchar(20),salary int)")
[Link]()
choice=None
while choice!=0:
print("1. ADD RECORD ")
print("2. DISPLAY RECORD ")
print("0. EXIT")
choice = int(input("Enter Choice :"))
if choice == 1:
e = int(input("Enter Employee Number :"))
n = input("Enter Name :")
d = input("Enter Department :")
s = int(input("Enter Salary :"))
query="insert into employee values({},'{}','{}',
{})".format(e,n,d,s)
[Link](query)
[Link]()
print("## Data Saved ##")
elif choice == 2:
query="select * from employee"
[Link](query)
result = [Link]()
print("%10s"%"EMPNO","%20s"%"NAME","%15s"%"DEPARTMENT","%10s"%"SA
LARY")
for row in result:
print("%10s"%row[0],"%20s"%row[1],"%15s"%row[2],"%10s"%row[3])
elif choice==0:
[Link]()
print("## Bye!! ##")
else:
print("## INVALID CHOICE ##")
Sample Output:-
31. Program to connect with database and search employee number in table employee and
display record, if empno not found display appropriate message.
Code:-
import [Link] as mycon
con = [Link](host='localhost',user='root',password="admn",
database="company")
cur = [Link]()
print("#"*40)
print("EMPLOYEE SEARCHING FORM")
print("#"*40)
print("\n\n")
ans='y'
while [Link]()=='y':
eno = int(input("ENTER EMPNO TO SEARCH :"))
query="select * from employee where empno={}".format(eno)
[Link](query)
result = [Link]()
if [Link]==0:
print("Sorry! Empno not found ")
else:
print("%10s"%"EMPNO", "%20s"%"NAME","%15s"%"DEPARTMENT",
"%10s"%"SALARY")
for row in result:
print("%10s"%row[0],"%20s"%row[1],"%15s"%row[2],"%10s"%row[3])
ans=input("SEARCH MORE (Y) :")
Sample Output:-
32. Program to connect with database and update the employee record of entered empno.
Code:-
import [Link] as mycon
con = [Link](host='localhost',user='root',password="admn",
database="company")
cur = [Link]()
print("#"*40)
print("EMPLOYEE UPDATION FORM")
print("#"*40)
print("\n\n")
ans='y'
while [Link]()=='y':
eno = int(input("ENTER EMPNO TO UPDATE :"))
query="select * from employee where empno={}".format(eno)
[Link](query)
result = [Link]()
if [Link]==0:
print("Sorry! Empno not found ")
else:
print("%10s"%"EMPNO","%20s"%"NAME","%15s"%"DEPARTMENT","%10s"%"SA
LARY")
for row in result:
print("%10s"%row[0],"%20s"%row[1],"%15s"%row[2],"%10s"%
row[3])
choice=input("\n## ARE YOUR SURE TO UPDATE ? (Y) :")
if [Link]()=='y':
print("== YOU CAN UPDATE ONLY DEPT AND SALARY ==")
print("== FOR EMPNO AND NAME CONTACT ADMIN ==")
d = input("ENTER NEW DEPARTMENT,(LEAVE BLANK IF NOT
WANT TO CHANGE )")
if d=="":
d=row[2]
try:
s = int(input("ENTER NEW SALARY,(LEAVE BLANK IF
NOT WANT TO CHANGE ) "))
except:
s=row[3]
query="update employee set dept='{}',salary={} where
empno={}".format(d,s,eno)
[Link](query)
[Link]()
print("## RECORD UPDATED ## ")
ans=input("UPDATE MORE (Y) :")
Sample Output:-
33. Program to connect with database and delete the record of entered employee number.
Code:-
import [Link] as mycon
con = [Link](host='localhost',user='root',password="admn",
database="company")
cur = [Link]()
print("#"*40)
print("EMPLOYEE DELETION FORM")
print("#"*40)
print("\n\n")
ans='y'
while [Link]()=='y':
eno = int(input("ENTER EMPNO TO DELETE :"))
query="select * from employee where empno={}".format(eno)
[Link](query)
result = [Link]()
if [Link]==0:
print("Sorry! Empno not found ")
else:
print("%10s"%"EMPNO","%20s"%"NAME", "%15s"%"DEPARTMENT",
"%10s"%"SALARY")
for row in result:
print("%10s"%row[0],"%20s"%row[1],"%15s"%row[2],"%10s"%
row[3])
choice=input("\n## ARE YOUR SURE TO DELETE ? (Y) :")
if [Link]()=='y':
query="delete from employee where
empno={}".format(eno)
[Link](query)
[Link]()
print("=== RECORD DELETED SUCCESSFULLY! ===")
ans=input("DELETE MORE ? (Y) :")
Sample Output:-
34. Program to connect Python with MySQL and do the following:
Table:- STORE
ITEMNO ITEM SCODE QTY RATE LASTBUY
2003 Sharpener Classic 23 60 8 30-Jun-09
2002 Ball Pen 0.25 22 50 25 01-Feb-10
2004 Gel Pen Premium 21 150 12 24-Feb-10
2001 Gel Pen Classic 21 250 20 11-Mar-09
2005 Eraser Small 22 220 6 19-Jan-09
(i) Create a connection between MYSQL and Python.
(ii) Create database Test and activate it
(iii) Create table STORE using information given above.
(iv) Insert record in the table and display it
(v) SELECT MAX (LASTBUY) FROM STORE.
Code:-
import [Link] as mycon
con =
[Link](host='localhost',user='root',password="admin")
cur = [Link]()
[Link]("create database if not exists Test")
[Link]("use Test")
[Link]("create table if not exists STORE(ITEMNO int primary
key, ITEM varchar(25), SCODE int, QTY int, RATE int, LASTBUY
date)")
[Link]()
choice=None
while choice!=0:
print("1. ADD RECORD ")
print("2. DISPLAY RECORD ")
print("3. MAXIMUM LASTBUY ")
print("0. EXIT")
choice = int(input("Enter Choice :"))
if choice == 1:
ino = int(input("Enter Item Number :"))
inm = input("Enter Item Name :")
sc = int(input("Enter Supplier Code :"))
q = int(input("Enter Quantity :"))
r = int(input("Enter Rate :"))
l = input("Enter Last Buy :")
query="insert into STORE values({},'{}',{},{},
{},'{}')".format(ino,inm,sc,q,r,l)
[Link](query)
[Link]()
print("## Data Saved ##")
elif choice == 2:
query="select * from STORE"
[Link](query)
result = [Link]()
print("%10s"%"ITEMNO","%25s"%"ITEM","%10s"%"SCODE","%10s"%"QTY","
%10s"%"RATE","%14s"%"LASTBUY")
for row in result:
print("%10s"%row[0],"%25s"%row[1],"%10s"%row[2],"%10s"%row[3],"%1
0s"%row[4],"%14s"%row[5])
elif choice == 3:
query="select MAX(LASTBUY) from STORE"
[Link](query)
result = [Link]()
if [Link]==0:
print("Sorry! No record is found ")
else:
print("%10s"%"MAX(LASTBUY)")
print("%14s"%result[0])
elif choice==0:
[Link]()
print("## END!! ##")
else:
print("## INVALID CHOICE ##")
Sample Output:-