GRADE 12 : PRACTICAL 21

 '''WAP using Python-MySQL connectivity to connect to the given DB and perform the queries for the following :

> UPDATE NO. OF PARTICIPANTS AFTER ACCEPTING THE ACTIVITY NAME

> FINALLY DISPLAY ALL THE DATA AND THE TOTAL NUMBER OF ROWS

TABLE : ACTIVITY

 ACODE| ANAME         | STADIUM     | NO | PRIZE | SCHEDULE_DATE  

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

1001  | RELAY 100M    | STAR ANNEX  | 16 | 10000 | 2004-01-23

1002  | HIGH JUMP     | STAR ANNEX  | 10 | 12000 | 2003-12-12

1003  | SHOTPUT       | SUPER POWER | 12 | 8000  | 2004-02-14

1005  | LONG JUMP     | STAR ANNEX  | 12 | 9000  | 2004-01-01

1008  | DISCUS THROW  | SUPER POWER | 10 | 15000 | 2004-03-19

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

Do not write this part : 

create table activity(

acode int primary key,
aname varchar(50),
stadium varchar(50),
no int,
prize int,
scheduled_date date);


insert into activity values
(1001,'RELAY 100M','STAR ANNEX',16,10000,'2004-01-23'),
(1002,'HIGH JUMP','STAR ANNEX',10,12000,'2003-12-12'),
(1003,'SHOTPUT','SUPER POWER',12,8000,'2004-02-14'),
(1005,'LONG JUMP','STAR ANNEX',12,9000,'2004-01-01'),
(1008,'DISCUS THROW','SUPER POWER',10,15000,'2004-03-19');

import mysql.connector as my

conn = my.MySQLConnection(host='localhost', user='root', password='123456789', database='lab21')
if conn.is_connected():
        print('Connected to MySQL successfully!')
else:
        print('Connection is unsuccessful!')
cur = conn.cursor(buffered=True)
print()

#UPDATE NO. OF PARTICIPANTS AFTER ACCEPTING THE ACTIVITY NAME
print('TO UPDATE NO. OF PARTICIPANTS AFTER ACCEPTING THE ACTIVITY NAME:')
aname=input('Enter name of Activity to be updated:')
cur.execute('select * from activity where aname=%s',(aname,))
rec=cur.fetchall()
if rec:
    print('Record found:',rec)
    no=int(input('Enter new participant count:'))
    cur.execute('update activity set no=%s where aname=%s',(no,aname))
    print('Data updated successfully!')
else:
    print('No such record found!')
conn.commit()
cur.reset()
print()

#DISPLAY ALL THE DATA AND THE TOTAL NUMBER OF ROWS
print('DISPLAY ALL THE DATA AND THE TOTAL NUMBER OF ROWS:')
cur.execute('select * from activity')
recs=cur.fetchall()
if recs:
    for a,b,c,d,e,f in recs:
        print(a,b,c,d,e,f, sep='| ')
else:
    print('Table empty! No records exist.')

count=cur.rowcount
print('Row count:', count)

cur.close()
conn.close()

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()