Question 4 - Past Year - 5 Mark Questions - Chapter 15 - Interface of Python with SQL Database

Consider table ‘Faculty’ in database EngColl with following structure.

Id                -    Faculty id. (Primary Key)

lastname    -    Lastname of the faculty member

Firstname   -    Firstname of the faculty member

locationid   -    Location id of the member.

phone         -    Phone Number of the member.

rank            -    It can be either ‘ASSO’, ‘FULL’,

‘ASST’, ‘INST’

startdate    -   date of joining of the member. Write a Python code to update the rank of the member as ‘FULL’ for all the records whose data of joining is before 31/3/1998.

Password of database is pwdfac, and userid is Fac_ dep

Answer:

import mysql.connector

mydb = mysql.connector.connect('localhost','Fac_dep','pwdfac','EngColl')

cursor = mydb.cursor()

sql = "UPDATE faculty set rank='FULL' where startdate<'1998-03-31';"

try:

mydb.execute(sql)

mydb.commit()

except:

mydb.rollback()

cursor.close()

mydb.close()