ested Practi cal
1. Read a text file line by line and display each word separated by a #.
Ans. # Code to write few lines in the file
fw= open("Computer.txt","\Ar")
print("Write 5 lines about Python:")
for a in range(S):
line=input( )
fw.write(line +'\n')
fw.close( )
print( )
# Code to read content of the file and to separate each word by'#'
fr= open("Computer'txt","r")
print("Reading lines from the file:")
for line in fr:
words=line.sp1it( )
for wd in words:
print(wd,'#',end=")
print( )
fr.close( )
Execution of the code:
te5 tines about Pythoa:
Computer Science
'Python
to learn
:: easy
I love Python
Reading 1l.nes frm the fif'e:
*Scieace *
*Pythoa *
*to *leara *
+ is *easy *
I* *P1rtshon *
2. Read a text file and display the number of vowels/consonants/uppercaseflowercase
characters in the file.
Ans. # Code to write few lines in the file
fw= oPen("MySelf .txt"," w ")
print("Write 3 lines in the file:")
for a in range(3):
line=input( )
fw.write(line +'\n')
fw.close( )
r&
nr""ti"als in Computer Sclence with python
@
print( )
# to read content of the file and count vowels and consonants
# to count lowercase and uppercase letters
fr= open("MySe1f.txt","r")
v-c=Ic-uc-0
data=fr.read( )
vowels= ['a'r'e'r'i'r,o'r'u']
print("Reading lines from the file:")
for ch in data:
if ch.isalpha( ):
if ch.lower( )in vowels:
V=V*1
else:
C=C*1
if ch.islower( ):
lc=lc+1
if ch.isupper( ):
uc=uc+1
print("Number of vowels:", v)
print("Number of consonants:", c)
print("Number of uppercase letters:", uc)
print("Number of lowercase letters:", lc)
fr.close( )
Exeeution of the code:
name -: Anri"t
I study 1n claas XI1
Public School-
lines from the file;
Number of voureS-st 77
cf conrlrnaltst 2*
of uppercase letlersl g
of lo$ercaso lettere: i6
>>r l
3. Remove all the lines that contain the character 'a' in a file and write it to another
file.
Ans. # Code to write few lines in the file
fw= open(l'Lines.fxt", "w")
print("Write 3 lines in the file:")
"'hTs-":l'l
fw.write(line +'\n')
fw.close( )
print( )
# to read content of the file
# copy to another file for those lines except the lines containing the letter 'a'
fr= open("Line.txt", "r")
Suggested Practica s
fw1= open("Line1.txt","w")
for line in fr:
if 'a' not in line:
fwl.write(line)
print("Copied Successfully! ")
!
fr.close( )
fw1.close( )
Execution of the code:
ne5 n
our sweet hooe
a nev friend
fly o1d bicyc].e
Copied Successfullyl !
,>, I
A code to read and display the content of the file (after copying):
# to read the content of the new file
frL= open("Line1.txt", "r")
data=fr1.read( )
print("Content of the new file:")
for ch in data:
print(ch, end=")
frl.close( )
Output:
Content of the new file:
our sweet home
my o1d bicycle
>r, I
4. Create a binary file with names and roll numbers of some students. Search for a
given roll number and display the name, if not found then display an appropriate
message.
Ans # To write ro11 numbers and names of some students in a binary file
print("Enter record of 5 students:")
import pickle
fwb= open("Name.dat","wb")
record=[]
for a in range(S):
rn=input("Enter Roll Number: ")
nm=input("Enter Name: ")
daln=[rn,nm]
record.append(data)
pickle.dump(record, fwb)
fwb.close( )
print("File is saved.")
frb= open("Name.dat","rb")
#code to search a record from the binary file
frb= open("Name.dat"r"rb")
E Practlca
found=0
s n Computer Science with Python
rn=input("Enter ro11 number to be searched: ")
print("Records of the existing file:")
try:
while Tiue:
19s=pickle.load(frb)
for a in rec:
print(a[0],' \t',a[1 ])
if(a[0]==6;'
rec=a
found=1
except:
if(found==1):
print("Record Found! !")
print(rec)
else:
print("No such record present!!:")
frb.close( )
print("Program Ends! ")
Entex rcecoxd of 5 students!
Enter Ro11 Nurrbetr 401
Eliter NatIIe: Ajay
Eater RolI Nr]l$bax. A1"2
Enter Name: Deepak
Enter Ro]-I Nua&erl A20
Enter l{ame: Ishani
Eater RolI lttr:arber: 431
Enter Name: Komal
Enter RolI l{runber: 442
Errter Nafire: Suaita
Eile is saved.
Entar ro11 an:mber to be searched: .&20
Records ef the existing fi].e:
e01 Ajay
e12 Deepak
A20 Ishani
A31 Konral
A42 Suilita
Record Foundl !
t r
'420 , ' Ishani ]
[
Procrram Endsl
>>>-l
5. Create a binary file with roll number, name and marks. Input a roll number and
update the marks.
Ans. # To display the records of the binary file before updating
import pickle
print("Records of the file before updating:")
print("Roll No.","Name\t\t","Marks in Computer")
f= open("Result.dat","rb+")
196=pickle.load(f)
for i in rec:
Suggested Practicals
print(i[0],' \t',i[1 ],' \t',i[2])
*****)F)trl{'*rFrF)t*)F***+****,F,t,t)+)t)+r+)F**t I
li f int(" )
= A program to update record in a binary file
ir'lund=0
:n=input("Enter ro11 number whose marks to be updated: ")
lor i in rec:
if i[0]==rn'
print("Current marks i'jl?l)
i[2]=lnp,ril"Enter updated marks:")
found=l-
break
-i found==1:
f.seek(0) # file pointer to update the record
pickle.dump(rec, f)
print("Record updated ") !
_l-^.
ti\c.
print("No such record found....")
= To display the records of the binary file after updating
:rint("Updated records of the file:")
:rint("Roll No.","Name\t\t","Marks in Computer")
r seek(0)
-g6=pickle.load(f)
icr i in rec:
print(i[0],' \t',i[1],' \t',i[2])
,J f Ult ('
I **+***:t)trF)F***>r****,r******)FrF******r r)
i.close( )
Execution of the code:
No. Name I'Iarks in Computer
Anan Bhatia
Anik Gupta 91
Deepak Sur a2
fndrani Das 94
Mohit Saran 9B
**+***************+**************
ro11 number whcse marks to be updated: 22
marks: 82
updated marks:92
updated!
records of the fife;
iI lJo. Name Marks in Computrer
4 Aman Bhatia 88
12 Aaik Gupta 91
22 Deepak Sur 92
25 Indrani Das 94
35 l{ohit Salan 90
**********+**********************
Ln:25 Col: 5
eru", ca s in Computer Sclence with python
@
6. Write a random number generator that generates random numbers between 1 and
5 (simulates a dice).
Ans. # a code to generate random numbers between 1 - 6 (simulating a dice)
import random
pfint("'rxx****'F'{'*+*,F)F** SimUlating a DiCg ************,rxxx+*")
while Tlue:
n=random.randint(1, 6)
if n==6:
print("Wow! You got:",n)
print("Take your turn and start the game!!!")
else:
print("Your number is:",n)
ch=input("Want to roil again .................(Enter y/N):")
if ch=='N' Or Ch=='n':
break
print("Thanks for playing !!!")
Ex*cutia:cl *f the er:c1s:
************t*+* Simulating a DiCe *t****************
Your numlDer is: 2
Want to ro11 again .... {Enter Y/N):y
Your number ist 5
to ro11 again .... (Enter Y/N) :y
Your number is: 4
hlant to ro11 again . . .. (Enter Y,/N) :y
Wow! You got: 6
Take yourturn and start the ga*e! ! !
Want to ro11 agai-n (Enter Y/N):n
Thanks for olavino
>>> I
7. Write a Python program to implement a stack using list.
Ans. # a code to irnplement a Stack using List
def Push(MyStack, sp, ele):
sP=sP+L
MyStack.append(ele)
print("Stack Pointer after Push operation:',,sp)
retuln sP
def Pop(MyStack, sp):
if sP == -1'
print("Stack Underflows")
else:
print("Popped out element:",MyStack.pop( ))
sP=sP-1
print("Stack Pointer after pop operation:",sp)
return sp
def Peek(MyStack, sp):
if sP == -1'
print("Stack Underfl ows")
else:
Suggested practrcals
E
print("Topmost element,,,M5rStack[sp])
def Display(MyStack, sp):
if sp =- -1'
print("Stack is Empty,,)
else:
print("Elements in the Stack are:,,)
print(MyStack)
# .............pain................
Stack=[] # Stack is empty
topele= -1
choice='Y'
while choice=='y':
print("Stack Operations: ")
print("l : push Operation")
print("2 : pop Operation,,)
print("3 : peek (Showing Top Element),,)
print("4 : Display Stack")
prin( )
ch=inllirrput("Enter your choice(1 to 4):,,))
if ch==L:
ele=int(rnput("Enter an element in the stack:,,))
topele = push(Stack, topele, ele)
if ch==2:
topele = pop(Stack, topele)
if ch==3:
Peek(Stack, topele)
if ch==4:
Display(Stack, topele)
choice=input("want to continue....Enter y/N or erse press Enter Key:,,)
choice=choice.upper( )
print("Program Over!,')
Execution of the code:
1 : Push Operation
2 : Pop Operation
3 , :?"k- (sholling Top Element)
4 : Display Stack
Eater your choice(l t<> 4) 1
an element in the stack:25
Stack Poi-nter after Push operation
0
Want to continue. Enter Y/N or else Press Enter Key:
Operations v
1 : Push Operation
2 : Pop Operatiori
3 ; Peek (Showi-ng Top Element)
d : Display Stack
contd.
E Practicals rn Computer Science with python
Enter your choice{1 to 4}:1
Enter at element in the stack:32
Stack Pointer after Push operation: 1
Want to continue....Enter Y,/N or else press Enter Key;y
Stack Operations:
l- l Push Operation
2 : Pop O;:eration
3 : Peek (Shorring Top Element)
4 : Display Stack
E{rter your choice(3_ to 4):1
Enter an element in the stack:L8
Stack Pointer after Push operation: Z
Bant to continue..r.Enter y,/N or else Fress Enter Key:y
Stack Operations:
1 : Push Operation
2 : Pop Otrreration
3 : Peek {Shouing Top Element}
{ : Display Stack
contd.
Enter your choiee(L to {}:4
Elements in the $taek are:
[25,32. L8]
Xlantto coEtinue,...Elrtef Yllit or eLse press Enter Eey:y
Stack Operations;
1 l Pueh Operati+n
2 : Fop Otrreration
3 : Peek {Sholring Top E1*ment}
!1 : Dispiay Stack
Enter your choice{l to 4}:2
Popped out eluent: LS
Stack Fointer after Po1l otr)eration: 1
!{ant to continue. . . .Enter y,/}it or else press Enter Hey;y
Stack Operations;
I : Fush Operation
2 : Pcp 0peration
3 : Peek {Shorring fop Elernent}
4 : Display Stack
Eater your chcice{1 to 4}:tl
Elentents in the Stack are:
[25, 32]
t to n tinue En Y /N o I else P'r En ter Key v
Stack O5:eratioas!
L l Push Operatiorr
2 : Pop Operet5.anx
3 : Peek {Sbor*ing Top E1e:aent}
4 : Display Stack
Enter ycur ch*ice(1 to 4):3
Topmost ele:uent: 32
I,{aat to eontinue Enter Y/li or else Fress Enter Heyl
Over I
8. Create a CSV file by entering user ID and password, read and search the password
for the given user ID.
Ans. # A code to write user ID and password in a csv file
import csv
fw=open("Password.csv"r "\A/", newline=")
writer=csv. writer(fw)
writer.writerow( [ "Userld","Password"] )
record=[ ]
Suggested Practicals
ch='Y'
while ch=='Y' or ch=-'y:
ud=input("Enter Userld:")
pw=input("Enter Password:")
Myl-ist=[ud, p*]
record.append(Myl-ist)
ch=input("Do you want to enter more record...? (Y/N):")
for i in record:
writer.writerow(i)
fw.close( )
# A Python code to search a record from the CSV file
fr= open("Password.csv", "r")
reader=csv.reader(fr)
found=0
uid=input("Enter Userld whose Password is to be searched:")
print("Records of the existing file are:")
next(reader)
for a in reader:
print(a)
fr.seek(0)
for a in reader:
if(a[0]==66;'
feC=a
found=1
break
if(found==1):
print("Record Found!")
print(rec)
else:
print("No such record present!!:")
fr.close( )
print("Program Ends!")
Ex*cr.lti** *t ti:* ;sg+.i:
Fassword: nisha123
Do you want to enter more record...? (Y/N):y
UserId: Jagj eet :
Password: jeet012 :
Do you want to enter more record.'.? {Y/N):y :
UserId:Atu1 i
Fassword: aG1802 :
Do you wart to enter more record...? {Y/N):n
UserId whose Password is to be searched:,fagjeet
the existing f i.le are:
'Manisha 'nisha12 3 'l
[',Jagjeet',' jeet012' ]
['Atu1', 'a81802']
Record Found!
I ,J"g j eet j eet0 12
Ends
Ln: 22 Col: 4
Database Management
L. Create a student table and insert data. Implement the following SQL commands on
the student table:
Ans. o Creating a table 'student' on MySQL Command Line
nrysql> Us* ldyDatabasel
ilatabas+ changed
mysql> Craate Table student
-> {Stu*Iil c}rar(5),
-> Narxe varchart30),
-> ltarks int(s));
. Viewin8 structure of the table 'student'
mysql> Sesc student;
I Field I Type I Nutt I ltey | $efaqlt I Extra I
I stu-ro I char(s) I ilo I mr I un-L I I
I tane l varchar(3o) I YES I I Xt LL I I
lilarka l:i{t(lr} lYEs I liluLt I I
3 rox= iu sst {S.6$ sec}
o Inserting data into the table 'student'
&ysqtr>Insert Into student
-> tl{ahit l{unar' ,811};
Ua.l-ue={'A,lO1*,
Query SX, X rcx af#ected (0"8I" sac)
mysql> Ensert fnts studeot
*> llelues{ 'AoaQ} | , ' Fiyush S{pta' . ?8) ;
Query Ol{, L ro* a{ferted {$.S1 serl
iliysq1> Insert Into stud*at
-> Valuesd'*/$3', *l{qntal Goyal',90} ;
gr.rery $K. 1 ro*r affetted tO.Sl secJ
*ysqL> trnsert Int* studr*t
-> Halue={'A/44t, n?lanish Jaia' ,88);
Query OX, L rcw aS*erted {9-S!. secJ
mysql> Insert Into student
*> Values{'A/s5','llitali Jain",9$};
Query $H. l^ ro* a*Fsct€d {9.9,1 see}
o Showing records of the table 'student'
*ysql> $e].ect * Frex st*dent;
I st*r-t$ | H*i*e E ?{*rlts !
A/ff3 f!oh:i"t ltrlanar I 84
I
s/s? Piyush 6uptr ! ?*
elsg Huntal Goyal I 9S
&16rr Xartish *l*:{.* I 88
,
4ls5 lilita?-i Jain { 94
$ rcs* in s*t {S.Sff secS
reil
Suggestederu"ti""t, p!
Performing various tasks on the above created table:
. ALTER table to add new attributes / modify data type drop
/ attribute
rysql> Atter Table studrnt
+ Add(Grade char(S));
9u..y -ff, 0 rors affected (e.84 sec)
Recordsl O DuptLcatesl O Uarrtngs: 0
nyeql> hsc sttrdtnt;
I rieta I Type I uutt I ltay 1 Defautt I Extra I
! stu-ro I eher(E) ! no I pRr I NULL I I
!tl""g lv*rehar(3o)!ves i inur-r- i i
!ilarhs ltnt(Il) lyes i iuuuu i i
lerade lchar(S) ivrs i iuuu i i
4 rors ln srt (O.00 s:c)
o UPDATE table to modify data
mysqt> Update student
-> Set tarks = 8?
-> *rcre llar{tr E ?8;
Qmry ffi, 1 roc affcetd (0.0I src)
Rore metched: I Changrdt 1 Sernlngs 0
Updated records of the table
nysqt> Select * From student;
I stu*ro I u*ma I tter*s I Gradr I
I A/CIl I t:ohtt t{umar I a4 I B I
I A/02 | Piyush Gupta I er B I
| */0s I Runtal Goyat I go i A
1
I
I A/oq | ftanish Jain I es I ts I
I A/os I tritatt ,:ain I eu ; A I
5 rows in set (8.00 see)
o ORDER BY to display data in ascending / descending order
mysqt> Stlect ri Fron stud:nt Order By ll*kr DESC;
I stu-tn I Name I ll*rks I Gradr I
I Al0$ I Hitali Jain I 94 I A I
I Al03 I Huntal Soyal I 90 i A I
I Al04 I l,lani.sh Jain t
I 88 I B I
I A/02 I Fiyush Gupta t 8? I B l
I a/or I I{ohit }tumar I ${ I B I
$ rorrls Ln set (0.00 sec)
eru"ti"ais in computer Science with Python
@
. DELETE to remove tuple(s)
SQL Query:
mysql> Delete From student
-> ldhere lolarks < 85;
Query 0l{, 1 row affected (0.01 sec)
Records of the table after performing'Delete' command:
mysql> Select * From student;
I stu-rD I Name I l,larks I Grade I
AlA2 I Piyush Gupta I 87 B
A/og I ltuntal Goyal I 90 A
A/A4 I Hanish Jai.n I 88 B
A/Os I llitati Jain I 94 A
+--
4 rovss in set (6,00 sec)
. GROUP BY and find the min, max, sum, count and average
Refer the following table created in a database to perform the various functions using
GROUP BY
Database: MyDatabasel
Table: Student
Stu-ID Name Marks Grade
A/01 Mohit Kumar 84 B
A/02 Piyush Gupta 87 B
A/03 Kuntal Goyal 90 A
A/04 Manish Jain 88 B
A/0s Mitali Jain 94 A
(i) : to count the total number of records in the table 'Student' with reference to
the column'Name'.
SQL query:
Select Courrt (Name) as 'No. of Records'
From Studen!
Output:
mysql> Use llyDatabasel
Database changed
mysql> Select Count(Name) as 'No.of Records'
-> From Student;
I No.of Records I
L row in set (0.0L sec)
Suggested Practicals
(ii) Ivlin( ): to know the lowest marks in the column 'Marks'
SQL query:
Select Min (Marks) as 'Lowest Marks'
From Studenq
Output:
mysqt> Select Flin(l{arks) as rlottest }larks'
-> From Student;
+---i--,--5----+
I Lowest I'larks I
+-*-*----*----+
I BqI
*---**
I row in set (0.00 sec)
(iii) Max( ): to know the highest marks in the column 'Marks'
SQL query:
Select Max (Marks) as 'Highest Marks'
From Student;
Output:
mysql> Select llax(Harks) as 'Highest llarks'
-> From Student;
+---*-----------+
I Highest Harks I
+---------------+
I gql
+---------------+
L row in set (0.00 sec)
(iv) Sum( ): to know the total marks of the column ,Marks':
SQL query:
Select Sum (Marks) as 'Total'
From Studen!
Output:
mysql> Setect Sum(llarks) as 'Total t
-> From Studant;
+--*---*+
I rotal I
+--*----*
I 4431
+-------+
L row in set (0.01 sec)
![! ","., :: s : C:ri.:e. Sc,ence with Python
(t') to calculate the average marks in the column 'Marks'
SQL query:
Avg (Marks) as 'Average Marks'
Select
From Student;
Output:
mysql> Select Avg(Harks) as 'Average llarks'
-> From Student;
I Average llarks I
83.6000 |
1 row in set (0.00 sec)
2. Integrate SQL with Python by importing suitable module.
Ans. Some of the tasks are mentioned below:
o To view all the tables which are available in the database 'Mydatabasel' that are
created in MySQL
# Python code to view the tables created in MySQL
import mysqi.connector as mycon
mydb=mycon. connect(host='localhost',
database='Mydatabasel',
user='root',
passwd='1234')
mycursor=mydb.cursor( )
mycursor.execute("Show tables")
print("Tables available in the database 'Mydatabasel' are:")
for i in mycursor:
print(i)
Python 3.8.1 Shell CI x
File Edk Shell Debug Options Window
3.8.1 {tags/v3.8,1:1b293b6, Dee 18 2Q19, 22:39:241 [uSC 1I.j"9]"6 32 bir (ra
tel) I on *in32
Type "he1p", 'copyright', "credits" or 'license{}' for more information
... -"1:}
RES?ART : C : /Users/Dilip Kumar Dey/DeEktop,/DFi1e. py
Tables available in the database 'Mydatabaselr are;
(rarranged value',)
('arraaged_value' , )
(tassessmentt,)
{'book', )
( tcompanyt, )
('cool_worJ-d',)
( 'country_customer' ,)
(rcricketrr)
('dept' , )
('doctor'
Contd......
Suggested Practicals
,)
(' t
rl
(' tr)
( 'stock' , )
stock_sa1e' , ) :
store' , )
(' stu_marks
(' student' ,
(' students'
(' sub_marks
(' subj ect ' ,
(' teacher' ,
(rterm_marks',)
( 'toppers' , )
'vehicles' ,'
o to view all the records of a table 'Company' created in a database 'Mydatabasel"'
Refer the given table:
A1 Britannia Kolkata
A2 Biskfarm Bhubaneswar
A3 Parle Jamshedpur
A4 PriyaGold Ranchi
A5 Anmol Delhi
# Python code to view the records of a tabie
import mysql.connector as mycon
mydb=mycon.connect(host='localhost',
database='Mydatabasel',
user='root',
passwd='1234')
mycursor=mydb.cursor( )
mycursor.execute("Select * From Company")
print("Records available in the table 'Company' are:")
for i in mycursor:
print(i)
Output on Python Script Mode:
R.ecords available in the table 'Companyr are:
('A1 t, 'Bri-tanniat, 'Kolkatat)
('A2','Biskfarm','Bhubaneswar')
(tA3', rParlerr',Jamshedpur')
('A4'r'PriyaGold'r'Ranchi')
('A5'. 'Anmo1 ', 'De1hi')
,r, I
g
e,r"ti"als in Computer Science with Python
@
. to view records of a table using fetchall( ), fetchmany( ) and fetchone( ) methods
Refer the given table 'Healthdrink' created in a database 'MyDatabasel':
No Name Title Code aqY Price Vitamin Calcium
1 Bournvita Plain 81 20 130 440 80
2 Bournvita Plain 81 30 200 440 80
J Bournvita C^hoco B1 60 380 440 80
4 Horlicks PIain H1 40 230 900 320
5 Horlicks Choco H1 10 41.0 1000 450
6 E1su1,e Choco E1 20 340 650 450
7 E1s_y1e Plain E1 30 560 650 450
8 Complan Plain C1 20 140 350 800
9 Complan Choco C1 50 270 350 800
# Python code to illustrate fetchmany( ) method
import mysql.connector as mycon
mydb=mycon. connect(host='localhost',
database='Mydatabasel',
user='root',
passwd='1234')
mlCursor=mydb.cursor( )
mycursor.execute("Se1ect * From Healthdrink")
record=mycursor.fetchmany(S)
nos=mycursor.rowcount
print("Number of rows extracted from the table: ",nos)
print("Records available in the table 'Healthdrink' are:")
for i in record:
print(i)
Output on Fython Seript IVI*de:
Number of rovs extracted from the tabl-e: 5
Records availal,le in the table 'Healthdri-nk' are:
(1, 'Bournvita', 'P1ain', '81', 20, 130, 440, 80)
12, 'Bournvita', 'P1ain', '81', 30, 200, 440, 80)
{3, 'Bournvj,ta', 'choco', 'B1', 60, 380, 440. 80)
14, 'Horficks'. 'Plain', 'Hl', 40, 23O, 900, 320)
(5, 'Horlicks', 'Choco', 'H1', 10, 4L0, 1000, 450)
.
Suggested Practicals E
to perform parameterised query from the table 'Healthdrink'
# Python code to perform parameterised query
import mysql.connector as mycon
mydb=mycon.connect(host='localhost',
database='MydatabaseL',
user='root',
passwd='1234')
mycursor=mydb.cursor( )
mycursor.execute("Select * From Healthdrink Where Name='Bournvita"')
^_n
print("Records available in the table 'Healthdrink' are:")
for i in mycursor:
print(i)
c=c*1
print("Number of records : ",c)
Output on Python Script Mode:
Records available in the table rHealthdrink' are:
(1, 'Bournvita', 'Plain', '81r, 20, 130, 440, 80)
(2, 'Bournvj-tar, 'Plain', 'Bl"', 30, 200, 440, 80)
(3, 'Bournvita', 'Choco', '8Lr, 60, 380, 440, 80)
of records : 3
Note: Similarly, you can perform more queries on tables created in a database