Class Notes
Class: XII Date: 21-12-2020
Subject: Topic: Additional Notes on Project
Informatics Practices
Project Contents:
#To check for all the databases, present in MySQL using Python
import mysql.connector
mydb = mysql.connector.connect(host="localhost",user="root",passwd="opjs")
mycursor = mydb.cursor()
mycursor.execute("SHOW DATABASES")
for x in mycursor:
print(x)
#To Create a Database Table:
import mysql.connector
mydb = mysql.connector.connect(host='localhost',user='root',passwd='opjs', database='school')
mycursor = mydb.cursor()
mycursor.execute("CREATE table students1(rollno int(2), name varchar(10), age int(2), marks decimal(5,2), city
varchar(20))")
#To modify table student (adding a new column) in
#MySQL using Python Interface
import mysql.connector
mydb = mysql.connector.connect(host="localhost",\
user="root",\
passwd="opjs",\
database="ajay")
mycursor = mydb.cursor()
mycursor.execute("Alter table students add(marks2 decimal(5,2))")
#To view the modified structure of table student in
#MySQL using Python Interface
import mysql.connector
mydb = mysql.connector.connect(host="localhost",\
user="root",\
passwd="opjs",\
database="school")
mycursor = mydb.cursor()
mycursor.execute("Desc students1")
for x in mycursor:
print(x)
#Menu-driven program to demonstrate FIVE major operations
#performed on a table through MySQL-Python connectivity
def menu():
c='y'
while (c=='y'):
print ("1. add record")
print ("2. update record ")
print ("3. delete record")
print("4. display records")
print ("5. display graph")
print("6. Exiting")
choice=int(input("Enter your choice: "))
if choice == 1:
adddata()
elif choice== 2:
updatedata()
elif choice== 3:
deldata()
elif choice== 4:
fetchdata()
elif choice==5:
graph()
elif choice == 6:
print("Exiting")
break
else:
print("wrong input")
c=input("Do you want to continue or not: ")
def fetchdata():
import mysql.connector
try:
mydb = mysql.connector.connect(host="localhost",user="root",passwd="opjs",database="ajay")
mycursor = mydb.cursor()
mycursor.execute("Select * from students")
myrecords = mycursor.fetchall()
for x in myrecords:
print(x)
except:
print ("Error: unable to fetch data")
def adddata():
try:
import mysql.connector
mydb = mysql.connector.connect(host="localhost",user="root",passwd="opjs",database="ajay")
mycursor = mydb.cursor()
mycursor.execute("INSERT INTO students VALUES(2,'Pooja',21, 'VI','A', 'Pending',390,320)")
mycursor.execute("INSERT INTO students VALUES(3,'Radhika',18, 'VII','B','Evaluated',388,450)")
mycursor.execute("INSERT INTO students VALUES(4,'Sonia',24,'X','D', 'Pending',300,544)")
mycursor.execute("INSERT INTO students VALUES(5,'Vinay',25,'XI','C','Evaluated',410,345)")
mycursor.execute("INSERT INTO students VALUES(10,'Shaurya',15,'X','C','Evaluated',345,560)")
mydb.commit()
except Exception as e:
print(e)
def deldata():
try:
import mysql.connector
mydb = mysql.connector.connect(host="localhost",user="root",passwd="opjs",database="ajay")
mycursor = mydb.cursor()
rno= int(input("Input the rollno to delete the record:"))
qry="DELETE FROM students where Rollno = %s;" %(rno,)
mycursor.execute(qry)
mydb.commit()
print(mycursor.rowcount,"Record (s) Deleted")
except Exception as e:
print(e)
def updatedata():
try:
import mysql.connector
mydb = mysql.connector.connect(host="localhost",user="root",passwd="opjs",database="ajay")
mycursor = mydb.cursor()
mks=float(input("Input the marks to update:"))
nm=input("Input the name for marks will be changed")
qry="UPDATE students set marks1 = %s where Name = '%s';"%(mks,nm)
mycursor.execute(qry)
mydb.commit()
print(mycursor.rowcount,"Record (s) Updated")
except Exception as e:
print(e)
def graph():
try:
import mysql.connector
import pandas as pd
import matplotlib.pyplot as plt
mydb = mysql.connector.connect(host="localhost",user="root",passwd="opjs",database="ajay")
qry="Select name,marks1 from students;"
df=pd.read_sql(qry, mydb)
print(df)
plt.bar(df['name'],df['marks1'])
plt.show()
except Exception as e:
print(e)
menu()
