SQL Database Connectivity Programs
EX.NO.21
Aim:
To Write a MySQL connectivity program in Python to
o Create a database school
o Create a table students with the specifications – ROLLNO integer, STNAME
character(10) in MySQL and perform the following operations:
Insert two records in it
Display the contents of the table
Program:
import mysql.connector
mycon=mysql.connector.connect(host='localhost',user='root',passwd='root12345')
cursor=mycon.cursor()
st="use school;"
cursor.execute(st)
st3="create table student_details(Roll_no integer,Student_Name varchar(10));"
cursor.execute(st3)
st4="insert into student_details values({},'{}');".format(1,'Anirudh')
cursor.execute(st4)
mycon.commit()
st5="insert into student_details values({},'{}');".format(2,'Madhan')
cursor.execute(st5)
st6="select * from student_details;"
cursor.execute(st6)
data=cursor.fetchall()
for row in data:
print(row)
OUTPUT
Result:
EX.NO.22
Aim:
2. Perform all the operations with reference to table Employee through MySQL-Python
connectivity.
i)Write a python program to insert 5 records in Employee table. Take these 5 records as an input from
the user (One record at a time). Note the following to establish connectivity between Python and
MySQL: Username is root Password is 12345 The table exists in a MySQL database named company.
The table has five attributes (Emp_ID, Emp_Name, DOJ, Gender, Salary)
ii) Update name of employee in Employee table whose employee id is ‘E1001’ (Take name as an
input from the user).
EX.NO.23
AIM:
To Write a python program that display first 8 rows fetched from student table of MySQl database
student_dbl
Program:
import mysql.connector
mycon=mysql.connector.connect(host='localhost',user='root'
,passwd='root12345')
cursor=mycon.cursor()
st="use school;"
cursor.execute(st)
mycon.commit()
st="select * from student_dbl;"
cursor.execute(st)
data=cursor.fetchmany(8)
for row in data:
print(row)
OUTPUT
(11, 'ATHANG', 12, 'A', 'MALE', 90)
(12, 'ATHARVA', 12, 'B', 'MALE', 80)
(13, 'ANJALI', 12, 'C', 'FEMALE', 78)
(14, 'MEENA', 12, 'C', 'FEMALE', 79)
(15, 'MEENAKSHI', 12, 'B', 'FEMALE', 53)
(16, 'SUSHIL', 12, 'B', 'MALE', 40)
(17, 'SUMEDH', 12, 'A', 'MALE', 69)
(18, 'LUMBINI', 12, 'A', 'FEMALE', 77)
EX.NO.24
AIM:
Write a python database connectivity program that deletes record from student table of database that
have name = Meena
Program:
import mysql.connector
mycon=mysql.connector.connect(host='localhost',user='root',passwd='root12345')
cursor=mycon.cursor()
st1="use school;"
cursor.execute(st1)
st2="delete from student_dbl where NAME='{}';".format('MEENA')
cursor.execute(st2)
mycon.commit()
st3="select * from student_dbl;"
cursor.execute(st3)
mycon.commit()
data=cursor.fetchall()
for row in data:
print(row)
mycon.close()
OUTPUT
(11, 'ATHANG', 12, 'A', 'MALE', 90)
(12, 'ATHARVA', 12, 'B', 'MALE', 80)
(13, 'ANJALI', 12, 'C', 'FEMALE', 78)
(15, 'MEENAKSHI', 12, 'B', 'FEMALE', 53)
(16, 'SUSHIL', 12, 'B', 'MALE', 40)
(17, 'SUMEDH', 12, 'A', 'MALE', 69)
(18, 'LUMBINI', 12, 'A', 'FEMALE', 77)
(19, 'LOKESH', 12, 'B', 'MALE', 88)
(20, 'SUJATA', 12, 'A', 'FEMALE', 98)
