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