1.
WAP to store students’ details like admission number, roll number, name and
percentage in a dictionary and display information on the basis of admission number.
record = dict ()
i=1
n= int (input ("How many records u want to enter: "))
while(i<=n):
Adm = input("Enter Admission number: ")
roll = input("Enter Roll Number: ")
name = input("Enter Name :")
perc = float(input("Enter Percentage : "))
t = (roll,name, perc)
record[Adm] = t
i=i+1
Nkey = [Link]()
for i in Nkey:
print("\nAdmno- ", i, " :")
r = record[i]
print("Roll No\t", "Name\t", "Percentage\t")
for j in r:
print(j, end = "\t")
2. Read a text file line by line and display each word separated by a #.
filein = open("[Link]",'r')
line =" "
while line:
line = [Link]()
#print(line)
for w in line:
if w == ' ':
print('#',end = '')
else:
print(w,end = '')
[Link]()
3. Write a Python code to find the size of the file in bytes, the number of lines,
number of words and no. of character.
import os
lines = 0
words = 0
letters = 0
filesize = 0
for line in open("[Link]"):
lines += 1
letters += len(line)
# get the size of file
filesize = [Link]("[Link]")
# A flag that signals the location outside the word.
pos = 'out'
for letter in line:
if letter != ' ' and pos == 'out':
words += 1
pos = 'in'
elif letter == ' ':
pos = 'out'
print("Size of File is",filesize,'bytes')
print("Lines:", lines)
print("Words:", words)
print("Letters:", letters)
4. Write a python program to implement a stack using a list data-structure.
def isempty(stk):
if stk==[]:
return True
else:
return False
def push(stk,item):
[Link](item)
top=len(stk)-1
def pop(stk):
if isempty(stk):
return "underflow"
else:
item=[Link]()
if len(stk)==0:
top=None
else:
top=len(stk)-1
return item
def peek(stk):
if isempty(stk):
return "underflow"
else:
top=len(stk)-1
return stk[top]
def display(stk):
if isempty(stk):
print('stack is empty')
else:
top=len(stk)-1
print(stk[top],'<-top')
for i in range(top-1,-1,-1):
print(stk[i])
#Driver Code
def main():
stk=[]
top=None
while True:
print('''stack operation
[Link]
[Link]
[Link]
[Link]
[Link]''')
choice=int (input('enter choice:'))
if choice==1:
item=int(input('enter item:'))
push(stk,item)
elif choice==2:
item=pop(stk)
if item=="underflow":
print('stack is underflow')
else:
print('poped')
elif choice==3:
item=peek(stk)
if item=="underflow":
print('stack is underflow')
else:
print('top most item is:',item)
elif choice==4:
display(stk)
elif choice==5:
break
else:
print('invalid')
exit()
main()
employee=[]
def push():
empno=input("Enter empno ")
name=input("Enter name ")
sal=input("Enter sal ")
emp=(empno,name,sal)
[Link](emp)
def pop():
if(employee==[]):
print("Underflow / Employee Stack in empty")
else:
empno,name,sal=[Link]()
print("poped element is ")
print("empno ",empno," name ",name," salary ",sal)
def traverse():
if not (employee==[]):
n=len(employee)
for i in range(n-1,-1,-1):
print(employee[i])
else:
print("Empty , No employee to display")
while True:
print("1. Push")
print("2. Pop")
print("3. Traversal")
print("4. Exit")
ch=int(input("Enter your choice "))
if(ch==1):
push()
elif(ch==2):
pop()
elif(ch==3):
traverse()
elif(ch==4):
print("End")
break
else:
print("Invalid choice")
OUTPUT
=============
1. Push
2. Pop
3. Traversal
4. Exit
Enter your choice 1
Enter empno 101
Enter name Ramesh
Enter sal 34000
1. Push
2. Pop
3. Traversal
4. Exit
===================connectivity==================
33. Write a program to connect Python with MySQL using database connectivity and
perform the following operations on data in database: Fetch, Update and delete
the data.
1. CREATE A TABLE
SOLUTION
import [Link]
demodb = [Link](host="localhost", user="root",
passwd="computer", database="EDUCATION")
democursor=[Link]( )
[Link]("CREATE TABLE STUDENT (admn_no int primary key,
sname varchar(30), gender char(1), DOB date, stream varchar(15), marks
float(4,2))")
2. INSERT THE DATA
SOLUTION
import [Link]
demodb = [Link](host="localhost", user="root",
passwd="computer", database="EDUCATION")
democursor=[Link]( )
[Link]("insert into student values (%s, %s, %s, %s, %s, %s)",
(1245, 'Arush', 'M', '2003-10-04', 'science', 67.34))
[Link]( )
3. FETCH THE DATA
SOLUTION
import [Link]
demodb = [Link](host="localhost", user="root",
passwd="computer", database="EDUCATION")
democursor=[Link]( )
[Link]("select * from student")
for i in democursor:
print(i)
4. UPDATE THE RECORD
SOLUTION
import [Link]
demodb = [Link](host="localhost", user="root",
passwd="computer", database="EDUCATION")
democursor=[Link]( )
[Link]("update student set marks=55.68 where admn_no=1356")
[Link]( )
5. DELETE THE DATA
SOLUTION
import [Link]
demodb = [Link](host="localhost", user="root", passwd="computer",
database="EDUCATION")
democursor=[Link]( )
[Link]("delete from student where admn_no=1356")
[Link]( )
6. Program to connect with database and store record of employee and display records.
import [Link] as mycon
con = [Link](host='localhost',
user='root',
password="root")
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"%"SALARY")
for row in result:
print("%10s"%row[0],"%20s"%row[1],"%15s"%row[2],"%10s"%row[3])
elif choice==0:
[Link]()
print("## Bye!! ##")
OUTPUT:
1. ADD RECORD
2. DISPLAY RECORD
0. EXIT
Enter Choice :1
Enter Employee Number :101
Enter Name :RAMESH
Enter Department :IT
Enter Salary :34000
## Data Saved ##
1. ADD RECORD
2. DISPLAY RECORD
0. EXIT
Enter Choice :2
EMPNO NAME DEPARTMENT SALARY
101 RAMESH IT 34000
7 : Program to connect with database and search employee number in table employee and
display record, if empno not found display appropriate message.
import [Link] as mycon
con = [Link](host='localhost', user='root', password="root", 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) :")
8. Perform all the operations (Insert, Update, Delete, Display) with reference to table ‘student’
through MySQL-Python connectivity
import [Link] as ms
db=[Link](host="localhost", user="root", passwd="root", database="class_xii" )
#cn=[Link]()
def insert_rec():
try:
while True:
rn=int(input("Enter roll number:"))
sname=input("Enter name:")
marks=float(input("Enter marks:"))
gr=input("Enter grade:")
[Link]("insert into student values({},'{}',
{},'{}')".format(rn,sname,marks,gr))
[Link]()
ch=input("Want more records? Press (N/n) to stop entry:")
if ch in 'Nn':
print("Record Inserted ")
break
except Exception as e:
print("Error", e)
def update_rec():
try:
rn=int(input("Enter rollno to update:"))
marks=float(input("Enter new marks:"))
gr=input("Enter Grade:")
[Link]("update student set marks={},gr='{}' where
rn={}".format(marks,gr,rn))
[Link]()
print("Record Updated .... ")
except Exception as e:
print("Error",e)
def delete_rec():
try:
rn=int(input("Enter rollno to delete:"))
[Link]("delete from student where rn={}".format(rn))
[Link]()
print("Record Deleted ")
except Exception as e:
print("Error",e)
def view_rec():
try:
[Link]("select * from student")
records = [Link]()
for record in records:
print(record)
#[Link]()
#print("Record...")
except Exception as e:
print("Error",e)
db = [Link]( host="localhost", user="root", passwd="root", database="class_xii" )
cn = [Link]()
while True:
print("MENU\n1. Insert Record\n2. Update Record \n3. Delete Record\n4. Display Record
\[Link]")
ch=int(input("Enter your choice<1-4>="))
if ch==1:
insert_rec()
elif ch==2:
update_rec()
elif ch==3:
delete_rec()
elif ch==4:
view_rec()
elif ch==5:
break
else:
print("Wrong option selected")
=====================Database========================
1. Create a table EMPLOYEE with constraints
SOLUTION Step-1 Create a database:
CREATE DATABASE Bank;
Step-2 Display the databases
SHOW DATABASES;
Step-3: Enter into database
Use Bank;
Step-4: Create the table EMPLOYEE
create table Employee(Ecode int primary key,Ename varchar(20) NOT NULL,
Dept varchar(15),City varchar(15), sex char(1), DOB date, Salary float(12,2));
2. Insert data into the table
SOLUTION
insert into Employee values(1001,"Atul","Production","Vadodara","M","1992
10-23",23000.50);
Query OK, 1 row affected (0.11 sec)
Note: Insert more rows as per above insert command.
3. Add a new column in a table.
SOLUTION ALTER TABLE EMPLOYEE ADD address varchar(50);
4. Change the data-type and size of an existing column.
SOLUTION ALTER TABLE EMPLOYEE MODIFY city char(30);
5. Write SQL queries using SELECT, FROM, WHERE clause based on
EMPLOYEE table.
SOLUTION
5.1. List the name of female employees in EMPLOYEE table.
Solution:- SELECT Ename
FROM EMPLOYEE
WHERE sex=’F’;
5.2. Display the name and department of those employees who work in surat
and salary is greater than 25000.
Solution:- SELECT Ename, Dept
FROM EMPLOYEE
WHERE city=’surat’ and salary > 25000;
5.3. Display the name of those female employees who work in Mumbai.
Solution:- SELECT Ename
FROM EMPLOYEE
WHERE sex=’F’ and city=’Mumbai’;
5.4. Display the name of those employees whose department is marketing or
RND.
Solution:- SELECT Ename
FROM EMPLOYEE
WHERE Dept=’marketing’ OR Dept=’RND’;
5.5. List the name of employees who are not males.
Solution:- SELECT Ename, Sex
FROM EMPLOYEE
WHERE sex!=’M’;
6. Queries using DISTINCT, BETWEEN, IN, LIKE, IS NULL, ORDER BY, GROUP BY, HAVING
6.1. Display the name of departments. Each department should be displayed once.
SOLUTION
SELECT DISTINCT(Dept)
FROM EMPLOYEE;
6.2. Find the name and salary of those employees whose salary is between 35000
and 40000.
SOLUTION
SELECT Ename, salary
FROM EMPLOYEE
WHERE salary BETWEEN 35000 and 40000;
6.3. Find the name of those employees who live in guwahati, surat or jaipur city.
SOLUTION
SELECT Ename, city
FROM EMPLOYEE
WHERE city IN(‘Guwahati’,’Surat’,’Jaipur’);
6.4. Display the name of those employees whose name starts with ‘M’.
SOLUTION
SELECT Ename
FROM EMPLOYEE
WHERE Ename LIKE ‘M%’;
6.5. List the name of employees not assigned to any department.
SOLUTION
SELECT Ename
FROM EMPLOYEE
WHERE Dept IS NULL;
6.6. Display the list of employees in descending order of employee code.
SOLUTION
SELECT *
FROM EMPLOYEE
ORDER BY ecode DESC;
6.7. Find the average salary at each department.
SOLUTION
SELECT Dept, avg(salary)
FROM EMPLOYEE
group by Dept;
6.8. Find maximum salary of each department and display the name of that
department which has maximum salary more than 39000.
SOLUTION
SELECT Dept, max(salary)
FROM EMPLOYEE
group by Dept
HAVING max(salary)>39000;
7. Queries for Aggregate functions- SUM( ), AVG( ), MIN( ), MAX( ), COUNT( )
7.1. Find the average salary of the employees in employee table.
Solution:- SELECT avg(salary)
FROM EMPLOYEE;
7.2. Find the minimum salary of a female employee in EMPLOYEE table.
Solution:- SELECT Ename, min(salary)
FROM EMPLOYEE
WHERE sex=’F’;
7.3. Find the maximum salary of a male employee in EMPLOYEE table.
Solution:- SELECT Ename, max(salary)
FROM EMPLOYEE
WHERE sex=’M’;
7.4. Find the total salary of those employees who work in Guwahati city.
Solution:- SELECT sum(salary)
FROM EMPLOYEE
WHERE city=’Guwahati’;
7.5. Find the number of tuples in the EMPLOYEE relation.
Solution:- SELECT count(*)
FROM EMPLOYEE;