Menu Driven CRUD in Python and MySQL

A simple menu driven crud program that we wrote together with my mentor in programming and best friend Rollyn M. Moises for our upcoming book in Python programming.

I am currently accepting programming work, IT projects, school and application development, programming projects, thesis and capstone projects, IT consulting.

work, computer tutorials, and web development work kindly contact me in the following email address for further details.  If you want to advertise on my website kindly contact me also in my email address also. Thank you.

My email address is the following jakerpomperada@gmail.com, jakerpomperada@aol.com, and jakerpomperada@yahoo.com.

My mobile number here in the Philippines is 09173084360.

My telephone number at home here in Bacolod City, Negros Occidental Philippines is  +63 (034) 4335675.

Here in Bacolod I also accepting computer repair, networking and Arduino Project development at a very affordable price.

My personal website is http://www.jakerpomperada.com

Sample Program Output

Program Listing

# Rollyn M. Moises and Jake R. Pomperada# CRUD Application in Python and MySQL

# Tools            : PyCharm Community Edition 2018.3

# Date             : March 6, 2019  Wednesday

# Place of Origin  : Bacolod City, Negros Occidental Philippines

# Website          : http://www.jakerpomperada.com

import pymysql
import subprocess as sp

connection = pymysql.connect(
    host='localhost',
    port=3306,
    user='root',
    password='',
    db='school_management',
)

def add_record():
   print();
   print("ADD STUDENT RECORD")
   print();
   name = input("Enter Student Name     : ")
   course = input("Enter Student Course   : ")
   print();
   try:
      with connection.cursor() as cursor:
         sql = "INSERT INTO student (`name`, `course`) VALUES (%s, %s)"         try:
            cursor.execute(sql, (name, course))
            print("Student added successfully")
         except:
            print("Unable to save student record")
      connection.commit()
   finally:
      print("") #connection.close()
def select_records():
   
   try:
      with connection.cursor() as cursor:
         sql = "SELECT `id`, `name`, `course` FROM student"         try:
            cursor.execute(sql)
            result = cursor.fetchall()
            print();
            print("VIEW STUDENT RECORDS")
            print();
            print("ID\t\t NAME\t\t\t\t\t\t\t\tCOURSE")
            print("---------------------------------------------------------------------------")
            for row in result:
               print(str(row[0]) + "\t\t" + row[1] + "\t\t\t" + row[2])
 
         except:
            print("Unable to fetch records")
 
      connection.commit()
   finally:
      print("") #connection.close()
def delete_input():
   print();
   print("DELETE STUDENT RECORD")
   print();
   id = input("Enter Student ID         : ")
   print();
   delete_record(id)

def delete_record(id):

   try:
      with connection.cursor() as cursor:
         sql = "DELETE FROM student WHERE id = %s"         try:
            cursor.execute(sql, (id,))
            print("Successfully Deleted the Record...")
         except:
            print("Unable to delete record")
 
      connection.commit()
   finally:
      print("") #connection.close()

def update_input():
   print()
   print("UPDATE STUDENT RECORD")
   print();
   id = input("Enter Student ID         : ")
   name = input("Enter Student Name       : ")
   course = input("Enter Student Course     : ")
   print();
   update_record(id, name, course)

def update_record(id, name, course):

   try:
      with connection.cursor() as cursor:
         sql = "UPDATE student SET `name`=%s, `course`=%s WHERE `id` = %s"         try:
            cursor.execute(sql, (name, course, id))
            print("Successfully Updated the Record...")
         except:
            print("Unable to update record")
 
      connection.commit()
   finally:
      print("") #connection.close()
def exit_app():
   print();
   print("\tThank you for using this software.");
   print();
   connection.close()

def menu_option():
   ch = "0"   while ch != "5":
      tmp = sp.call('cls',shell=True)
      print();
      print("===== STUDENT RECORD SYSTEM =====");
      print();
      print("1. Add Student Record")
      print("2. Update Student Record")
      print("3. Show Student List Record")
      print("4. Delete a student Record")
      print("5. Quit Program ")
      print();
      ch = input("Select option : ")
      menu_selection = {
         "1": add_record,
         "2": update_input,
         "3": select_records,
         "4": delete_input,
         "5": exit_app
      }  
      func = menu_selection.get(ch)
      if func is not None:
         func()
      else:
         print("Invalid option selected")
      print();
      input('PRESS THE ENTER KEY TO CONTINUE ')

menu_option()

school_management.sql

-- phpMyAdmin SQL Dump
-- version 4.8.4
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Mar 06, 2019 at 02:17 AM
-- Server version: 10.1.37-MariaDB
-- PHP Version: 7.3.0

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `school_management`
--

-- --------------------------------------------------------

--
-- Table structure for table `student`
--

CREATE TABLE `student` (
  `id` int(11) NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  `course` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `student`
--

INSERT INTO `student` (`id`, `name`, `course`) VALUES
(2, 'Jake Pomperada', 'Master in Information Technology'),
(3, 'Rollyn M. Moises', 'Master of Science in Computer Science');

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `username` varchar(100) DEFAULT NULL,
  `password` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`id`, `username`, `password`) VALUES
(1, '123', '123'),
(2, 'admin', 'admin');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `student`
--
ALTER TABLE `student`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `student`
--
ALTER TABLE `student`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

--
-- AUTO_INCREMENT for table `users`
--
ALTER TABLE `users`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;