DATABSE CONNECTIVITY ( STUDENTS )
PROBLEM DEFINITION:
Write a program to connect python with MySQL using database connectivity and
perform the following operations on data in database: Insert, Fetch, Update and Delete the data.
(i) create a table student with admno, sname, gender, dob, stream, average.
(ii) Insert 5 records into the student table by accepting information from the user.
(iii) Increase the mark by 5 for those students who belong to science stream.
(iv) Display the number of male students who belong to commerce stream.
(v) Delete the records of those students whose average < 40.
PROGRAM:
import sqlite3
con=sqlite3.connect('student.db')
c=con.cursor()
def creat():
c.execute('create table stud(adno int,sname char(20),gen char(1),dob date,stream
char(10),average int)')
con.commit()
def ins():
adno=int(input("Enter admission number"))
sname=input("Enter student name")
gen=input("Enter your gender")
dob=input("enter your date of birth")
stream=input("Enter your stream")
avg=float(input("Enter your average mark"))
qu="insert into stud values({},'{}','{}','{}','{}',{})".format(adno,sname,gen,dob,stream,avg)
c.execute(qu)
con.commit()
def display():
c.execute('select * from stud')
rows=c.fetchall()
print(rows)
def update():
c.execute('update stud set average=average+5 where stream="science" ')
con.commit()
def delete():
c.execute('delete from stud where average<40')
con.commit()
def display2():
c.execute('select count(*) from stud where stream="commerce" and gen="m" ')
rows=c.fetchall()
print(rows)
ch='y'
while ch=='y':
print('1.create')
print('2. insert')
print('3. update')
print('4. delete')
print('5. display all')
print('6. display count of male commerce students')
ch1=int(input("enter your choice"))
if ch1==1:
creat()
elif ch1==2:
ins()
elif ch1==3:
update()
elif ch1==4:
delete()
elif ch1==5:
display()
elif ch1==6:
display2()
else:
print("your choice is wrong")
ch=input("Do you want to continue")
con.close()
OUTPUT:
1.create
2. insert
3. update
4. delete
5. display all
6. display count of male commerce students
enter your choice2
Enter admission number10
Enter student nameZZ
Enter your genderm
enter your date of birth10-10-2008
Enter your streamscience
Enter your average mark32
Do you want to continuey
1.create
2. insert
3. update
4. delete
5. display all
6. display count of male commerce students
enter your choice5
[(1, 'aa', 'm', '08/18/2000', 'csc', 78), (2, 'bb', 'f', '09/20/2002', 'science', 87.5), (3, 'cc', 'm',
'07/26/2003', 'commerce', 72.3), (4, 'dd', 'f', '06/21/2004', 'csc', 74), (10, 'ZZ', 'm', '10-10-2008',
'science', 32)]
Do you want to continuey
1.create
2. insert
3. update
4. delete
5. display all
6. display count of male commerce students
enter your choice3
Do you want to continuey
1.create
2. insert
3. update
4. delete
5. display all
6. display count of male commerce students
enter your choice5
[(1, 'aa', 'm', '08/18/2000', 'csc', 78), (2, 'bb', 'f', '09/20/2002', 'science', 92.5), (3, 'cc', 'm',
'07/26/2003', 'commerce', 72.3), (4, 'dd', 'f', '06/21/2004', 'csc', 74), (10, 'ZZ', 'm', '10-10-2008',
'science', 37)]
Do you want to continuey
1.create
2. insert
3. update
4. delete
5. display all
6. display count of male commerce students
enter your choice4
Do you want to continuey
1.create
2. insert
3. update
4. delete
5. display all
6. display count of male commerce students
enter your choice5
[(1, 'aa', 'm', '08/18/2000', 'csc', 78), (2, 'bb', 'f', '09/20/2002', 'science', 92.5), (3, 'cc', 'm',
'07/26/2003', 'commerce', 72.3), (4, 'dd', 'f', '06/21/2004', 'csc', 74)]
Do you want to continuey
1.create
2. insert
3. update
4. delete
5. display all
6. display count of male commerce students
enter your choice6
[(1,)]
Do you want to continue n
RESULT:
In this program the datas are successfully inserted, updated and deleted in the database.
