Introduction

In this guide, we will create a simple Employee Database System that supports CRUD (Create, Read, Update, Delete) operations using Python. CRUD operations are essential for managing data in a database, and learning how to implement these operations in a basic employee database is a great start for beginners. This project will demonstrate how to create a database, interact with it, and perform basic management tasks like adding, reading, updating, and deleting employee records.

Objective

The objective of this program is to design and implement a simple employee management system using Python. The program will:

• Create a database to store employee information.
• Allow users to add new employee records.
• Allow users to view existing employee records.
• Allow users to update details of existing employees.
• Allow users to delete employee records.

Python Code: Employee Database with CRUD Operations

import sqlite3

# Create a database connection and a table
def connect_db():
    conn = sqlite3.connect('employee.db')
    cursor = conn.cursor()
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS employees (
            id INTEGER PRIMARY KEY,
            name TEXT NOT NULL,
            position TEXT NOT NULL,
            salary REAL NOT NULL
        )
    ''')
    conn.commit()
    return conn

# Create operation: Add a new employee
def add_employee(conn, name, position, salary):
    cursor = conn.cursor()
    cursor.execute('''
        INSERT INTO employees (name, position, salary)
        VALUES (?, ?, ?)
    ''', (name, position, salary))
    conn.commit()

# Read operation: View all employees
def view_employees(conn):
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM employees')
    rows = cursor.fetchall()
    for row in rows:
        print(row)

# Update operation: Update employee details
def update_employee(conn, emp_id, name, position, salary):
    cursor = conn.cursor()
    cursor.execute('''
        UPDATE employees
        SET name = ?, position = ?, salary = ?
        WHERE id = ?
    ''', (name, position, salary, emp_id))
    conn.commit()

# Delete operation: Remove an employee from the database
def delete_employee(conn, emp_id):
    cursor = conn.cursor()
    cursor.execute('DELETE FROM employees WHERE id = ?', (emp_id,))
    conn.commit()

# Main program function
def main():
    conn = connect_db()
    
    while True:
        print("\nEmployee Database System")
        print("1. Add Employee")
        print("2. View Employees")
        print("3. Update Employee")
        print("4. Delete Employee")
        print("5. Exit")
        
        choice = input("Enter your choice: ")

        if choice == '1':
            name = input("Enter name: ")
            position = input("Enter position: ")
            salary = float(input("Enter salary: "))
            add_employee(conn, name, position, salary)
            print("Employee added successfully!")

        elif choice == '2':
            print("\nEmployee List:")
            view_employees(conn)

        elif choice == '3':
            emp_id = int(input("Enter employee ID to update: "))
            name = input("Enter new name: ")
            position = input("Enter new position: ")
            salary = float(input("Enter new salary: "))
            update_employee(conn, emp_id, name, position, salary)
            print("Employee updated successfully!")

        elif choice == '4':
            emp_id = int(input("Enter employee ID to delete: "))
            delete_employee(conn, emp_id)
            print("Employee deleted successfully!")

        elif choice == '5':
            print("Exiting the program.")
            break

        else:
            print("Invalid choice. Please try again.")

    conn.close()

if __name__ == "__main__":
    main()

Program Structure and How to Run the Program

This Python program utilizes the SQLite3 module to create and manage a simple employee database. It is structured as follows:

• connect_db(): This function connects to the SQLite database and creates an ’employees’ table if it doesn’t already exist.
• add_employee(): This function adds a new employee record to the database.
• view_employees(): This function retrieves and displays all employee records stored in the database.
• update_employee(): This function updates the details of an existing employee.
• delete_employee(): This function deletes a specific employee record from the database.
• main(): The main program loop where users can interact with the employee database through a simple text menu. It allows the user to perform CRUD operations.

How to Run the Program

1. Ensure you have Python installed on your system.
2. Save the provided Python code into a file named employee_db.py.
3. Open a terminal or command prompt.
4. Navigate to the folder where the employee_db.py file is saved.
5. Run the program by typing python employee_db.py in the terminal.
6. Follow the on-screen instructions to manage employee records.