EMPLOYEES INFORMATION SYSTEM IN PYTHON AND SQLITE

Here is my first database application that uses Python and SQLITE I called this program EMPLOYEES INFORMATION SYSTEM IN PYTHON AND SQLITE which allows the user to add,edit,delete,view and quit the program. The code is very simple yet easy to understand. I wrote this program for our upcoming book on Python programming with my best friend Mr. Rollyn M. Moises as my co-author. 

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

crud.py

"""
Simple CRUD in Python and SQLite
Authors :
Mr. Rollyn  M. Moises,MSCS
Mr. Jake R. Pomperada,MAED-IT
Date : March 25, 2019   Monday
Location :Bacolod City, Negros Occidental Philippines
"""

import sqlite3
import os

def create_table():
 conn = sqlite3.connect('employees.sqlite')

 cursor = conn.cursor()

 query = '''
     CREATE TABLE IF NOT EXISTS employee(
      id INTEGER PRIMARY KEY, 
      emp_id INTEGER, 
      name TEXT,
   position TEXT,
         department TEXT,
   salary TEXT
     )
 '''

 cursor.execute(query)
 conn.commit()
 conn.close()

def add_employee(emp_id,name,position,department,salary):
 conn = sqlite3.connect('employees.sqlite')

 cursor = conn.cursor()

 query = '''
     INSERT INTO employee(emp_id,name,position,department,salary)
              VALUES (?,?,?,?,?)
 '''
 cursor.execute(query,(emp_id,name.upper(),position.upper(),department.upper(),salary))
 conn.commit()
 conn.close()

def get_employees():
 conn = sqlite3.connect('employees.sqlite')

 cursor = conn.cursor()

 query = '''
     SELECT  emp_id,name,position,department,salary
     FROM employee
 '''

 cursor.execute(query)
 all_rows = cursor.fetchall()
 conn.commit()
 conn.close()
 return all_rows

def get_employee_by_emp_id(emp_id):
 conn = sqlite3.connect('employees.sqlite')
 cursor = conn.cursor()
 query = '''
     SELECT emp_id, name, position,department,salary
     FROM employee
     WHERE emp_id = {}
 ''' .format(emp_id)
 cursor.execute(query)
 all_rows = cursor.fetchall()
 conn.commit()
 conn.close()
 return all_rows

def update_employee(emp_id,name,position,department,salary):
 conn = sqlite3.connect('employees.sqlite')
 cursor = conn.cursor()
 query = '''
     UPDATE employee
     SET name = ?, position = ?,department = ?, salary = ?
     WHERE emp_id = ?
 '''
 cursor.execute(query,(name.upper(),position.upper(),department.upper(),salary.upper(),emp_id))
 conn.commit()
 conn.close()

def delete_employee(emp_id):
 conn = sqlite3.connect('employees.sqlite')
 cursor = conn.cursor()
 query = '''
     DELETE
     FROM employee
     WHERE emp_id = {}
 ''' .format(emp_id)

 cursor.execute(query)
 all_rows = cursor.fetchall()
 conn.commit()
 conn.close()
 return all_rows

create_table()


def add_data(id_,name,position,department,salary):
 add_employee(id_,name,position,department,salary)
def get_data():
 return get_employees()

def show_data():
 employees_record = get_data()
 for emp in employees_record:
  print(emp)

def show_data_by_id(id_):
 employees_record = get_employee_by_emp_id(id_)
 if not employees_record:
  print("No Data Found at emp_id",id_)
 else:
  print (employees_record)

def option():
 os.system('cls')
 print()
 print("\tEMPLOYEES INFORMATION SYSTEM IN PYTHON AND SQLITE")
 print()
 print("\t1.ADD RECORD \n\t2.SHOW RECORD\n\t3.FIND RECORDS\n\t4.EDIT RECORDS\n\t5.ERASE RECORDS\n\t6.QUIT PROGRAM")
 print()
 select_option= input("\tSELECT YOUR CHOICE ==> ")
 print()
 
 if select_option=='1':
  os.system('cls')
  id_emp = int(input('\tGive Employees ID : '))
  name = input('\tName: ')
  position = input('\tPosition: ')
  department = input('\tDepartment: ')
  salary = input('\tSalary: ')
  add_data(id_emp,name,position,department,salary)
  input("\n\tYour record has been saved.\n\tPRESS ENTER KEY TO RETURN TO MAIN MENU")
  option()
 elif select_option=='2':
  os.system('cls')
  show_data()
  input("\n\tPRESS ENTER KEY TO RETURN TO MAIN MENU")
  option()
 elif select_option=='3':
  os.system('cls')
  id__ = int(input('\tEnter Employees ID: '))
  show_data_by_id(id__)
  input("\n\tPRESS ENTER KEY TO RETURN TO MAIN MENU")
  option()
 elif select_option=='4':
  os.system('cls')
  id_emp = int(input('\tEnter Employees ID: '))
  show_data_by_id(id_emp)
  print()
  name = input('\tName: ')
  position = input('\tPosition: ')
  department = input('\tDepartment: ')
  salary = input('\tSalary: ')
  update_employee(id_emp,name,position,department,salary)
  input("\nYour data has been updated. \n\tPRESS ENTER KEY TO RETURN TO MAIN MENU")
  option()
 elif select_option=='5':
  os.system('cls')
  id_emp = int(input('\tEnter Employees ID: '))
  show_data_by_id(id_emp)
  delete_employee(id_emp)
  input("\n\tYour record has been deleted. \n\tPRESS ENTER KEY TO RETURN TO MAIN MENU")
  option()
 else:
   print()
   print("\tTHANK YOU FOR USING THIS SOFTWARE.")
   return 0;
   return 1;

while(option()):
 pass

DOWNLOAD SOURCE CODE HERE