SAMPLE TABLE:-
33. PROGRAM TO SEARCH PARTICULAR RECORDS IN MY SQL TABLE USING
PYTHON. IF NOT FOUND, DISPLAY APPROPRIATE MESSAGE .
Input:-
import mysql.connector
con=mysql.connector.connect (host='localhost',
user='root',
passwd='12345',
database='file',
charset='utf8')
if con.is_connected():
print("Connected..")
else:
print("Not connected..")
cur=con.cursor ()
while True:
found=0
s=input(“Enter Stream.. “)
query="Select * from student where Stream=’%s’”%s
cur.execute (query)
data=cur.fetchall ()
for i in data:
print (i)
found=1
if found==0
print(“no record found.. “)
ch=input("Do you want to search more records or not :”)
if ch in 'Nn':
break
Output:-
Connected..
Enter Stream. . non medical
(2, 'Nisha', 100, 'Non Medical')
(5, 'Soniya', 75, 'Non Medical')
(7, Shilpa', 5, 'Non Medical')
Do you want to search more records or not: y
Enter Stream..medical
No record found..
34. PROGRAM TO UPDATE THE PARTICULAR RECORD(S). IF NOT FOUND
DISPLAY APPROPRIATE MESSAGE .
Input:-
import mysql.connector
con=mysql.connector.connect (host='localhost',
user='root',
passwd='12345',
database='file',
charset='utf8')
cur=con.cursor ()
st=input ("Enter Name of new stream:")
r=int(input("Enter the roll for which you want to update the stream:”))
query="Update Student SET Stream='%s' where Rno=%s" % (st, r)
cur.execute (query)
con.commit()
print ("Record Updated Sucessfully..")
Output:-
Enter Name of new stream: Medical
Enter the roll for which you want to update the stream : 2
Record Updated Sucessfully..
35. PROGRAM TO DELETE THE RECORDS FROM MY SQL TABLE USING
PYTHON.
Input:-
import mysql.connector
con=mysql.connector.connect (host='localhost',
user='root',
passwd='12345',
database='file',
charset='utf8')
cur=con.cursor ()
r=int(input(“Enter the roll no whose record you want to delete : ))
query=”Delete from Student where Rno=%s” %r
cur.execute (query)
con.commit()
print (“Record deleted successfully…”)
Output:-
Enter the roll no whose record you want to delete :4
Record deleted successfully…
36. INTERFACE PYTHON WITH MY SQL INSERTING DATA IN TABLEFROM
PYTHON TO MY SQL.
Input:-
import mysql.connector
con=mysql.connector.connect (host=’localhost’,
user=’root’,
passwd=’12345’,
database=’file’,
charset=’utf8’)
If con.is_connected():
Print(“Connected..”)
else:
Print(“Not connected..”)
cur=con.cursor ()
while True:
r=int(input("Enter Roll No. :"))
n=input("Enter Name : ")
m=int (input("Enter Marks : "))
s=input ("Enter Stream : ")
query="insert into Student values\
(%s, '%s', %s, '%s') "% (r,n,m,s)
print (query)
cur.execute (query)
con.commit()
ch=input(“More (Y/N) “) if ch in ‘Nn’:
break
print (“Records inserted successfully…”)
cur.execute(“Select * from Student”)
data=cur.fetchall()
for I in data:
print (i)
Output:-
Connected..
Enter Roll No. : 1
Enter Name: Neha
Enter Marks :100
Enter Stream: Commerce
insert into Student values(1, ‘Neha’, 100, ‘Commerce’)
More (Y/N) Y
Enter Roll No. : 2
Enter Name: Nisha
Enter Marks :100
Enter Stream Non Medical
insert into Student values(2, ‘Nisha’, 100, ‘Non Medical’)
More (Y/N) Y
Enter Roll No. : 3
Enter Name: pooja
Enter Marks :65
Enter Stream: Humanities
insert into Student values(3, Pooja’, 65, ‘Humanities’)
More (Y/N) Y
Enter Roll No. : 4
Enter Name: priyanka
Enter Marks :87
Enter Stream: Commerce
insert into Student values(4, 'Priyanka', 87, 'Commerce')
More (Y/N) Y
Enter Roll No. : 5
Enter Name: Soniya
Enter Marks :75
Enter Stream: Non Medical
insert into Student values(5, ‘Soniya’, 75, ‘Non Medical’)
More (Y/N) Y
Enter Roll No. :6
Enter Name: Kritika
Enter Marks :32
Enter Stream: Humanities
insert into Student values(6, Kritika’, 32, ‘Humanities’)
Enter Roll No. : 7
Enter Name: Shilpa
Enter Marks :5
Enter Stream: Non Medical
insert into Student values(7, Shilpa, 5, ‘Non Medical’)
More (Y/N) N
Records inserted successfully...
(1, 'Neha', 100, 'Commerce')
(2, 'Nisha', 100, 'Non Medical')
(3, Pooja', 65, 'Humanities')
(4, 'Priyanka', 87, 'Commerce')
(5, 'Soniya', 75, 'Non Medical')
(6, Kritika', 32, 'Humanities')
(7, Shilpa, 5, 'Non Medical')
