i n [ ' he ' , ' she ' J :

1f 1
count=count+l
print ( "frequenc y of he and she in file " , count )

OUTPUT:
- - - - - --- .J.

!frequency of he and she in file 0

PRACTICAL-9

22 of 57
22 I Page

23

AIM : Create a binary file with name and roll number. Search
for a given roll number and display the name, if not found
display appropriate message

SOFTWARE USED : Python 3.12.0 64-bit

HARDWARE USED : Keyboard, Monitor, Mouse, Hard disc,
Ram

CODE:
'""' ,H pickle
def write ():
D-11
f - o~n ( .. Studtntdeta1 l . dat · , ·w · J
Wh1l• 'Ttu• :
r • lnt (Input ( " En tor Ro ll no : • I l
n• input 1"'£nter ?la~ : • >
DI ' "" 11 "°'
DI ' N•:ne · J• n
l•r
plekle.duap(D, fl
eh• lnput l" l'.ore7 (y/nl " )
lf ch 1n · un · :
b.tfoa~
! . cloae(I
del Search ():
!ound• O
rollno• lnt (input ( '" Enter Ro ll no v hose naoe you want t o display: "' ))
f • opt!n (*3tud• ntdeta1 ll,dat .. , '" rb• )
uy :
vtul• ':'r~ :
rec•pickle. load (fl
1( rec( Roll uo· 1- rollno:
0

print (rec ( · 11a::w, • JI

lound•l
k-n•• t
~xc~pt £0t£rror :
!.close O
if !oun<l-0:
print ( "So rry no t f ound .. . • • ,
!.close()
write O
Search(I

OUTPUT:
24of 57
24 I Page

25

AIM : Create a binary file with roll number, name and marks.
Input a roll number and update the marks.
SOFTWARE USED : Python 3.12.0 64-bit
HARDWARE USED : Keyboard, Monitor, Mouse, Hard disc,
Ram

CODE:
urt rt pickle
J'"r v rito ():
t•open c• ~tudentdctall . d•t • , • wb ' J
vhtl• Tru :
r-int Unput ( * Enter Ro l l no: • ,>
n.• input ( '"Ent~r :lam.: '" )
. .1n.tUnput. ( "' £nter M.ai rk>: • ))
record• ( r, n , a)
pick,le.duap(record, t>
ch• input ( '"Do you v •nt t o enter aor•7 (y/ nJ · J
1t ch Ul 'Un ' :
br•• k
!.close()
dt-f re• d ():
t • o~n C- at.udentdet• il . d.tt • , • rb ' )
uy :
v tnl• ':'rUill' :
rte-pick.le. load ct J
print (rec)
ucntJ t E.Ortrror :
t,close()
1.U/'f upda te ():
f•os,.n ( '"atuden tdetc111 l . d•t • , • rb• • )
roll no-int (input C- &nt er R.oll n o vho :e nuie yo u v a nt t o dhpl &y : "'))
u ·y :
.. h~ le ':'tV'P :
poa•f.t•ll()
rec-pick le. lo.d (f)
1t rec(OJ - rollno:
ia- int ( input C-tnter U'pdllt• M.a t k a: '" ) )
rec(2) • i.a
t. seek (pos)
pickle.dwlp(l'ec.. 0
f p .d nt.(rec)
exc•"t ~0FError :
t .close O
write()
readO
Up(lat.-0 (J
read()

OUTPUT:

25 of 57
25 I Page

26

Enter Roll no :1
Enter Name:hitesh
Enter Marks:44
Do you want to enter more?(y/n) n
r1 . 'hitP.:;h' . 441
QUERY7:RENAME

mysql> re~ame table info to studentinfo;

Query OH, 0 rows affected (0.05 sec)

400157
40 I Page

41

QUERY7:ALTER
mysql> alter table studentinfo add phone_no int;
Query OW, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0

QUERYS:SELECT
mysql> select•from studentinfo;
+----------+----------- ♦ ---------+-------+----------- ♦

I admin _no I name I stream I marks I phone _no

+---------- +----------- +--------- +------- +----------- +
5Ll Hitesh Science {j{j 655555555
56 Tushar Science 36 658565535
59 Ritik Science 51 956565535
559 Srijan Science 30 98Ll565835
Ll59 Vaibhav Science 30 97Ll525835
Ll69 Manish Science 32 97Ll532895
Ll56 Deepanshu Science 3Ll 88Ll556285
+----------+-----------+---------+-------+-----------+
7 rows in set (0 01 sec)

QUERY9:FROM
mysq > se ect name rom stu en in o;
+-----------+
I name
+-----------+
Hitesh
Tushar
Ritik
Srijan
Vaibhav
Manish
Deepanshu
+ --- ---- - - - - +
7 rows in set (0.00 sec)

410157
4 1 I Page

42
 I name
+-----------+
Hit:esh
Tus har
Rit: ik
Sr ijan
Vaibhav
Man ish
Deepanshu
+-----------+
7 rows i n s e t: (0 . 00 s ec )

4lof 57
41 I Page

42

QUERYl0:WHERE
111y s t1 > se ect nan1e ro m stu en tin o;
+ ------- ----+
I n ame
+----------- +
Hitesh
Tushar
Ri t ik
Sri j an
Vaibhav
Manish
Deepanshu
+ ---------- -+
7 rows i n se t (0.00 sec)

QUERYll:INSERT
y r.ql:• 1 w , 1..• 1·t 1nto '.'.tudt..'nt i n ♦ o ,...-,·.l,1~--..(0056, ' lw.h ,u ' , ' ScH·nce ' , 36, &~8!.i65~35) ;
lli' l"Yo~ . 1 1·0.-, ,l ffl:.'cb..•;:l (0 D) ' , ('C)
y --. q l> in s<' 1· t i n t o s t ud<'nt1n f o v~, i.uc--..(0059, 'Rit1J.c'. ' Sc 1c-nc t• ' , 5 1, 956565535 ),
LIC' l'Y OW , l r o1, ,1 f f('C t C'CI (0 02 S('C)

y •,ql > tn•.t• i-l in t o •,tudf'n t1 nfo v,, :u('•,(0559, ' S r 1JcH1 ', 'Sc 1 r n c c- ' , 30 ,9 8 11 565835) ,
m •1· y OW, 1 1·0:1 .1fh•ch•cl (0 OJ •_. l•C)

QUERY12:UPDATE COMMAND
mysq l> update stude nt i nfo
- > set mark s =33
- > where a dmin _ no =~56;
Query O~ , 1 row a f fecte d (0 . 02 sec)
Rows matched: 1 Change d : 1 Warnings: 0

42of 57
42 I Page

43
HARDWARE USED · Keyboard, Monitor, Mouse, Hard disc,
RAM

QUERYl:DISTINCT
mysql> select distinct stream fro m studentinfo;
+--- --------+
I stream
+-----------+
Sc i ence
Commerce
Arts
Commercee
+-----------+
4 rows in set (0.01 sec)

QUERY2:BETWEEN

I ,uJn1n no n,nc ~tn.·.,n

59 R1t1M Seier.CC' 51 956565535

559 SrlJ,lr SCH'r'.(<' JO 98ij5653J5
tJS9 v.11bh~,v Sc Hrcc 30 9'/115)5835
1j (,<) ~.,n1 •,f· ScH.•r~c<.• 3:' 971J~>3/S95
1J~6 De('p.,r.-.hu Sc Hrcf' 33 SE•J~~62Z5
259 Roh.,n Con·,c-1·0• 55 9E$656J85
3~6 ~;\r'lll: .",rt', 2~ 9616'.,6:>30
565 J.,t 1n Con-,C'fCl'{' 65 936655?80

8 ro1·,.-. 1n -:.d (0 01 '>C'C)

43 of 57
43 I Page

44

QUERY3:IN
mysql> select • fron studentinfo where stream in('Science');
+---------- ♦ -----------+---------+-------+-----------+

I admin_no I name I stream I narks I phone_no

+----------+----------- ♦ ---------+-------+----------- ♦

5LI Hitcsh Science !lll G5555~555

56 Tushar Science 36 65856~535
59 Ritik Science 51 95656~535
559 Srijan Science 30 98456~835
459 Vaibhav Science 30 97452~835
469 Manish Science 32 974532895
456 Deepanshu Science 33 88455E285
+----------+-----------+---------+------- ♦ -----------+
7 rans in set (0.01 sec)

QUERY4:LIKE
mysql> sel ect • from studentinfo nhere name like 'H%';
+----------+--------+---------+-------+-----------+
I ad min_ no I name I stream I marks I phone _no
+----------+--------+---------+-------+-----------+
54 I Hitesh I Science I 44 I 655555555 I
+----------+--------+---------+-------+-----------+
1 row i n set (0.01 sec)

QUERYS:IS NULL
 ♦- - - -- - - - - - t - - - - - - - - - - - ♦-- - - - - - - - - -♦-- - - - - - ♦- - - -- - - - -- - ♦

59 Ritik Science 51 956565535

559 Sr ijan Science 38 98~565835
q59 Va ib hav Science 38 97q525s35
q69 Han i sh Science 32 97q532s9s
q56 Deepanshu Science 33 saq556285
259 Rohan Connerce 55 988656285
856 Rahul Arts 25 963656280
565 Jat in Connercee 65 936655280
t - - -- - - -- --t- - - - - - - - - -- ♦ - - - - - - - - - - - +- -- - - - -+- - - - - - - - --- +

S ro,.1s in sH (0 01 sc,,c)

43of 57
43 I Page

44

QUERY3:IN
mysql> select • from studentinfo where stream in('Science');
♦ ----------+-----------+---------+-------+-----------+
I admin_no I name I stream I narks I phone_no
♦----------♦ -----------+---------+------- ♦ -----------+

54 Hite sh Science 44 655555555

56 Tusha1· Science 36 658565535
59 Ritik Science 51 956565535
559 Srijan Science 30 98<156~835
459 Vaibhav Science 30 974 525835
469 M,rnish Science 32 974532895
<156 Oeepanshu Science' 33 834556785
♦ ----------+-----------+---------+-------+-----------+
7 rows in SC't (0.01 sec)

QUERY4:LIKE
mysql> select • from studentinfo where name like 'H\';
+----------+--------+---------+-------+-----------+
I ad min_no I name I stream I marks I phone_no
+----------+--------+---------+-------+-----------+
54 I Hitesh I Science I 44 I 655555555 I
+----------+--------+---------+-------+-----------+
1 row in set (0.0 1 sec)

QUERYS:IS NULL

44of 57
44 I Page

45

nysql> select• Fron studentinfo where narks is n Jt null;

+----------+-----------+-----------+-------+-----------+
I admi n_n o I name I strean I mark s I phone_no I
+----------+-----------+-----------+-------+-----------+
440157
44 I Page

45

my s q l> select • from studentinfo where marks is no t null ;

+----------+-----------+-----------+-------+-----------+
I admin_no I name I strean I rnarl< s I phone _ no
+---------- +-----------+-----------+------- +-----------+
5'-1 Hitesh Science 655555555
56
59
Tushar
Ritik
Science
Science
36
51
658565535
956565535
""
559 Srijan Science 30 98ll 565835
ll59 Vaibhav Science 30 97ll525835
ll69 M,rni s h Sci e nce 32 97ll 532895
1156 Oeep,,ns hu Sci e nce 33 884 556285
259 Roh,,n Cor,nerce 55 988656285
856 Rahul Arts 25 963656280
565 Jatin Connercee 65 936655280
t ---------- +----------- +----------- +------- +----------- +
10 rows in set (0 . 00 sec)

QUERY6:0RDER BY
ny•,c1l> •,rlrct • fro n ~tLJ(t~n t1 r f o or,l<•r t,y n ,,t·k~ <l<••,c,
t ---------- + ----------- ♦ ----------- + ------- ♦ ----------- ♦

I admin _no I na~c I strcan I nark• I phonc _no

♦ ---------- + ----------- ♦----------- ♦ -------•----------- ♦
565 J<, tin Cor:u~c rec e 65 936655280
259 Roh~rn Cor:u;1ercc 55 988656285
59 llitik Sci<-ncc 51 %6565535
5•l Hitcsh Science ,,,1 655555555
56 Tushar Sc icncc 36 658565535
ij 56 Occp~,nshu Sc icncc 33 88ij556285
ij 69 Man i sh Science 32 97ij532895
559 S1· i j~rn Science 30 98<1565835
<159 V,\ ibh.\v Sci<- ncc 30 9'/11525835
856 Ra hul Arts 25 963656280
• ---------- + ---·-------- + ----------- + ------- + ----------- ♦
10 rows i n set (0 00 sec)

QUERY7:GROUP BY

4Sof 57
4S I Page

46

mysql> select sum(marks) fro m st udent info group by stream;

+------------+
I sum(marks) I
+------------+
256
irr.po rt mysql.connect:.o r

I Connect to the database

db connection= mysql .connector.connectl
- host= "l o cal ho st",
user-"' r oot " ,,
passvd-;" r oot ",
database•"practical•

Create a cursor object

cursor• db_connect:ion.cursor()
t Update the record in the table
sql • "UPDATE studentin !o SET marks • 56 WHERE admin_no=54"
cursor.execut:e(sql)

t Commit the changes

db_connect:ion.commit: II
t Print the number o! rows a!!ected
print (cursor.rowcount:, " record(s) affec ted" )

OUTPUTS:

1 record(s) affected
-· \----- ,··---

53 ol 57
53 I Page

S4

.tdn1n no •,t l'P,tr1 n,lrk• . phon,• _ ro

t-
SIi H l t l..-,h Sc l<'nCP '>&
56 Tl ~.h,u· ScicncC' 36
',9 f.htHc Sc it'nc<' 51
559 Sri J.Ul Sc1l'ncr 30 CJSIJ~16'.)S~S
lJ!J(J V,1ibh.,v Sci{'ncc 30 9·1,1~;,s.:ns
11 69 M,,n1-:.h Sc 1l'nc1..~ 3;> 9711532395
1J 56 Ocep.1n~hll Science 33 83LJ556 ✓ 35
]59 Ach.rn Conrif'l'CC' ',', 983656/35
S56 R,1hu l )5 963656)30
Sfl~ J<1t in (onn<'rCrl' 9)()(15S)20
t -

1 0 ro,·,•~ ·.et ( 0 00 ·. ,•c)

"'
I create a cursor object
cursor• db_connection.cursor()
I Delete a record from the t a ble
sql = " DE.LE.TE.fro m studentinfo WHERE. name= ' s rijan ' ; "
cursor.execute(sql)

I Commi t the changes

db_connection.commit()

J Close the cursor and data b ase connection

cursor.close()
db_connection.close()

55 of 57
55 I Page

56

OUTPUT:

mysq l > select• from stude ntinfo;

t ---------- +----------- +----------- +---- - -- +----------- l
I admin _no I name I stream I narks I phcne _no
+---------- +----------- +----------- +---- - - -+ ----------- +
54 Hitesh Science 56 65~555555
56 Tushar Science 36 65c565535
59 Ritik Science 51 95E565535
IJ59 Vaibhav Science 30 974525835
IJ69 Manish Science 32 974532895
IJ56 Deepanshu Science 33 884556285
259 Rohan Commerce 55 988656285
856 Rahul Ar t s 25 963656280
565 Jatin Connercee 65 93E655280
+----------+-----------+-----------+-------t-----------t
9 rows i n set (0.00 sec)

56 of57
56 I Page