PYTHON-SQL CONNECTIVITY PROGRAMS
PROGRAM 1: Write a program to connect python with mysql using database
connectivity and perform the following operations in database:
A)Create the table
B) Insert the records
C) Display the records using fetchall function [table name is student]
SOURCE CODE:
import mysql.connector
# Establish a connection to the MySQL server
mycon = mysql.connector.connect(
host='localhost',
user='root',
password='password',
database='School')
if mycon.is_connected():
print('Database connected')
# Create a cursor object
cursor = mycon.cursor()
# Create the table
sql = '''
CREATE TABLE IF NOT EXISTS STUDENT (
ID INT PRIMARY KEY,
Name CHAR(10),
Stream CHAR(10),
AvgMark FLOAT,
Grade CHAR(2),
Class CHAR(5))'''
cursor.execute(sql)
# Insert records
sql = '''
INSERT INTO STUDENT (ID, Name, Stream, AvgMark, Grade, Class)
VALUES
(1, 'Karan', 'Medical', 78.5, 'B', '128'),
(2, 'Divakar', 'Commerce', 89.2, 'A', '11C'),
(3, 'Divya', 'Commerce', 68.6, 'C', '12C'),
(4, 'Arun', 'Computers', 89.9, 'A', '12A'),
(5, 'Sabina', 'Medical', 83.3, 'B', '128') '''
cursor.execute(sql)
# Commit the changes
mycon.commit()
# Retrieve and display records
cursor.execute('SELECT * FROM STUDENT')
data = cursor.fetchall()
for rec in data:
print(rec)
# Close the connection
mycon.close()
OUTPUT:
PROGRAM 2: Write a program to connect python with mysql using database
connectivity and perform the following operations in database:
a) Display all the records of students who are in Medical stream
b) Change the Class of Divakar to 12C
c) Display the maximum mark of 12B students
SOURCE CODE:
import mysql.connector
# Establish a connection to the MySQL server
mycon = mysql.connector.connect(
host='localhost',
user='root',
password='password',
database='School')
if mycon.is_connected():
print('Database connected')
# Create a cursor object
cursor = mycon.cursor()
# a) Display all the records of students who are in the Medical
stream
sql = "SELECT * FROM STUDENT WHERE Stream='Medical'"
cursor.execute(sql)
records = cursor.fetchall()
print("Answer for a):")
for rec in records:
print(rec)
# b) Change the Class of Divakar to 12C
sql = "UPDATE STUDENT SET Class='12C' WHERE Name='Divakar'"
cursor.execute(sql)
mycon.commit()
print("Record Updated")
# c) Display the maximum mark of students in class 12
sql = "SELECT MAX(AvgMark) FROM STUDENT WHERE Class='12'"
cursor.execute(sql)
max_mark = cursor.fetchone()[0]
print("Answer for c):")
print("The maximum mark in class 12 is: {max_mark}")
# Close the connection
mycon.close()
OUTPUT:
PROGRAM 3: Write a program to connect python with mysql using database connectivity and
perform the following operations in database:
a) Display all the records of students in the ascending order of Name of the student
b) Display the number of records whose name starts with D
c) Delete the record whose ID is 3
SOURCE CODE:
import mysql.connector
# Establish a connection to the MySQL server
mycon = mysql.connector.connect(
host='localhost',
user='root',
password='password',
database='School')
if mycon.is_connected():
print('Database connected')
# Create a cursor object
cursor = mycon.cursor()
# a) Display all the records of students in ascending order of
Name
sql = "SELECT * FROM STUDENT ORDER BY Name"
cursor.execute(sql)
data = cursor.fetchall()
print("Answer for a):")
for rec in data:
print(rec)
# b) Display the number of records whose name starts with 'D'
sql = "SELECT COUNT(*) FROM STUDENT WHERE Name LIKE 'D%'"
cursor.execute(sql)
count = cursor.fetchone()[0]
print("The number of records whose name starts with 'D' is
{count}")
# c) Delete the record whose ID is 3
sql = "DELETE FROM STUDENT WHERE ID = 3"
cursor.execute(sql)
mycon.commit()
print("Record Deleted")
# Close the connection
mycon.close()
OUTPUT:
PROGRAM 4: Write a program to connect python with mysql using database
connectivity and perform the following operations in database:
a) Count the number of records in each stream and display it
b) Display the number of students who have secured ‘A’Grade
c) Add a new column called Rank in the table.
SOURCE CODE:
import mysql.connector
# Establish a connection to the MySQL server
mycon = mysql.connector.connect(
host='localhost',
user='root',
password='password',
database='School')
if mycon.is_connected():
print('Database connected')
# Create a cursor object
cursor = mycon.cursor()
# a) Count the number of records in each stream and display it
sql = 'SELECT Stream, COUNT(*) FROM STUDENT GROUP BY Stream'
cursor.execute(sql)
data = cursor.fetchall()
print("Answer for a):")
for rec in data:
print(rec)
# b) Display the number of students who have secured 'A' Grade
sql = "SELECT COUNT(*) FROM STUDENT WHERE Grade='A'"
cursor.execute(sql)
count = cursor.fetchone()[0]
print( "The number of students with 'A' grade is {count}")
# c) Add a new column called Rank in the table.
sql = 'ALTER TABLE STUDENT ADD Rank CHAR(3)'
cursor.execute(sql)
mycon.commit()
print('Field Added')
# Close the connection
mycon.close()
OUTPUT:
