Database Connectivity Programs

1) Program to connect with database and store record of employee and display records.
Source Code
import mysql.connector as mycon
con = mycon.connect(host='localhost', user='root', password = "admin")

cur = con.cursor()
cur.execute("create database if not exists company")
cur.execute("use company")
cur.execute("create table if not exists employee(empno int, name varchar(20), dept
varchar(20),salary int)")
con.commit()
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)
cur.execute(query)
con.commit()
print("## Data Saved ##")
elif choice == 2:
query="select * from employee"
cur.execute(query)
result = cur.fetchall()
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])
elif choice==0:
con.close()
print("## EXIT!! ##")
else:
print("## INVALID CHOICE ##")
Output
1. ADD RECORD
2. DISPLAY RECORD
0. EXIT
Enter Choice :1
Enter Employee Number :101
Enter Name :n1
Enter Department :d1
Enter Salary :10000
## Data Saved ##
1. ADD RECORD
2. DISPLAY RECORD
0. EXIT
Enter Choice :1
Enter Employee Number :102
Enter Name :n2
Enter Department :d3
Enter Salary :20000
## Data Saved ##
1. ADD RECORD
2. DISPLAY RECORD
0. EXIT
Enter Choice :2
EMPNO NAME DEPARTMENT SALARY
101 n1 d1 10000
102 n2 d3 20000
1. ADD RECORD
2. DISPLAY RECORD
0. EXIT
Enter Choice :
=====================================================================================
Some cursor functions
1. cur.fetchall()- [(1,"raj","cardio"),(2,"sam","dermatologist"),(3,"bob","ophalmologist")]
It returns a list of tuples
If there are no records to fetch, then fetchall() returns empty list
2. cur.fetchone()- (1,"raj","cardio")
It returns a tuple
If there are no records to fetch, then fetchone() returns None
3. cur.fetchmany(2)- [(1,"raj","cardio"),(2,"sam","dermatologist")]
It returns a list of tuples
If there are no records to fetch, then fetchmany(n) returns empty list
4. cur.rowcount- It returns int

Parametarized query
new style:
str="select * from student where marks>{} and section='{}'".format(70,'A')
str="select name,age from student where age<{} and marks between {} and {}".format(30,40,70)
old style:
str="select * from student where marks>%s"%(70,)" and section=%s"%('A',)
2) Program to connect with database and store record of employee and display records.
Source Code
con = mycon.connect(host='localhost',user='root',password="admin", database="company")

cur = con.cursor()
print("#"*40)
print("EMPLOYEE SEARCHING FORM")
print("#"*40)
print("\n\n")
ans='y'
while ans.lower()=='y':
eno = int(input("ENTER EMPNO TO SEARCH :"))
query="select * from employee where empno={}".format(eno)
cur.execute(query)
if cur.rowcount==0:
print("Sorry! Empno not found ")
else:
print("%10s"%"EMPNO", "%20s"%"NAME","%15s"%"DEPARTMENT", "%10s"%"SALARY")
for row in result:
ans=input("SEARCH MORE (Y) :")
Output
########################################
EMPLOYEE SEARCHING FORM
########################################
ENTER EMPNO TO SEARCH :101

101 n1 d1 10000
SEARCH MORE (Y) :y
ENTER EMPNO TO SEARCH :103
Sorry! Empno not found
SEARCH MORE (Y) :
3) Program to connect with database and update the employee record of entered empno.
Source Code

cur = con.cursor()
print("#"*40)
print("EMPLOYEE UPDATION FORM")
print("#"*40)
print("\n\n")
ans='y'
eno = int(input("ENTER EMPNO TO UPDATE :"))
cur.execute(query)
if cur.rowcount==0:
else:
print("%10s"%"EMPNO","%20s"%"NAME", "%15s"%"DEPARTMENT", "%10s"%"SALARY")
for row in result:
choice=input("\n## ARE YOUR SURE TO UPDATE ? (Y) :")
if choice.lower()=='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]
s = int(input("ENTER NEW SALARY,(LEAVE BLANK IF NOT WANT TO CHANGE ) "))
if s == "":
s=row[3]
query="update employee set dept='{}',salary={} where empno={}".format(d,s,eno)
cur.execute(query)
con.commit()
print("## RECORD UPDATED ## ")
ans=input("UPDATE MORE (Y) :")
Output
########################################
EMPLOYEE UPDATION FORM
########################################
ENTER EMPNO TO UPDATE :101

101 n1 d1 10000
## ARE YOUR SURE TO UPDATE ? (Y) :y

== YOU CAN UPDATE ONLY DEPT AND SALARY ==
== FOR EMPNO AND NAME CONTACT ADMIN ==
ENTER NEW DEPARTMENT,(LEAVE BLANK IF NOT WANT TO CHANGE )
ENTER NEW SALARY,(LEAVE BLANK IF NOT WANT TO CHANGE ) 15000
## RECORD UPDATED ##
UPDATE MORE (Y) :n
4) Program to connect with database and delete the record of entered employee number.
Source Code

cur = con.cursor()
print("#"*40)
print("EMPLOYEE DELETION FORM")
print("#"*40)
print("\n\n")
ans='y'
eno = int(input("ENTER EMPNO TO DELETE :"))
cur.execute(query)
if cur.rowcount==0:
else:
print("%10s"%"EMPNO","%20s"%"NAME", "%15s"%"DEPARTMENT", "%10s"%"SALARY")
for row in result:
choice=input("\n## ARE YOUR SURE TO DELETE ? (Y) :")
if choice.lower()=='y':
query="delete from employee where empno={}".format(eno)
cur.execute(query)
con.commit()
print("=== RECORD DELETED SUCCESSFULLY! ===")
ans=input("DELETE MORE ? (Y) :")
Output
########################################
EMPLOYEE DELETION FORM
########################################
ENTER EMPNO TO DELETE :101

101 n1 d1 15000
## ARE YOUR SURE TO DELETE ? (Y) :y

=== RECORD DELETED SUCCESSFULLY! ===
DELETE MORE ? (Y) :n
Q. Consider the table Faculty whose columns name are
FID, Fname, Lname, Hiredate, Salary
Write a python code to fetch the details of those faculties whose salary is more than 70000.
Consider the database used is "IIT".
Ans:
con=mycon.connect(host="localhost",user="root",password="abc",database="iit")
try:
cur=con.cursor()
cur.execute("select * from Faculty where salary>70000")
result=cur.fetchall()
print(data)
except:
con.rollback()
con.close()

Database Connectivity Programs

Uploaded by

Copyright:

Available Formats

Database Connectivity Programs

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Database Connectivity Programs

Uploaded by

Copyright:

Available Formats

1) Program to connect with database and store record of employee and display records.

import mysql.connector as mycon

con = mycon.connect(host='localhost', user='root', password = "admin")

Some cursor functions

4. cur.rowcount- It returns int

import mysql.connector as mycon

con = mycon.connect(host='localhost',user='root',password="admin", database="company")

ENTER EMPNO TO SEARCH :101

import mysql.connector as mycon

con = mycon.connect(host='localhost',user='root',password="admin", database="company")

ENTER EMPNO TO UPDATE :101

## ARE YOUR SURE TO UPDATE ? (Y) :y

import mysql.connector as mycon

con = mycon.connect(host='localhost',user='root',password="admin", database="company")

ENTER EMPNO TO DELETE :101

## ARE YOUR SURE TO DELETE ? (Y) :y

You might also like