GRADE 12 : PRACTICAL 20

 WAP using Python-MySQL connectivity to connect to the given table and perform the following :

DISPLAY DESIRED NUMBER OF RECORDS UPDATE THE TRAINS HAVING START AS 'PUNE JUNCTION' TO 'MUMBAI' . DELETE DATA WHOSE END IS 'HABIBGANJ' FINALLY DISPLAY ALL THE ROWS & THE NO. OF ROWS

TABLE : TRAIN ,     DATABASE : PRACT20

 TNO  | TNAME                | START              | END 

-----------------------------------------------------------------------

11096 | AHIMSA EXPRESS       | PUNE JUNCTION      | AHMEDABAD JUNCTION

12015 | AJMER SHATABDI       | NEW DELHI          | AJMER JUNCTION

16513 | PUNE HBJ SPECIAL     | PUNE JUNCTION      | HABIBGANJ

13005 | AMRITSAR MAIL        | HOWRAH JUNCTION    | AMRITSAR JUNCTION  

12002 | BHOPAL SHATABDI      | NEW DELHI          | HABIBGANJ

---------------------------------------------------------------------- '''

THIS PART IS NOT TO BE WRITTEN IN RECORD

CREATE DATABASE PRACT20; USE PRACT20; CREATE TABLE TRAIN ( TNO INT PRIMARY KEY, TNAME VARCHAR(50), START VARCHAR(50), END VARCHAR(50) );

INSERT INTO TRAIN VALUES (11096, 'AHIMSA EXPRESS', 'PUNE JUNCTION', 'AHMEDABAD JUNCTION'), (12015, 'AJMER SHATABDI', 'NEW DELHI', 'AJMER JUNCTION'), (16513, 'PUNE HBJ SPECIAL', 'PUNE JUNCTION', 'HABIBGANJ'), (13005, 'AMRITSAR MAIL', 'HOWRAH JUNCTION', 'AMRITSAR JUNCTION'), (12002, 'BHOPAL SHATABDI', 'NEW DELHI', 'HABIBGANJ');

WRITE IT IN THE RECORD

import mysql.connector as my

import sys con=my.connect(host='localhost', user='root', password='1234', database='lab20', use_pure=True) #use_pure when set to True ensures that the connection is made purely using python if con.is_connected(): print('Connected To MySQL Server Successfully') else: sys.exit('Connection To MySQL Server Failed!') cur=con.cursor()

print('''DISPLAY DESIRED NUMBER OF RECORDS''') cur.execute("select * from train") n=int(input('Enter the number of records to fetch: ')) recs=cur.fetchmany(n) # fetches 'n' records for no,name,start,end in recs: print(no,name,start,end, sep=' | ') print() cur.reset() print("""UPDATE THE TRAINS HAVING START AS 'PUNE JUNCTION' TO 'MUMBAI'""")

cur.execute("select * from train where start='PUNE JUNCTION'")

rec=cur.fetchone() if not rec:

    print('No such record found' )

else:

    cur.execute("update train set start = 'MUMBAI' where start = 'PUNE JUNCTION'")     con.commit() print("""DELETE DATA WHOSE END IS 'HABIBGANJ'""")

cur.execute("select * from train where end='HABIBGANJ'")

rec=cur.fetchone() if not rec:

    print('No such record found' )

else:

    cur.execute("delete from train where end='HABIBGANJ'")     con.commit() print('''DISPLAY ALL THE ROWS & THE NO. OF ROWS''') print('TNO', 'TNAME', 'START', 'END',sep=' | ') cur.execute("select * from train") recs=cur.fetchall() for no,name,start,end in recs: print(no,name,start,end, sep=' | ') print() print('Total Number of Rows: ', cur.rowcount)

cur.close()

conn.close()