Python-MySQL Interface project (Text based)

import mysql.connector as mc

from art import text2art as t2a

def Display(recs, nor, src):

  if src != "delete":

    print("\nRoll    F_Name       L_Name    Sex   Average")

    print("====    ======       ======    ===   =======")

  if nor == 1:

    print("%3s   %-11s  %-11s  %1s %10s" % (recs[0], recs[1], recs[2], recs[3], recs[4]))

  else:

    for rec in recs:

      print("%3s   %-11s  %-11s  %1s %10s" % (rec[0], rec[1], rec[2], rec[3], rec[4]))

  print("\n", nor, "records displayed")

def display():

  conn = mc.connect(host="localhost", user="root", passwd="root", database="test")

  cur = conn.cursor()

  cur.execute("SELECT * FROM students")

  records = cur.fetchall()

  count = cur.rowcount

  if count == 0:

    print("Empty table")

  else:

    Display(records, cur.rowcount, "display")

  conn.close()

  input("\nPress any key to continue ...\n")

def add():

  conn = mc.connect(host="localhost", user="root", passwd="root", database="test")

  cur = conn.cursor()

  role = int(input("Enter roll number: "))

  fnm  = input("Enter first name: ")

  lnm  = input("Enter last name: ")

  s    = input("Enter sex: ")

  aver = float(input("Enter average: "))

  sql = "INSERT INTO students(roll, fname, lname, sex, avg) VALUES(%s, %s, %s, %s, %s)"

  values = (role, fnm, lnm, s, aver)

  cur.execute(sql, values)

  conn.commit()

  conn.close()

  input("\nPress any key to continue ...\n")

  

def search():

  conn = mc.connect(host="localhost", user="root", passwd="root", database="test")

  cur = conn.cursor()

  field = input("Enter field to be searched: ")

  if field == "roll":

    cond  = input("Enter roll to search for: ")

    query = "SELECT * FROM students WHERE roll = '" + cond + "'"

  elif field == "fname":

    cond  = input("Enter fname to search for: ")

    query = "SELECT * FROM students WHERE fname = '" + cond + "'"

  elif field == "lname":

    cond  = input("Enter lname to search for: ")

    query = "SELECT * FROM students WHERE lname = '" + cond + "'"

  elif field == "sex":

    cond  = input("Enter sex to search for: ")

    query = "SELECT * FROM students WHERE sex = '" + cond + "'"

  elif field == "avg":

    opt   = input("Search for >, <, >=, <=, == or !=: ")

    cond  = input("Enter avg to search for: ")

    query =  "SELECT * FROM students WHERE avg " + opt + " " + cond

  print()

  cur.execute(query)

  records = cur.fetchall()

  if cur.rowcount == 0:

    print("No records returned")

  else:

    Display(records, cur.rowcount, "search")

  conn.close()  

  input("\nPress any key to continue ...\n")

  

def modify():

  role = int(input("Enter roll no of record to modify: "))

  conn = mc.connect(host="localhost", user="root", passwd="root", database="test")

  cur = conn.cursor()

  query = "SELECT * FROM students WHERE roll = " + str(role)

  

  print(query)

  cur.execute(query)

  rec = cur.fetchall()

  Display(rec, 1, "modify")

  

  ans = input("Are you sure you wish to update this record? (Y/N) ")

  if ans.upper() == 'Y':

    fnm  = input("Enter new first name   : ")

    lnm  = input("Enter new last name    : ")

    sx   = input("Enter sex              : ")

    aver = input("Enter new average: ")

    cur.execute("UPDATE students SET fname = '" + fnm  + "' WHERE roll = " + str(role))

    cur.execute("UPDATE students SET lname = '" + lnm  + "' WHERE roll = " + str(role))

    cur.execute("UPDATE students SET sex   = '" + sx   + "' WHERE roll = " + str(role))

    cur.execute("UPDATE students SET avg   = '" + aver + "' WHERE roll = " + str(role))

    conn.commit()

  conn.close()

  input("\nPress any key to continue ...\n")

def delete():

  role = input("\nEnter roll no of record to delete: ")

  conn = mc.connect(host="localhost", user="root", passwd="root", database="test")

  cur = conn.cursor()

  query = "SELECT * FROM students WHERE roll = " + role

  cur.execute(query)

  rec = cur.fetchone()

  if cur.rowcount == 1:

    Display(rec, 1, "delete")

    ans = input("\nDelete this record? (Y/N)")

    if ans.upper() == 'Y':

      cur.execute("DELETE FROM students WHERE roll = " + role)

      conn.commit()

  else:

    print("Record not found")

  input("\nPress any key to continue ...\n")

  

choice = 0

while True:

  print(t2a("      STUDENT     DATABASE"))

  print("\t\t\t\t\t\t\t\tPrepared by T. V. Thomas (Regn. No. __)")

  print("1. Display all records")

  print("2. Add record")

  print("3. Search record")

  print("4. Modify record")

  print("5. Delete record")

  print("6. Display sorted records")

  print("0. Exit")

  choice = int(input("Enter your choice: "))

  if choice == 1:

    display()

  elif choice == 2:

    add()

  elif choice == 3:

    search()

  elif choice == 4:

    modify()

  elif choice == 5:

    delete()

  elif choice == 6:

    pass

  elif choice == 0:

    break

  else:

    print("Wrong choice")