GRADE 12 PRACTICAL 19

 '''WAP using Python-MySQL connectivity to connect to an existing table(created on MySQL) and perform the following :

1 DISPLAY DETAILS OF FEMALE PASSENGERS WHO ARE MORE THAN 30 YEARS OLD and ALSO THE TOTAL NUMBER OF SUCH PASSENGERS

2.UPDATE TRAIN NO OF A PARTICULAR PASSENGER

3.DELETE RECORD WHERE PASSENGER NAME HAS SHARMA

TABLE : PASSENGER, DATABASE: PRACT19

+------------------------------------------------------+

| PNR | TNO   |    PNAME   | GENDER | AGE | TRAVELDATE |

+------------------------------------------------------+

|P003 | 12015 |S TIWARI    | FEMALE | 22  | 2018-11-10 |

|P004 | 12030 |S K SAXENA  |  MALE  | 42  | 2018-10-12 |

|P005 | 12030 |S SAXENA    | FEMALE | 35  | 2018-10-12 |

|P008 | 12030 |J K SHARMA  |  MALE  | 65  | 2018-05-09 |

|P009 | 12030 |R SHARMA    | FEMALE | 58  | 2018-05-09 |

+------------------------------------------------------+  '''

DO NOT WRITE THE GREY PART IN THE NOTE BOOK. JUST PUT A PIC OF THE TABLE IN THE RECORD.

CREATE DATABASE PRACT19; USE PRACT19; CREATE TABLE PASSENGER ( PNR CHAR(4) PRIMARY KEY, TNO INT, PNAME VARCHAR(30), GENDER CHAR(6), AGE INT, TRAVELDATE DATE );

INSERT INTO PASSENGER VALUES ('P003', 12015, 'S TIWARI', 'FEMALE', 22, '2018-11-10'), ('P004', 12030, 'S K SAXENA', 'MALE', 42, '2018-10-12'), ('P005', 12030, 'S SAXENA', 'FEMALE', 35, '2018-10-12'), ('P008', 12030, 'J K SHARMA', 'MALE', 65, '2018-05-09'), ('P009', 12030, 'R SHARMA', 'FEMALE', 58, '2018-05-09');

WRITE ONLY THE BELOW PYTHON CODE

import mysql.connector as my conn = my.MySQLConnection(host='localhost', user='root', password='123456789', database='lab19') if conn.is_connected(): print('Connected to MySQL successfully!') else: print('Connection is unsuccessful!') cur = conn.cursor(buffered=True) # DISPLAY DETAILS OF FEMALE PASSENGERS WHO ARE MORE THAN 30 YEARS OLD print('''To display the details of female passengers who are more than 30 years old and the count of such passengers:''') age = 30 sex = 'female' cur.execute('SELECT * FROM passenger WHERE age > %s AND gender = %s', (age, sex)) recs = cur.fetchall() count = cur.rowcount for a, b, c, d, e, f in recs: print(a, b, c, d, e, f, sep=' | ') print('Total no. of such passengers:', count) cur.reset() print() # UPDATE TRAIN NO OF A PARTICULAR PASSENGER print('To update train no. of a particular passenger:') pnr = input('Enter your booking PNR: ') cur.execute('SELECT * FROM passenger WHERE pnr = %s', (pnr,)) rec = cur.fetchone() if not rec: print('No such record found!') else: print('Passenger found:', rec) newtno = int(input('Enter new Train No: ')) cur.execute('UPDATE passenger SET tno = %s WHERE pnr = %s', (newtno, pnr)) print('Details updated!') cur.reset() conn.commit() print() # DELETE DATA WITH 'SHARMA' print("To delete data with 'SHARMA':") cur.execute('SELECT * FROM passenger WHERE pnr = %s', (pnr,)) rec = cur.fetchone() if not rec: print('No such record found!') else: cur.execute('DELETE FROM passenger WHERE pname LIKE "%sharma%"') print('Data deleted!') cur.reset() conn.commit() print() # DISPLAY ALL RECORDS cur.execute('SELECT * FROM passenger') recs = cur.fetchall() for a, b, c, d, e, f in recs: print(a, b, c, d, e, f, sep=' | ') # Close resources cur.close() conn.close()