Python MySQL Interface programs
(1) Write a program in Python to create a table TEACHERS in SCHOOL database with the column names
TID, TNAME, DEPARTMENT, SALARY, GENDER.
import [Link] as sql
#Function to create connection object
def connection():
try:
conn = [Link](host="localhost", user="root",
password="root", database="school")
if not conn.is_connected():
print("Database not connected")
else:
return conn
except [Link] as er:
print(er)
#Function to create table
def CreateTable():
conn = connection()
mycursor = [Link]()
query = "CREATE TABLE IF NOT EXISTS TEACHERS(TID INTEGER NOT NULL
PRIMARY KEY,TNAME VARCHAR(30),DEPARTMENT VARCHAR(30),SALARY
INTEGER,GENDER CHAR(10))"
[Link](query)
[Link]("COMMIT")
print("Table is created")
[Link]()
[Link]()
#Calling function
CreateTable()
(2) Write a program in Python to insert records in the table TEACHERS of SCHOOL database.
import [Link] as sql
#Function to create connection object
def connection():
try:
conn = [Link](host="localhost", user="root",
password="root", database="school")
if not conn.is_connected():
print("Database not connected")
else:
#print("Database connected")
return conn
except [Link] as er:
print(er)
#Function to insert record in the table
def InsertRecord():
conn = connection()
mycursor = [Link]()
tid = int(input("Enter Teacher's id: "))
tname = input("Enter Teacher's name: ")
dept = input("Enter Teacher's department: ")
salary = int(input("Enter Teacher's salary: "))
gender = input("Enter Teacher's gender: ")
query = "INSERT INTO TEACHERS VALUES(%s,%s,%s,%s,%s)"
values = (tid, tname, dept, salary, gender)
[Link](query, values)
[Link]("COMMIT")
print("Record is inserted")
[Link]()
[Link]()
#Calling function
while True:
InsertRecord()
choice = input("Do you want to enter more records (y/n): ")
if choice in "nN":
break
(3) Write a program in Python to display all records of the TEACHERS table of SCHOOL database.
import [Link] as sql
#Function to create connection object
def connection():
try:
conn = [Link](host="localhost", user="root",
password="root", database="school")
if not conn.is_connected():
print("Database not connected")
else:
return conn
except [Link] as er:
print(er)
#Function to display all records of the table
def DisplayRecord():
conn = connection()
mycursor = [Link]()
query = "SELECT * FROM TEACHERS"
[Link](query)
record = [Link]()
for rec in record:
print(rec[1], rec[2], rec[3], rec[4], sep="\n")
print()
[Link]()
[Link]()
#Calling function
DisplayRecord()
(4) Write a program in Python to search a record from the TEACHERS table of SCHOOL database.
import [Link] as sql
#Function to create connection object
def connection():
try:
conn = [Link](host="localhost", user="root",
password="root", database="school")
if not conn.is_connected():
print("Database not connected")
else:
#print("Database connected")
return conn
except [Link] as er:
print(er)
#Function to search a record from the table
def SearchRecord():
conn = connection()
mycursor = [Link]()
query = "SELECT * FROM TEACHERS WHERE TNAME = %s"
name = input("Enter name of the teacher to search: ")
[Link](query, (name,))
record = [Link]()
if [Link] == 0:
print("Teacher not found!!")
else:
for rec in record:
print(rec[1], rec[2], rec[3], rec[4], sep="\n")
print()
[Link]()
[Link]()
#Calling function
SearchRecord()
(5) Write a program in Python to update a record of the TEACHERS table of SCHOOL database.
import [Link] as sql
#Function to create connection object
def connection():
try:
conn = [Link](host="localhost", user="root",
password="root", database="school")
if not conn.is_connected():
print("Database not connected")
else:
return conn
except [Link] as er:
print(er)
#Function to update a record of the table
def UpdateRecord():
conn = connection()
mycursor = [Link]()
query = "UPDATE TEACHERS SET SALARY = %s WHERE TID = %s"
tid = input("Enter teacher id: ")
sal = int(input("Enter updated salary: "))
[Link](query, (sal,tid))
[Link]("COMMIT")
[Link]()
[Link]()
#Calling function
UpdateRecord()
(6) Write a program in Python to delete a record of the TEACHERS table of SCHOOL database.
import [Link] as sql
#Function to create connection object
def connection():
try:
conn = [Link](host="localhost", user="root",
password="root", database="school")
if not conn.is_connected():
print("Database not connected")
else:
return conn
except [Link] as er:
print(er)
#Function to delete a record from the table
def DeleteRecord():
conn = connection()
mycursor = [Link]()
query = "DELETE FROM TEACHERS WHERE TID = %s"
tid = input("Enter teacher id: ")
[Link](query, (tid,))
[Link]("COMMIT")
[Link]()
[Link]()
#Calling function
DeleteRecord()