Class XII Computer Science
MySQL Connectvity Assignment
# TO CREATE A TABLE IN MYSQL USING PYTHON INTERFACE
import mysql.connector
mydb = mysql.connector.connect(host="localhost",user="root", passwd="system", database="student")
mycursor=mydb.cursor()
mycursor.execute("CREATE TABLE FEES (ROLLNO INT,NAME VARCHAR(20),AMOUNT INT);")
# TO SHOW THE TABLES IN MYSQL USING PYTHON INTERFACE
import mysql.connector
mydb=mysql.connector.connect(host="localhost",user="root",passwd="system ", database="student")
mycursor=mydb.cursor()
mycursor.execute("SHOW TABLES")
for x in mycursor:
print(x)
#TO DESCRIBE TABLE STRUCTURE USING PYTHON INTERFACE
mydb=mysql.connector.connect(host="localhost",user="root",passwd="system",database="student")
mycursor=mydb.cursor()
mycursor.execute("DESC STUDENT")
for x in mycursor:
print(x)
# TO EXECUTE SELECT QUERY USING A PYTHON INTERFACE
import mysql.connector
conn = mysql.connector.connect (host = "localhost",user = "root",passwd = "12345", database="student")
c=conn.cursor()
c.execute("select * from student")
r=c.fetchone()
while r is not None:
print(r)
r=c.fetchone()
# TO EXECUTE SELECT QUERY WITH WHERE CLAUSE USING A PYTHON INTERFACE
import mysql.connector
conn=mysql.connector.connect(host="localhost",user="root",passwd="12345",database="student")
c=conn.cursor()
c.execute("select * from student where marks>90")
r=c.fetchall()
count=c.rowcount
print("total no of rows:",count)
for row in r:
print(row)
#TO INSERT A RECORD (ROLLNO,NAME,AND MARKS) IN MYSQL TABLE student USING PYTHON
INTERFACE
import mysql.connector
mydb= mysql.connector.connect(host="localhost",user="root",passwd="system",database="student")
mycursor=mydb.cursor()
r=int(input("enter the rollno"))
n=input("enter name")
m=int(input("enter marks"))
mycursor.execute("INSERT INTO student(rollno,name,marks) VALUES({},'{}',{})".format(r,n,m))
mydb.commit()
print(mycursor.rowcount,"RECORD INSERTED")
# TO UPDATE A DATA IN A TABLE USING PYTHON INTERFACE
import mysql.connector
mydb=mysql.connector.connect(host="localhost",user="root",passwd="system",database="student")
mycursor=mydb.cursor()
mycursor.execute("UPDATE STUDENT SET MARKS=100 WHERE MARKS=40")
mydb.commit()
print(mycursor.rowcount,"RECORD UPDATED")
# TO DELETE A RECORD FROM THE TABLE USING PYTHON INTERFACE
import mysql.connector
mydb=mysql.connector.connect(host="localhost",user="root",passwd="system",database="student")
mycursor=mydb.cursor()
mycursor.execute("DELETE FROM STUDENT WHERE MARKS<50")
mydb.commit()
print(mycursor.rowcount,"RECORD DELETED")
# TO DROP AN ENTIRE TABLE FROM MYSQL DATABASE USING PYTHON INTERFACE
import mysql.connector
mydb=mysql.connector.connect(host="localhost",user="root",passwd="system", database="student")
mycursor=mydb.cursor()
mycursor.execute("DROP TABLE STUDENT")
# TO ADD A COLUMN IN THE EXISTING TABLE USING PYTHON INTERFACE
import mysql.connector
mydb=mysql.connector.connect(host="localhost",user="root",passwd="system", database="student")
mycursor=mydb.cursor()
mycuror.execute("ALTER TABLE STUDENT ADD AGE NT”)
mydb.commit()
#TO DROP A COLUMN FROM THE TABLE USING PYTHON INTERFACE
import mysql.connector
mydb=mysql.connector.connect(host="localhost",user="root",passwd="system", database="student")
mycursor=mydb.cursor()
mycuror.execute("ALTER TABLE DROP AGE ”)
mydb.commit()
# TO ALTER THE DATATYPE OF A COLUMN IN A TABLE USING PYTHON INTERFACE
import mysql.connector
mydb=mysql.connector.connect(host="localhost",user="root",passwd="system", database="student")
mycursor=mydb.cursor()
mycursor.execute("ALTER TABLE STUDENT MODIFY GRADE CHAR(3)")
